Best way to get data from a closed workbook

[rhombus4]

Usually use code in TestCopy but then recently saw testCopy, so was wondering which is the best way

Also in the first version if it contains a password you can use that in the code but wasn't sure how to use in the Get Object version

Sub testCopy()
Dim wb As Workbook

Application.DisplayAlerts = False

Set wb = Workbooks.Open("C:\MyDocs\Test.xls", Password:="myPassword", ReadOnly:=True)

wb.Sheets("Sheet2").Range("A2:F100").Copy
ThisWorkbook.Sheets("Sheet1").Range("A2").PasteSpecial xlAll

wb.Close False
Application.DisplayAlerts = True

End Sub

and

Sub testCopy2()
With GetObject("C:\MyDocs\Test.xls")
  .Sheets("Sheet2").Range("A2:F100").Copy ThisWorkbook.Sheets("Sheet1").Range("A2")
  .Close 0
End With

End Sub

 

[diddi]

benchmark them and let everyone know your results.
i use a generic sql sub to load data from closed workbooks. i just did a quick benchmark and found i could load a 7000 x 10 array of mixed types from a closed workbook in .247 seconds.

 

[rhombus4]

WOW , very impressive times
Have you got some code that i could try using the generic sql sub

I benchmarked the 2 above turning off screenupdating for both and there wasn't much to chodse between the two roughly between 1.5 to 3 seconds after running about 5 times each, although it was only a small piece of data

Also i want sure how to use the With GetObject("C:\MyDocs\Test.xls") if the spreadhseet was password protected

 

[diddi]

not sure about the password issue, but here is my sub. it is not my work, and unfortunately i cannot give cred because i cant recall where it came from.

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
                   SourceRange As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)

    Dim rsCon As Object, rsData As Object, szConnect As String, szSQL As String, lCount As Long

    If Header = False Then
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=No"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=No"";"
        End If
    Else
        If Val(Application.Version) < 12 Then
            szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 8.0;HDR=Yes"";"
        Else
            szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=Yes"";"
        End If
    End If

    If SourceSheet = "" Then
        ' workbook level name
        szSQL = "SELECT * FROM " & SourceRange$ & ";"
    Else
        ' worksheet level name or range
        szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "];"
    End If

    On Error GoTo SomethingWrong

    Set rsCon = CreateObject("ADODB.Connection")
    Set rsData = CreateObject("ADODB.Recordset")

    rsCon.Open szConnect
    rsData.Open szSQL, rsCon, 0, 1, 1

    ' Check to make sure we received data and copy the data
    If Not rsData.EOF Then

        If Header = False Then
            TargetRange.Cells(1, 1).CopyFromRecordset rsData
        Else
            'Add the header cell in each column if the last argument is True
            If UseHeaderRow Then
                For lCount = 0 To rsData.Fields.Count - 1
                    TargetRange.Cells(1, 1 + lCount).Value = _
                    rsData.Fields(lCount).Name
                Next lCount
                TargetRange.Cells(2, 1).CopyFromRecordset rsData
            Else
                TargetRange.Cells(1, 1).CopyFromRecordset rsData
            End If
        End If

    Else
        MsgBox "No records returned from : " & SourceFile, vbCritical
    End If

    ' Clean up our Recordset object.
    rsData.Close
    Set rsData = Nothing
    rsCon.Close
    Set rsCon = Nothing
    Exit Sub

SomethingWrong:
    MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _
           vbExclamation, "Error"
    On Error GoTo 0

End Sub

and here is a sample from a project

    GetData "D:\Documents\TimeSheet Data\NMF.xlsm", "D", "A4:J10000", Sheets("B").Range("KN1"), False, False

 

[rhombus4]

Thanks
Is it possible to get the current region using the code and not sure what the False False does at the end

'GetData "C:\Documents\test.xlsm", "Sheet2", "A4",currentregion, Sheets("Sheet1").Range("A5"), False, False

 

[diddi]

the 2 false parameters are for headers. if currentregion is a string then that is fine. what i did in the example posted is got 10000 records where i actually only needed 7000. it is so fast getting the blanks is not an issue. btw you have an extra parameter in your interpretation of my example

 

[rhombus4]

the 2 false parameters are for headers. if currentregion is a string then that is fine. what i did in the example posted is got 10000 records where i actually only needed 7000. it is so fast getting the blanks is not an issue. btw you have an extra parameter in your interpretation of my example

The current region wasn't a string. It changed to a comma when I typed it
I tried below but got an error

GetData "C:\Documents\test.xlsm", "Sheet2", "A4".currentregion, Sheets("Sheet1").Range("A5"), False, False

 

[diddi]

it must be a string, if it is a range it wont work. eg

GetData "C:\Documents\test.xlsm", "Sheet2", "A2:F100", Sheets("Sheet1").Range("A5"), False, False

 

[rhombus4]

That's what I did initially which worked. Not sure how to get the current region of the closed workbook into a string

i.e. Range("A1").CurrentRegion.Copy

 

[diddi]

you have to supply the parameters required by the sub. which are as per the examples
just get a larger region and you will be covered