Web Development

Ask something
Discussion Locked

exporting from access to excel

0 helpful
8 replies

by jnaughton

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am new to VB programming and need a little help.
I have a simple access database with a vb frontend. I need to be able to export the data in one of the tables into an excel spreadsheet using a button. Is this possible?
Also when adding new records I need to be able to group by a reference number so the users only need to input the reference number once until all parts are inputted.
Thanks for ye're help
James

8 replies

by richtee

If you are using Access 2K, use the following code in your command button. Substitute your table name for tblName and your Excep file name for the WorkbookName.

DoCmd.TransferSpreadsheet acExport, 8, "tblName", "C:\WorkbookName.xls", True

Good luck

by bristar

This is the code I use to do this function:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "tbl/qryName", "directory\filename.xls", True
MsgBox "Excel File has been created"

* An export profile is not needed since Excel uses it's own format type.

* The first set of quotes can be either table or query.

* The directory structure can include a network path: \\server\directory\filename.xls

* As you type this is, the options such as acExport and acSpreadsheet will begin to fill in automatically. You may choose to save the spreadsheet in a lower version.

* The 'True' option tells the system that you want column headers as well. This can be left blank.

* I put a simple MsgBox function afterward to let me know that everything is done.

* You can place this code in under the 'On Click' event procedure for your button.

* You will probably need to reformat your columns in the spreadsheet.

This discussion is closed to new replies.