CODEWITHSUNDEEP
excelvbaimport
Satheesh K
Satheesh K

Reputation: 108

excel VBA importing multiple worksheets is slow

I use the following code to import multiple worksheets from another workbook and do some processing. the Importing time is too long. can any one suggest a more efficient way to import? Should I be looking in to more information in the source files for copy?

Sub SKR_Import()
    On Error GoTo errorhandler
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim Sht As Worksheet
    Set wb1 = ActiveWorkbook
    Dim fd As FileDialog
    Dim filechosen As Integer
    Dim filename As String
    Dim i As Long
    Set fd = Application.FileDialog(msoFileDialogOpen)
    fd.AllowMultiSelect = True
    fd.Title = "Select Excel workbooks to import all sheets"
    filechosen = fd.Show
    If filechosen = -1 Then
        For i = 1 To fd.SelectedItems.Count
            Set wb2 = Workbooks.Open(fd.SelectedItems(i))
            For Each Sht In wb2.Sheets
                Sht.Activate
                ActiveSheet.Copy after:=wb1.Sheets(wb1.Sheets.Count)
            Next Sht
            wb2.Close SaveChanges:=False
        Next i
    End If
    wb1.Activate
    Application.ScreenUpdating = True
    Exit Sub
errorhandler:
    msgBox Error, vbCritical, "Error"
    wb2.Close SaveChanges:=False
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Upvotes: 0

Views: 165

Answers (1)

A.S.H
A.S.H

Reputation: 29332

Instead of looping on the sheets of wb2, you can try to copy them at once using the copy method of sheets (with an s):

        Set wb2 = Workbooks.Open(fd.SelectedItems(i))
        ' For Each Sht In wb2.Sheets
        '     Sht.Activate
        '     ActiveSheet.Copy after:=wb1.Sheets(wb1.Sheets.Count)
        ' Next Sht
        wb2.Sheets.Copy after:=wb1.Sheets(wb1.Sheets.Count)
        wb2.Close SaveChanges:=False

This will also get rid of the Activate statement, which wasn't necessary but only wasted some time.

I dont seem to find other ways to accelerate further your code.

Upvotes: 2

Related Questions