Slow workbook resulting in slow vba

[PrashanthKumar123]

hi All ! I have a VBA code (provided below) that allows user to upload a sheet from another workbook. This loads all sheets pretty fast, except for one category of files. The size is 300kb only with 1500 rows, 10 columns. But the vba code surprisingly takes 5 minutes+ to load this worksheet.

I suspect this is because the sheet to load has excessive formatting. This takes me almost the same time even when I double click the file and create a fresh workbook (instead of using the VBA)

Any change in my VBA code that speed this up? Ideal will be to only open a "values-only" FileToOpen and copy sheet

Sub Upload()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim lastRow As Integer
Dim LastColumn As Integer

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
Set src = OpenBook.Sheets(1)
src.Copy Before:=ThisWorkbook.Sheets(1)
OpenBook.Close False
End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

 

[richh]

It could be that you have some rows/columns that appear to be empty that are not, or column-lvel formatting that makes that file a lot bigger than it truly is. Open the egregious file, and do Control + End. See if it takes you to some wild row you weren't expecting.

 

[PrashanthKumar123]

It could be that you have some rows/columns that appear to be empty that are not, or column-lvel formatting that makes that file a lot bigger than it truly is. Open the egregious file, and do Control + End. See if it takes you to some wild row you weren't expecting.

hi Richh, I tried ctrl + end and it takes to me F1500 cell - so this doesn't seem to be the issue!

 

[PrashanthKumar123]

Another reason might be because the excel opens in "Protected View".

When I try to open the file normally (without VBA), it opens in "Protected View" by default and when I change the trust settings etc., it takes fair bit of time to load.

Anyway the VBA code can circumvent this and speed this up?

 

[Alex Blakenburg]

I think it is unlikely that it will make a difference but based on your last comment you could try using this instead of your current OpenBook line:

Set OpenBook = Application.Workbooks.Open(Filename:=FileToOpen, ReadOnly:=True)

 

[PrashanthKumar123]

I think it is unlikely that it will make a difference but based on your last comment you could try using this instead of your current OpenBook line:

Set OpenBook = Application.Workbooks.Open(Filename:=FileToOpen, ReadOnly:=True)

Hi Alex, unfortunately, it still takes the same amount of time.

 

[Alex Blakenburg]

Is it worth considering importing the data using Power Query ?

 

[PrashanthKumar123]

Is it worth considering importing the data using Power Query ?

hi Alex, my existing tool can only accept upload this way, so I'm constrained that.

I ran a few checks and I'm near certain that the issue is because the user upload has many merged cells. Of 1500 rows, about 50% are merged across 3 columns. I'm now checking if there is a way for VBA to import and still circumvent this issue