Fastest way to load column range from a closed workbook into an array

[johnnyL]

Need some advice on the fastest way to load a column range from a closed workbook into an array.

Since the workbook is closed, I don't know the last row that I need. I only know the start row. It will always be 2.

I do know the sheet name in the closed workbook as well as the column I want values from, actually they are names ie. strings.

I have code that is lightning fast to get a column range from the closed workbook into a sheet, but since I have to guess on the lastrow of the column, the ending is filled with a bunch of zero values.

I am asking if anyone knows a fast way to load the range from the closed workbook directly into an array, thus bypassing the dump to a sheet first, otherwise the fastest way to get the sheet values into an array without all of the zero values that result from the unknown last row of column in the closed workbook ( A loop to check each cell for <> 0 I assume would be less than ideal).

The following is the lightning fast code I have to load the column range from a closed workbook to a sheet range:

Sub GetRangeFromClosedWorkbook()
'
    Dim SourceDirectory     As String
    Dim SourceFileName      As String
    Dim SourceRange         As String
    Dim SourceSheetName     As String
    Dim TempSheetName       As String
    Dim UserSelectedFile    As String
'
    SourceSheetName = "Sheet1"                                                      ' <--- set this to the sheet name in the closed workbook to get data from
    SourceRange = "A2:A300"                                                         ' <--- Set this to the range in the closed workbook to get data from
    TempSheetName = "OurScrapeSheet"                                        ' <--- Set this to the temp sheet name used to store values from the closed workbook
'
    UserSelectedFile = Application.GetOpenFilename(Title:="Please choose Mary's file to open", FileFilter:="Excel Files *.xlsx (*.xlsx),")
    If UserSelectedFile = "False" Then Exit Sub                                     ' If user cancelled then exit sub
