VBA File Open is slow

Question

I'm trying to open a series of Excel spreadsheets using an instance of Excel created inside of a module in an Access database. I can get the files to open properly; however, the actual call to make Excel start takes quite a while, and to open the files takes even longer. The location of the files doesn't matter (same time to open on a local HDD as a network drive).

In an attempt to figure out what was taking so long, I added a timer to the logging module. Opening the files takes approximately 2m30s, during which the host application (Access) is entirely unresponsive to user input); the rest of the script executes in less than 10 seconds.

I'm using the standard Excel.Workbooks.Open call as follows

Set OpenSpreadsheet = Excel.Workbooks.Open(Name, 2, False)

Using Debug.Print methods around this line says it can take up to 2 1/2 minutes for this one line to execute.

Is there anything I can do to make the Excel files open quicker?

EDIT: When opening, UpdateLinks is False and ReadOnly is True; all other options are left to their defaults.

Answer 1

First idea: Can you use a jet driver with an ODBC connection to Excel, instead of opening it in an Excel object? Might be much faster.

Second idea: Make sure to create and instantiate the Excel application object just once at the beginning of the routine, then use the Excel.Workbooks.Open() and Excel.ActiveWorkbook.Close() for each spreadsheet. That way you're not "re-launching" the MS Excel application each time.

Answer 2

To draw out the second of @BradC's well-advised recommendations, if you need to use Excel in more than one procedure, create a self-initializing global function. I always use late binding for automating Office apps.

  Public Function Excel(Optional bolCleanup As Boolean = False) As Object
    Static objExcel As Object

    If bolCleanup Then
       If Not objExcel Is Nothing Then
          Set objExcel = Nothing
          Exit Function
       End If
    End If
    If objExcel Is Nothing Then
       Set objExcel = CreateObject("Excel.Application")
    End If
    Set Excel = objExcel
  End Function

Then you can use it in code without needing to initialize it, and the single instance will remain available to any code that needs to use Excel.

And when you shut down your app, you'd call Excel(True) to clean up.

I do this with Outlook and Word all the time. However, there are some COM apps that it works poorly with, such as PDF Creator, which doesn't take kindly to this kind of treatment (you end up in an endless loop with it shutting down and re-initializing itself if you try to destroy the instance this way).