PrashanthKumar123
New Member
- Joined
- May 1, 2021
- Messages
- 38
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
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