'
    SourceDirectory = Left(UserSelectedFile, InStrRev(UserSelectedFile, "\"))       ' Get the path to the file
    SourceFileName = Dir(UserSelectedFile)                                          ' Get the file name and extention
'
    Sheets.Add.Name = TempSheetName                                                 ' Add a temporary sheet to store values from the closed workbook
'
    With ThisWorkbook.Sheets(TempSheetName).Range(SourceRange)
        .FormulaArray = "='" & SourceDirectory & "[" & SourceFileName & "]" & SourceSheetName & "'!" & SourceRange  ' Set Array Formula to range
        .Value = .Value                                                             ' Remove formula from range, leave just the resulting value
    End With
'
MsgBox "Done"                                                                       ' Alert user that range from closed workbook has been loaded to sheet
'
'
' Some code here to quickly load all non zero, non blanks to Array ??? , that is, if a fast way to load from a closed workbook directly to an array is not known
'
'
Application.DisplayAlerts = False                                                   ' Turn off the alert warning for pending Sheet delete
Sheets(TempSheetName).Delete                                                        ' Delete the temporary sheet used to store values from the closed workbook
Application.DisplayAlerts = True                                                    ' Turn alert warnings back on
End Sub

Any advice will be most welcome!

 

[johnnyL]

Ok, this is where I am ending off prior to going to bed:

Sub GetRangeFromClosedWorkbook()
'
    Dim SourceDirectory     As String
    Dim SourceFileName      As String
    Dim SourceRange         As String
    Dim SourceSheetName     As String
    Dim TempSheetName       As String
    Dim UserSelectedFile    As String
    Dim DumpedArrayList       As Object
'
    Set DumpedArrayList = CreateObject("System.Collections.ArrayList")    ' Set up DumpedArrayList
    DumpedArrayList.Add "Closed Workbook List"                                       ' Add a Header to fill Item(0)
'
    SourceSheetName = "Sheet1"                                                      ' <--- set this to the sheet name in the closed workbook to get data from
    SourceRange = "A2:A300"                                                         ' <--- Set this to the range in the closed workbook to get data from
    TempSheetName = "OurScrapeSheet"                                        ' <--- Set this to the temp sheet name used to store values from the closed workbook
'
    UserSelectedFile = Application.GetOpenFilename(Title:="Please choose Mary's file to open", FileFilter:="Excel Files *.xlsx (*.xlsx),")
    If UserSelectedFile = "False" Then Exit Sub                                     ' If user cancelled then exit sub
'
    SourceDirectory = Left(UserSelectedFile, InStrRev(UserSelectedFile, "\"))       ' Get the path to the file
    SourceFileName = Dir(UserSelectedFile)                                          ' Get the file name and extention
'
'-----------------------------------------------------------------------------------------------------------------------------------------------------
'
    Sheets.Add.Name = TempSheetName                                                 ' Add a temporary sheet to store values from the closed workbook
'
    With ThisWorkbook.Sheets(TempSheetName).Range(SourceRange)
        .FormulaArray = "='" & SourceDirectory & "[" & SourceFileName & "]" & SourceSheetName & "'!" & SourceRange  ' Set Array Formula to range
        .Value = .Value                                                             ' Remove formula from range, leave just the resulting value
    End With
'
'-----------------------------------------------------------------------------------------------------------------------------------------------------
'
    For Each cell In ThisWorkbook.Sheets(TempSheetName).Range(SourceRange)          ' Loop through each cell value that was obtained from the closed workbook
        If cell.Value <> 0 Then DumpedArrayList.Add cell.Value                      '   Add all non zero values to the arrayList
    Next
'
'   Column Values from the closed workbook are now saved into the array list without all of the ending zeros, ie blank cells
'
    Application.DisplayAlerts = False                                               ' Turn off the alert warning for pending Sheet delete
    Sheets(TempSheetName).Delete                                                    ' Delete the temporary sheet used to store values from the closed workbook
    Application.DisplayAlerts = True                                                ' Turn alert warnings back on
End Sub

 

[johnnyL]

Ok, after looking at the code again, I made some changes to make it faster.

I eliminated the need for the For/Next loop, as well as added a detection for the sheet name in the closed workbook. Added some cleanup also:

Sub GetRangeFromClosedWorkbookIntoAnArray()
'
'   This code will get a column range from a closed workbook, and save that range into a 1D array.
'
'   1) Ask user for the closed workbook to use
'   2) Find the first sheet name in the closed workbook, that is all we are concerned with for this project ;)
'   3) Find the last row number used in that sheet name
'   4) Set up a temporary sheet to dump values from the closed workbook, this temporary sheet will be deleted later after we are done with it
'   5) Load those values from the temporary sheet into a 1D array
'   6) Remove all tracks
'
    Dim SourceLastRow       As Long
    Dim StartingRowOfNames  As Long
    Dim SourceDirectory     As String
    Dim SourceFileName      As String
    Dim SourceRange         As String
    Dim SourceSheetName     As String
    Dim strConnect          As String
    Dim strSQL              As String
    Dim TempSheetName       As String
    Dim UserSelectedFile    As String
    Dim objConnection       As Object
    Dim objCatalog          As Object
    Dim objRecordSet        As Object
    Dim DumpedNamesArray()  As Variant
'
    StartingRowOfNames = 2                                                  ' <--- Set this to the proper Row that the Dumped names start in
    TempSheetName = "OurScrapeSheet"                                        ' <--- Set this to the temp sheet name used to store values from the closed workbook
'
    Set objConnection = CreateObject("ADODB.Connection")
    Set objCatalog = CreateObject("ADOX.Catalog")                                   ' Set up catalog so we can get the sheet name
    Set objRecordSet = CreateObject("ADODB.Recordset")                              ' Set up recordset so we can get the Last row
'
    UserSelectedFile = Application.GetOpenFilename(Title:="Please choose Mary's file to open", FileFilter:="Excel Files *.xlsx (*.xlsx),")
    If UserSelectedFile = "False" Then Exit Sub                                     ' If user cancelled then exit sub
'
    SourceDirectory = Left(UserSelectedFile, InStrRev(UserSelectedFile, "\"))       ' Get the path to the file
    SourceFileName = Dir(UserSelectedFile)                                          ' Get the file name and extention
'
    strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & UserSelectedFile & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
    objConnection.Open ConnectionString:=strConnect
'
    objCatalog.ActiveConnection = objConnection
    SourceSheetName = Replace(objCatalog.Tables(0).Name, "$", "")                   ' Remove $ from end of sheet name
    SourceSheetName = Replace(SourceSheetName, "'", "")                             ' Remove 's from sheet name
'
    strSQL = "SELECT Count(*) FROM [" & SourceSheetName & "$]"
    objRecordSet.Open Source:=strSQL, ActiveConnection:=objConnection, CursorType:=adOpenForwardOnly, Options:=adCmdText
    SourceLastRow = objRecordSet(0) + 1                                             ' Save Last row number
'
    SourceRange = "A" & StartingRowOfNames & ":A" & SourceLastRow                   ' Set Range in the closed workbook to get data from
'
'-----------------------------------------------------------------------------------------------------------------------------------------------------
'
    Sheets.Add.Name = TempSheetName                                                 ' Add a temporary sheet to store values from the closed workbook
'
    With ThisWorkbook.Sheets(TempSheetName).Range(SourceRange)
        .FormulaArray = "='" & SourceDirectory & "[" & SourceFileName & "]" & SourceSheetName & "'!" & SourceRange  ' Set Array Formula to range
        .Value = .Value                                                             ' Remove formula from range, leave just the resulting value
    End With
'
'-----------------------------------------------------------------------------------------------------------------------------------------------------
'
'   Load Dumped Names into a 1D Array
    DumpedNamesArray = Application.Transpose(ThisWorkbook.Sheets(TempSheetName).Range("A" & StartingRowOfNames & ":A" & SourceLastRow))
'
'   Clean-Up!
    Application.DisplayAlerts = False                                               ' Turn off the alert warning for pending Sheet delete
    Sheets(TempSheetName).Delete                                                    ' Delete the temporary sheet used to store values from the closed workbook
    Application.DisplayAlerts = True                                                ' Turn alert warnings back on
'
     objConnection.Close
'
    Set objConnection = Nothing
       Set objCatalog = Nothing
     Set objRecordSet = Nothing
'
    MsgBox "Dumped values from the closed workbook are now loaded into the 1D Array."
End Sub

That is probably as fast as I can make it. Which is still pdf (pretty dang fast).

Anyone else have any suggestions?

 

[Kyle123]

Since you’re using ado to count the records , why don’t you just use it to grab them instead?

 

[johnnyL]

Since you’re using ado to count the records , why don’t you just use it to grab them instead?

Ok, After much googling again, I have adopted that approach by combining it with some of my initial code:

Sub GetRangeFromClosedWorkbookIntoAnArrayWithoutHelperSheet()                ' Works in excel 2016
'
'   if SourceRange is a range reference:
'       this can only return data from the first worksheet in SourceFile
'
'   if SourceRange is a defined name reference:
'       this function can return data from any worksheet in SourceFile
'
'   SourceRange must include the range headers
'
    Dim objCatalog          As Object
    Dim objConnection       As Object
    Dim objRecordSet        As Object
    Dim strConnect          As String
    Dim SourceRange         As String
    Dim strSQL              As String
    Dim UserSelectedFile    As String
    Dim DumpedNamesArray    As Variant
'
    UserSelectedFile = Application.GetOpenFilename(Title:="Please choose Mary's file to open", FileFilter:="Excel Files *.xlsx (*.xlsx),")
    If UserSelectedFile = "False" Then Exit Sub                                     ' If user cancelled then exit sub
'
    Set objCatalog = CreateObject("ADOX.Catalog")                                   ' Set up catalog so we can get the sheet name
    Set objConnection = CreateObject("ADODB.Connection")                            ' Set up connection so we can connect to the source file
    Set objRecordSet = CreateObject("ADODB.Recordset")                              ' Set up recordset so we can get the Last row and range values
'
    strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & UserSelectedFile & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
'
    On Error GoTo InvalidInput                                                      ' If an error is encountered, goto the error handling section
    objConnection.Open strConnect                                                   ' open the connection to the source file
'
    objCatalog.ActiveConnection = objConnection
    SourceSheetName = Replace(objCatalog.Tables(0).Name, "$", "")                   ' Remove $ from end of sheet name
    SourceSheetName = Replace(SourceSheetName, "'", "")                             ' Remove 's from sheet name
'
    strSQL = "SELECT Count(*) FROM [" & SourceSheetName & "$]"
    objRecordSet.Open Source:=strSQL, ActiveConnection:=objConnection, CursorType:=adOpenForwardOnly, Options:=adCmdText
    SourceLastRow = objRecordSet(0) + 1                                             ' Save Last row number
'
    SourceRange = "A1:A" & SourceLastRow                                            ' Set the range of values that we want to get from source file
'
    Set objRecordSet = objConnection.Execute("[" & SourceRange & "]")
    On Error GoTo 0                                                                 ' Turn off error handling
'
    DumpedNamesArray = objRecordSet.GetRows                                         ' returns a two dimensional array with all values in objRecordSet range
    DumpedNamesArray = Application.Transpose(DumpedNamesArray)
'
'   Clean-Up!
    objConnection.Close                                                             ' close the connection to the source file
'
       Set objCatalog = Nothing                                                     ' Delete Object
    Set objConnection = Nothing                                                     ' Delete Object
     Set objRecordSet = Nothing                                                     ' Delete Object
'
    MsgBox "Dumped values from the closed workbook are now loaded into the 2D Array DumpedNamesArray."
'
    Exit Sub
'
InvalidInput:
    MsgBox "The UserSelectedFile or source range is invalid!", vbExclamation, "Get data from closed workbook"   ' Inform user that an error occurred
     Set objRecordSet = Nothing                                                     ' Delete Object
    Set objConnection = Nothing                                                     ' Delete Object
End Sub

Question that remains, How would I get this code, and/or my previous code, to work in excel versions prior to 2016?

Older versions error.