CODEWITHSUNDEEP
excelvbaexcel-2010
ElectroMotiveHorse
ElectroMotiveHorse

Reputation: 456

Quickest Way to open an excel file with VBA

I have a spreadsheet that has roughly 7600 rows and a lot of formulas in it. The spreadsheet can take a couple of minutes to load at times. Sometimes i just want to quickly check the date of an item in the spreadsheet but it just takes so long. I created a macro that reduces the load time to 32 seconds so far. Is there any way to make it load even faster than what I have coded so far?

Application.DisplayAlerts = False
Application.Calculation = xlManual

Workbooks.Open "foo_bar.xls", ReadOnly:=True, UpdateLinks:=False
Windows("foo_bar.xls").Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
MsgBox(ActiveCell.Value)

Upvotes: 4

Views: 8401

Answers (3)

ElectroMotiveHorse
ElectroMotiveHorse

Reputation: 456

With the help of everyone I came to a solution. Use the following code to suppress alerts, prevent calculations, and supress the screen updates. Another key to making the spreadsheet open immediately was to fix all the links that were broken. Apparently, if the there are broken links, this will still cause a delay issue with loading the spreadsheet although it is told not to calculate or update any formulas referencing them.

Application.DisplayAlerts = False
Application.Calculation = xlManual
Application.ScreenUpdating = False
Workbooks.Open "foo_bar.xlsx",ReadOnly:=True, UpdateLinks:=False

Upvotes: 1

ASH
ASH

Reputation: 20302

Save it with an xlsb extension. Binary files tend to be around 4x smaller than non-binary files (on average), and they tend to open/close 4x faster too (on average).

Upvotes: 0

Davesexcel
Davesexcel

Reputation: 6984

You can add your other applications to the code, as well, change the file name

Sub GetIt()
    Dim wb As Workbook, sh As Worksheet, LstRw As Long
    Set wb = Workbooks.Open("C:\Users\dmorrison\Downloads\foo_bar.xlsx")
    With wb
        Set sh = Sheets(1)
        With sh
            LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
            MsgBox .Range("A" & LstRw).Value
        End With
        .Close
    End With
End Sub

Upvotes: 0

Related Questions