Add VBA code to an Exported Excel Workbook

[certifydgangsta]

I have some code that exports two hundred workbooks using various queries. I'm trying to add code to each workbook so that when the workbook opens it formats the data. I have all the formatting code complete, it is the access VBA code that adds VBA into each workbook that I cannot figure out. Any help is greatly appreciated.


I should add, that my code currently exports each query as its own spreadsheet in a single workbook. It then opens the workbook, does the formatting and re-saves. The issue is that it is taking roughly 8 hours to export/format all 200 files and there are people waiting to use the files. To make the files available quicker, I was thinking that I could just export all 200 files quickly and then have the formatting code run when the users open up any of the files. I'm certainly open to any ideas of how to speed up this process.

 

[Mile-O]

Why not just format the data from Access?

If you do something like:

Dim objXL As Object
Set objXL = CreateObject("Excel.Application")

With objXL
    .Workbooks.Open('your workbook path')
<<<do formatting here, or pass Excel object to a sub that will handle it>>>
    .Quit
End With

Set objXL = Nothing

 

[certifydgangsta]

Thanks for the quick help Mile-O. I'm currently formatting the data from Access, but it is taking too long and there are users waiting to use the files. I was hoping that doing the formatting once the user opens the workbook would spread the time taken to format across multiple workbooks and users.

 

[namliam]

easiest way to format things or use predetermined VBA in excel workbooks is to have a "pre fit" excel workbook, that you copy to a new file, then add the exported data to the copied workbook

 

[certifydgangsta]

Thanks namliam. Do you have any references that you recommend for learning more about exporting from access into an excel template?

 

[namliam]

Just lookup Docmd.Transferspreadsheet, plenty available from that method