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.

If you add some code to show how you open your docs, + what references you set, it will be easier to reply. But for sure, anything above 10 sec. is to long. — Patrick Honorez
– Patrick Honorez, Commented Aug 13, 2009 at 15:41
I added the call I'm making and noted the time the single line takes to run. — Andrew Scagnelli
– Andrew Scagnelli, Commented Aug 13, 2009 at 17:17

BradC · Accepted Answer · 2009-08-13 18:40:06Z

3

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.

answered Aug 13, 2009 at 18:40

BradC

40.3k14 gold badges77 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Scagnelli Over a year ago

This worked -- I ran the module while keeping an eye on Task Manager and three instances of Excel were created, even though I had only created one in the code.

David-W-Fenton · Accepted Answer · 2009-08-14 01:28:13Z

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).

Sancarn · Accepted Answer · 2019-08-14 11:09:17Z

Another approach depends on your setup and process.

In my case I need read-only access to a set of Excel documents stored on SharePoint. My code is currently like:

For each path in paths
   set wb = Workbooks.open(path,false)
next

In this case, each workbook is individually downloaded each time a workbook is opened. It would be significantly more efficient if the files were downloaded asyncronously and after the download is complete, the rest of the process executes on the local disk.

My idea is to use CopyFileEx() and pass a callback function. Excel will then download the Excel documents to disk asynchronously and call a VBA function regarding progress. When all files are completed, we can launch the next part of the process, which opens the local workbooks, scans them and then removes them from the local drive.

I'll post code later if I manage to implement it.

Collectives™ on Stack Overflow

VBA File Open is slow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related