Export to excel

[AndyShuter]

Can anyone help me please?

I would like to create a command button that would sit on one of my forms, and would automatically export the current data shown (the underlying source is a query) into a new Excel file

Does that make sense?

Hope you can help

Regards

Andy

 

[theprez]

Use the DoCMD.transferspreadsheet method. Here is an example


DoCmd.TransferSpreadsheet acExport, 3, _
"Employees","C:\Lotus\Newemps.wk3", True, "A1:G12"


Instead of Employees, you would use your query name.

Once you start typing the command you know it will help you with all the parameters.

The code would be assigned to the On Click event of your command button.

 

[AndyShuter]

Thanks mate!

x

 

[AndyShuter]

Thanks

...Many thanks but is there a way in which I could automatically create a new excel spreadsheet without having to specify a filename such as C:/Lotus etc???

sorry to be a pain!

 

[theprez]

Naw man, no problem. That's why this forum is here. Try using this

DoCmd.OutputTo acOutputQuery, "Employees", acFormatXLS, "C:\Employee.xls", True

I believe in this case if employee.xls is not there, it will create the file. Look at the help documentation for OutPutTo and Transferspreadsheet. It is pretty good.

 

[AndyShuter]

thanks

works great mate, many thanks!

 

[JoshuaAnthony]

Is there a way to just export one record without using a query?

 

[theprez]

Well you could, but it's a little tricky. The easiest thing would be to have the query filter down to the one record you wanted.

If you don't want to use a query, you could run a query to create a temporary file that just contains the one record. Then output the temporary file:

DoCmd.OutputTo acOutputTable, "Temporary Table", acFormatXLS, "C:\Employee.xls", True