My script opens a large workbook - too slow - any fixes?

[pebcak]

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.

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


Dim dbFile, xlApp, dbWB, dbCases, dbClaims, dbComplaints, dbSCPaths
dbFile = "U:\Project Support\Local Database\localDatabase.xlsm"
Set xlApp = Excel.Application
Set dbWB = xlApp.Workbooks.Open(dbFile)
Set dbCases = dbWB.Worksheets("Cases")
Set dbClaims = dbWB.Worksheets("Claims")
Set dbComplaints = dbWB.Worksheets("Complaints")
Set dbSCPaths = dbWB.Worksheets("SCPaths")


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?

[snb]

Reduce the code to:


getobject "U:\Project Support\Local Database\localDatabase.xlsm"

I fear you have too many shapes in the workbook and/or too many conditional formatting rules and/or too many named ranges.

[Paul_Hossler]

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.

1. Clean the DB WB to just data, no shapes, macros, conditional formatting, formulas, etc.

2. How many times to you access it? I'd open it at the start and close it at the end, i.e. one time



Dim dbFile as String, dbWB as Workbook, dbCases as Worksheet, dbClaims as Worksheet, dbComplaints, dbSCPaths as Worksheet dbFile = "U:\Project Support\Local Database\localDatabase.xlsm"
Set dbWB = Workbooks.Open(dbFile)
Set dbCases = dbWB.Worksheets("Cases")
Set dbClaims = dbWB.Worksheets("Claims")
Set dbComplaints = dbWB.Worksheets("Complaints")
Set dbSCPaths = dbWB.Worksheets("SCPaths")


3. You can try this to get the data from the second workbook without opening it.
One call for each of the 3 sheets you're interested in

Examples in the attachments




Option Explicit

'https://www.snb-vba.eu/VBA_Arrays_en.html
Sub drv()
Dim v As Variant


v = xlFillArray("c:\users\daddy\desktop\DB.xlsx", "Sheet1", False) ' <<<<<<<<<<<<<<<<<<<<<<<<<<<<< change

MsgBox LBound(v, 1) & " to " & UBound(v, 1) & " rows by " & LBound(v, 2) & " to " & UBound(v, 2) & " columns"

ActiveSheet.Cells(1, 1).Resize(UBound(v, 1), UBound(v, 2)).Value = v

End Sub

'----------------------------------------------------------------------------------------------------------------
'Graham Mayor - http://www.gmayor.com - 24/09/2016 (modified by me)
Private Function xlFillArray(WorkbookPath As String, WorksheetName As String, Optional SkipFirstRecord As Boolean = False) As Variant

Dim oRecordSet As Object, oConnection As Object
Dim iRows As Long
Dim sHeaderRow As String

sHeaderRow = IIf(SkipFirstRecord, "YES", "NO")

Set oConnection = CreateObject("ADODB.Connection")
oConnection.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & WorkbookPath & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=" & sHeaderRow & ";IMEX=1"""


Set oRecordSet = CreateObject("ADODB.Recordset")
oRecordSet.Open "SELECT * FROM [" & WorksheetName & "$]", oConnection, 2, 1


With oRecordSet
.MoveLast
iRows = .RecordCount
.MoveFirst
End With

'for some reason, the first dim is cols, and second dimension is rows
' i.e. array(0 to #cols-1, 0 to #rows-1)
' Transpose() makes this array(1 to #rows, 1 to #cols)
xlFillArray = Application.WorksheetFunction.Transpose(oRecordSet.GetRows(iRows))

If oRecordSet.State = 1 Then oRecordSet.Close
Set oRecordSet = Nothing

If oConnection.State = 1 Then oConnection.Close
Set oConnection = Nothing
End Function

[snb]

Three ways to retrieve data using ADODB Recordset:


Sub M_snb()
c00 = "J:\download\DB.xlsx"
c01 = "Sheet1"

With CreateObject("ADODB.recordset")
.Open "SELECT * FROM `" & c01 & "$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & c00 & ";Extended Properties=""Excel 12.0"""
ThisWorkbook.Sheets(1).Cells(1).CopyFromRecordset .DataSource
.Close

.Open "SELECT * FROM `" & c01 & "$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & c00 & ";Extended Properties=""Excel 12.0"""
sn = .getrows
.Close

.Open "SELECT * FROM `" & c01 & "$`", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & c00 & ";Extended Properties=""Excel 12.0"""
c02 = .getstring
.Close
End With

MsgBox c02
MsgBox UBound(sn) & vbTab & UBound(sn, 2)
End Sub

See also: https://www.snb-vba.eu/VBA_ADODB_recordset_en.html#L_12

[Paul_Hossler]

Nice writeup :thumb

[pebcak]

Thank you all for your replies.

To clear up a point - accessing the three Worksheets is not a problem at all, it's opening the Workbook. The macros that access this database/workbook are in separate workbooks. The delay occurs when the Workbooks.Open(xlFile) is executed. The xlFile has no conditional formatting, shapes, filters, or any other unnecessary items. Just data - but a lot of data.

[snb]

Getobject is the solution: it stays in memory for every macro in every workbook, it will be loaded only once until you close Excel.

I don't think the amount of data causes the delay, probably the file contains links to other sources that are being updated.
But you wouldn't be the first one who was unaware of a burden of hidden shapes in a workbook (or other invisible clutter) or macros.
If you are right: save the Excel file as CSV-file.
Open the CSV-file and save it as .xlsx file. See what it does to it's size and loading time.

If you really want to convince us: upload the file.

[Paul_Hossler]

Thank you all for your replies.

To clear up a point - accessing the three Worksheets is not a problem at all, it's opening the Workbook. The macros that access this database/workbook are in separate workbooks. The delay occurs when the Workbooks.Open(xlFile) is executed. The xlFile has no conditional formatting, shapes, filters, or any other unnecessary items. Just data - but a lot of data.

How long does it take to just open the DB file from File, Open vs. having the macro open it?