Export data in Access form to Excel ? 1

[MrPeds]

Hi,

I am displaying some data in an Access 2000 form.

I would like to be able to click on a button that opens up MS Excel with the data from the form in the Excel spreadsheet.

Is this possible ? if so , how?

Kind regards,

ROss

 

[AlienRay]

Sure, just make a report containing text fields which refer back to the form (i.e., "=Forms!MyForm!Field1&quot. Then, on the On Click event for the button, use this code:

DoCmd.OutputTo "ReportName", stDocName

That should do it.

 

[dvannoy]

create a query or report and a macro..in the macro use output, then you can specify what type and where you want to export the file to.

 

[MrPeds]

my solution was a bit hacked but it works.

i created an empty (dummy) table in access.

I then used the DoCmd.TransferSpreadsheet function using the dummy database as an argument.

Finally, i used VBA code to manipulate the cells of the excel spreadsheet.

I will post this code tomorrow for others to use.

Regards,

MrPeds

 

[ChrisJF]

MrPeds,

Can you please post that code. This is something I am looking at, especially the use of VBA to manipulate the cells in Excel.

Regards,
ChrisJF

 

[MrPeds]

This code should be of use.

Apologies for the delay in posting.

DoCmd.TransferSpreadsheet acExport, , "tblSomeTable",
"C:\someFile.xls", 0

Dim mysheet As Object
Dim xlApp As Object

' Set object variable equal to the OLE object.
Set xlApp = CreateObject("Excel.Application&quot
Set mysheet = xlApp.Workbooks.Open("c:\someFile.xls&quot.Sheets(1)

' Put the values in the Excel Cells:
'column 1:
mysheet.Cells(1, 1).Value = ""
mysheet.Cells(2, 1).Value = "asdasda"
mysheet.Cells(3, 1).Value = "asdasda"
mysheet.Cells(4, 1).Value = "asdas"
mysheet.Cells(5, 1).Value = "asdads"
mysheet.Cells(6, 1).Value = "asdas"
mysheet.Cells(7, 1).Value = "asdas"

' sheet, and quit Microsoft Excel.
mysheet.Application.Windows("someFile.xls&quot.Visible = True
mysheet.Application.ActiveWorkbook.Save
mysheet.Application.ActiveWorkbook.Close
xlApp.Quit

Shell ("EXCEL.EXE C:\someFile.xls&quot

' Clear the object variable.
Set mysheet = Nothing
Set xlApp = Nothing

MrPeds

 

[OhioSteve]

You can do all of this with queries and macros. You do not need VB. This is what to do:

1) make a query with calculated fields like alienray described. That way, you can get the values from the form.

2) make a macro that outputs the data. The "output to" action has all the arguments that you need.

 

[LonnieJohnson]

Thanks Mr Peds,

This just cut some time off of one of my projects. You da man!!!

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!