Create Excel file, export Access table at run time....

[Isadore]

I'm in the middle of trying to find VB code which will allow a user to "export" an Access table to an Excel file but the file is non-existent, i.e., I want to create a new excel file, name it, and export the table into the newly created excel file.... I have enough programming behind me that I can handle the nec. strings, etc... to name the file, and provide the path, etc.. just need to know if you can start with no file, and then when the process is over you end up with "Myfile.xls" in a particular directory.

 

[Isadore]

Discovered a "back-door" approach to this problem.

1) Create a Macro (macExport)
2) Choose "Output to"
Object Type: Table
Object Name: tblMyTable
Object Format: Microsoft Excel (*.xls)
Object File: =Forms![MyForm]![Mypath]
Auto Start: No
Template File:

Now, in VB code behind the form you have to develop your path from information (strings) on the form and stick it to an invisible textbox on the back of the form, here I call the textbox "MyPath". The macro reads the path and creates the Excel file and saves it to that path. Be sure to compile the complete path, e.g.,

"C:\MyExcelFiles\MySites\0400530.xls"

Works like a charm