Fastest way to import data

[Peppermintschnapps]

Hello! I want to get data from 5 closed workbooks, in the same directory, and import them into separate sheets in a single workbook("Master Spreadsheet").
The external workbooks update overnight, and are approx (Cols x Rows) 50 x 500; 10 x 2,000; 70 x 1,0000; 30 x 2000; 70 x 10,000 in size. The names don't change but the sizes are somewhat volatile.

I don't need every single column in these external workbooks, but everything seems to be faster on ranges.

I need to get the time down to under 30 seconds (preferable 20 seconds) as the team is really impatient and won't wait longer than that.

I'd appreciate any help at all.

I've tried by cell reference on sheets in the Master Spreadsheet, which seems to be the fastest:

 "='\\xxx.xxx.xxx.xx.xx\xxx\xxx\[Project Expenditure Forecast.xlsx]Detail - Project Version'!$A$2:$BP$10000"
 "='\\xxx.xxx.xxx.xx.xx\xxx\xxx\[Expenditure Forecast.xlsx]Detail - Cost Category by Sub P'!A3:BS10000"
    ' repeated for each workbook

I've tried Range.Value:

Private Sub Workbook_Open()
    Dim sourceFile As String
    Dim sourceWorkbook As String
    Dim sourceSheet As String
    Dim destinationSheet As String
    
    Application.ScreenUpdating = False

    sourceFile = "\\xxx.xxx.xxx.xx.xx\xxx\xxx\Project Expenditure Forecast.xlsx"
    sourceWorkbook = "Project Expenditure Forecast.xlsx"
    sourceSheet = "Detail - Project Version"
    destinationSheet = "Detail - Project Version"
    Call ImportReportsRangeValue(sourceFile, sourceWorkbook, sourceSheet, destinationSheet)
    ' repeated for each workbook

    Application.ScreenUpdating = True
End Sub
'---------------------------------------
Sub ImportReportsRangeValue(sourceFile As String, sourceWorkbook As String, sourceSheet As String, destinationSheet As String)
    On Error GoTo ErrHandler
    
    Application.ScreenUpdating = False
    Application.EnableEvents = True
    Application.DisplayAlerts = False
    
    Worksheets(destinationSheet).Cells.Clear
    
    Workbooks.Open sourceFile
    'get the size of the external worksheet
    Dim sourceRows As Integer
    sourceRows = Workbooks(sourceWorkbook).Worksheets(sourceSheet).UsedRange.Rows.Count
    Dim sourceColumns As Integer
    sourceColumns = Workbooks(sourceWorkbook).Worksheets(sourceSheet).UsedRange.Columns.Count
    
    Dim sourceRange As Worksheet
    Set sourceRange = Workbooks(sourceWorkbook).Worksheets(sourceSheet)

    Dim destRange As Worksheet
    Set destRange = Workbooks("Test Import.xlsm").Worksheets(destinationSheet)
    
    destRange.Range(destRange.Cells(1, 1), destRange.Cells(sourceRows, sourceColumns)).Value = _
        sourceRange.Range(sourceRange.Cells(1, 1), sourceRange.Cells(sourceRows, sourceColumns)).Value
    
ErrHandler:
    Workbooks(sourceWorkbook).Close
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

I've tried Arrays: (called by Workbook_Open with a name change)

Sub ImportReportsArray(sourceFile As String, sourceWorkbook As String, sourceSheet As String, destinationSheet As String)
    On Error GoTo ErrHandler
    
    Application.ScreenUpdating = False
    Application.EnableEvents = True
    Application.DisplayAlerts = False

    Dim sourceWb As Workbook
    Set sourceWb = Workbooks.Open(sourceFile)
    Dim sourceWs As Worksheet
    Set sourceWs = sourceWb.Sheets(sourceSheet)

    Dim rowCount As Integer, colCount As Integer
    rowCount = sourceWs.UsedRange.Rows.Count    
    Dim colCount As Integer
    colCount = sourceWs.UsedRange.Columns.Count

    ' range-array copy from source sheet
    Dim data() As Variant
    data = sourceWs.Range(sourceWs.Cells(1, 1), sourceWs.Cells(rowCount, colCount)).Value

    'paste into destination
    Dim destWb As Workbook
    Set destWb = Workbooks("Test Import.xlsm")
    Dim destWs As Worksheet
    Set destWs = destWb.Sheets(destinationSheet)
    
    destWs.Cells.Clear
    
    Dim destRange As Range
    Set destRange = destWs.Range(destWs.Cells(1, 1), destWs.Cells(rowCount, colCount))
    destRange = data

ErrHandler:
    Workbooks(sourceWorkbook).Close
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Thanks in anticipation

 

[igold]

Hi Peppermintschnapps- Wlecome to the MrExcel Forum. In your Arrays Code, you could probably pick up a little time by changing this:

Dim destRange As Range
Set destRange = destWs.Range(destWs.Cells(1, 1), destWs.Cells(rowCount, colCount))
destRange = data

to this:

destWs.Range("A1").Resize(UBound(data, 1), UBound(data, 2)) = data

Also, clearing all the cells with this line

destWs.Cells.Clear

is time consuming, It may be quicker just delete and re-add the sheet.

Also can't you put the used range directly into the array

data = sourceWs.UsedRange

 

[Domenic]

Or, maybe simply...

    With sourceWs.UsedRange
        destWs.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

Hope this helps!