troubleshooting Question

Import from a closed workbook into a specific worksheet

Avatar of Lawrence Salvucci
Lawrence SalvucciFlag for United States of America asked on
Microsoft Excel
10 Comments1 Solution353 ViewsLast Modified:
I have this code that imports data from a closed workbook but I need to specify which sheet to import it to. Right now it will import to the active sheet. I want to specify the sheet it does to.

Sub LoadDataFromWorkbook03()
    On Error Resume Next
    On Error GoTo 0

Dim tArray As Variant, r As Long, c As Long
n = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(n, "A").Select
    tArray = ReadDataFromWorkbook("D:\Operations\Performance\Data\Sales\MTD Sales PC03", "A2:P15000")
    For r = LBound(tArray, 2) To UBound(tArray, 2)
        For c = LBound(tArray, 1) To UBound(tArray, 1)
            ActiveCell.Offset(r, c).Formula = tArray(c, r)
        Next c
    Next r
Application.Calculation = xlAutomatic
    On Error Resume Next
    
    On Error GoTo 0
End Sub

Private Function ReadDataFromWorkbook(SourceFile As String, SourceRange As String) As Variant
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
    dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile
    Set dbConnection = New ADODB.Connection
    On Error GoTo InvalidInput
    dbConnection.Open dbConnectionString ' open the database connection
    Set rs = dbConnection.Execute("[" & SourceRange & "]")
    On Error GoTo 0
    ReadDataFromWorkbook = rs.GetRows ' returns a two dim array with all records in rs
    rs.Close
    dbConnection.Close ' close the database connection
    Set rs = Nothing
    Set dbConnection = Nothing
    On Error GoTo 0
    Exit Function
InvalidInput:
    MsgBox "The source file or source range is invalid!", vbExclamation, "Get data from closed workbook"
    Set rs = Nothing
    Set dbConnection = Nothing
End Function
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros