Creating a mass of Excel Files

[marlan]

Hi,

I am basically done with this process, I would just like to hear other opinions on using the GetObject or CreateObject for creating a mass of MS Office files. I am using Acc2003.

My App creates some 200-500 Excel files in one loop, using this code:

Public Function GetExcelObject() As Object
    On Error GoTo GetExcelObject_Err
    
    Set GetExcelObject = GetObject(, "Excel.Application")
    
GetExcelObject_Exit:
    Exit Function
    
GetExcelObject_Err:
On Error GoTo 0
    Set GetExcelObject = CreateObject("Excel.Application")
    Resume GetExcelObject_Exit
End Function

later On I get workBook and worksheet objects using this ExcelApp.

At first I have created an ExcelApp for each writing in the Excel (there are a few in each file), and I got the 462 or 91 errors after some 7 files created. Next I had created one for each file, and passed it on ByRef to the Sub writing to the Excel. This let me write some 30 files.
Surfing the web, I found Here, and in other places, that I should focus to some form, so that code stops running, and Windows Running Object Table (ROT) can register this object, so I Created a form, and had a label on it Inform the user of the state of the process:

    Set ApXL = GetExcelObject
    updProgress (Getting Excel Object... Please wait.)

Public Sub updProgress(progress as String)
    DoCmd.OpenForm "DispInProcess"
    
    Forms![DispInProcess].lblProcess.Caption = progress
    DoEvents

updProgress_Exit:
    Exit Sub
End Sub

Now, having DoEvets, I've got the Errors after some 130 files. Still too early.

Finally, I have created this Excel Object outside of the loop, and passed it in ByRef (could have used a global object. How do you create a singleton in VBA?).

The Process runs for about 30 Minutes, creating and sending by e-Mail over 250 files.
Is this Creating one Excel Object, and holding it for so long a good method? I want to use this for production.​

 

[DJkarl]

Unless you have a specific need to use Excel Automation (creating pivots, formatting, etc...) I would suggest using the TransferSpreadsheet method of DoCmd.

 

[marlan]

Hi, and thanks for the reply!

I have 4 queries in diferet sections of the sheet, and some other text. The formatting is used just because I can...

Could you do this using TransferSpreadsheet? I think each transfer creates a new file.

What do think about reusing the referece to the App object rather than getting a new one? Is there a reson why I havn't seen this method being suggested?