CODEWITHSUNDEEP
ms-accessvbaexcelms-access-2007excel-2007
Andrew Scagnelli
Andrew Scagnelli

Reputation: 1604

VBA File Open is slow

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.

Upvotes: 1

Views: 5835

Answers (3)

Sancarn
Sancarn

Reputation: 2824

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.

Upvotes: 0

David-W-Fenton
David-W-Fenton

Reputation: 23067

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

Upvotes: 0

BradC
BradC

Reputation: 39916

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.

Upvotes: 3

Related Questions