title: How can I speed up opening a large Excel Workbook in a VBA script?
body: <p>A lot of my procedures access the same workbook/database, which is saved as a macro-enabled Excel file.  The workbook has a total of about about 255,000 records each with 15 fields (three worksheets: 50,000; 177,000; and 26,000).  All of the procedures only read from the workbook, none of them write to it.  It takes a few seconds for the procedure to open the workbook each time its accessed.</p>
  <p>The workbook itself is read-only and the sheets are protected.  I open it as read-only as well, and the language I use is pretty straight forward ...</p>
  <pre><code>Dim dbFile, xlApp, dbWB, dbCases, dbClaims, dbComplaints, dbSCPaths
      dbFile = &quot;U:\Project Support\Local Database\localDatabase.xlsm&quot;
      Set xlApp = Excel.Application
      Set dbWB = xlApp.Workbooks.Open(dbFile)
      Set dbCases = dbWB.Worksheets(&quot;Cases&quot;)
      Set dbClaims = dbWB.Worksheets(&quot;Claims&quot;)
      Set dbComplaints = dbWB.Worksheets(&quot;Complaints&quot;)
      Set dbSCPaths = dbWB.Worksheets(&quot;SCPaths&quot;)
  </code></pre>
  <p>Is there anyway I can speed up the time it takes to open the workbook?  Can I save the database-workbook in another format so it will open quicker, or keep the workbook open in memory?</p>