Add VBA code to an Exported Excel Workbook

[gasmaskman]

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.

[drexasaurus]

Are you talking about adding code and modules to the workbooks themselves? I haven't worked with this often, so I can't really vouch for using this technique, but this page describes coding to the VBE. You could do it this way, just note the warning that it has about virus scanners. Hopefully this helps.

http://www.cpearson.com/excel/vbe.aspx

[ItsMe]

Perhaps you can format the worksheets from Access by creating an Excel object. I am not an expert but here are a couple examples.

https://www.accessforums.net/program...art-40325.html

https://www.accessforums.net/import-...eet-38117.html

[gasmaskman]

ItsMe, 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.

Drexasaurus, thanks I'll take a look at this. I'm pretty terrible at getting excel VBA to work in Access, but it's certainly worth a try.

[pbaldy]

Just to throw out an option, could you add the formatting code to a blank spreadsheet file, and use that file as a template when creating the 200?

[ItsMe]

EDIT: and certainly a template would be nice. Wasn't thinking about that.. end edit

It sounds as though time is being spent exporting and then, once again, opening the files to format them. Not sure you are saving time formatting when the user opens the file. I believe convention is to format during the export process.

Combining the export and format procedure together seems to create the least amount of processing threads. Could possibly export to the local machine to a temp folder and then run another process to copy the files to a share. Splitting it up that way may speed things up.

[gasmaskman]

pbaldy, that's a pretty creative idea. I'll give it a try. Thanks.

ItsMe, can I paste a simplified version of the code that I have and you help me rearrange the code to see how much it speeds up the process?

[ItsMe]

Go ahead and paste it. I have limited experience interacting with Excel objects. But others will be able to help too.

[gasmaskman]

The following code exports four queries into an excel workbook, resizes the columns for all sheets, and deletes empty columns for one specific spreadsheet. I didn't include variable declarations, but assume that all have been declared.

        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Basic Losses", CurrentFolder & SVCnumber1 & " Output", True
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "All losses", CurrentFolder & SVCnumber1 & " Output", True
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Basic Gains", CurrentFolder & SVCnumber1 & " Output", True
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "All Gains", CurrentFolder & SVCnumber1 & " Output", True
                
        Set wk = xl.Workbooks.Open(strPath)
        For Each ws In wk.Sheets
            ws.Select
            ws.Columns.AutoFit
        Next ws
                                   
        Set ws = wk.Sheets("Basic Losses")
    
        For j = 40 To 1 Step -1
            If wf.CountA(ws.Range(ws.Cells(2, j), ws.Cells(ws.Rows.Count, j))) = 0 Then
                ws.Columns(j).Delete
            End If
        Next j
                            
        wk.Sheets(1).Activate
        wk.Save
        wk.Close
        xl.Quit
        Set xl = Nothing

[ItsMe]

I think you can speed things up using "CopyFromRecordset"

Here is some sample code. My example was to incorporate what the user had already started with. So, there could possibly be some refactoring done to the example still. But, post #10 does demonstrate some formatting. Post #9 simply gets straight to business and uses the minimum number of lines of code. There are additional methods using this technique that will afford more interaction.
https://www.accessforums.net/program...tml#post210391

[gasmaskman]

Thanks ItsMe. I might be having a brain fart right now, but using your example, is it possible to reference the query when opening the recordset instead of storing the query's SQL code in a string? My SQL statements are realllly long and change often so it would be a pain to have to convert the SQL code and paste it into VBA everytime.

[ItsMe]

Shure, you just include the name of the query in place of the SQL string. I think intelisense even asks for a table name or something.

Set MyRecordset = MyDatabase.OpenRecordset("qryName", dbOpenSnapshot)

Post if you have any more questions.

[gasmaskman]

When I change it to my query's name (qAdjs), I get the following error: "Run-time error '3061': Too few paramters. Expected 1."

Set MyRecordset = MyDatabase.OpenRecordset("qAdjs", dbOpenSnapshot)

[ItsMe]

EDIT: I have been thinking and it should not matter how you open your query, DAO or otherwise. The error is probably because there is a missing field name in your form or you copied a control and did not correct the control name. This is assuming you have a parameterized query that references the form. Try opening the query object in DS view while the form is open and before executing the form's code. end edit..



Sounds like you have a parameterized query. You will need to bring the parameters into your forms module. Create another query or remove parameters and then create a string.

Maybe something like

Dim strSQL as string
strSQL = "SELECT * FROM qAdjsMODIFIED WHERE [ThisField] = " & the rest of your expression

Set MyRecordset = MyDatabase.OpenRecordset(strSQL, dbOpenSnapshot) 'be sure to remove the quotes when placing string variable into statement