Avatar of Lawrence Salvucci
Lawrence Salvucci
Flag for United States of America asked on

Import from a closed workbook into a specific worksheet

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

Open in new window

Microsoft Excel

Avatar of undefined
Last Comment
Lawrence Salvucci

8/22/2022 - Mon
Anastasia D. Gavanas

Try this and let me know
Sub LoadDataFromWorkbook03()
    On Error Resume Next
    On Error GoTo 0
Dim myws As Worksheet
mysheet = "TEST"  'You specify your worksheet here
Set myws = Application.ActiveWorkbook.Sheets(mysheet)

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)
            myws.Cells.Range("A2").Activate 'You specify where you want the active cell to be
            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

Open in new window

Lawrence Salvucci

ASKER
I'm getting an error:

Activate method of Range class failed.

I don't want to specify what the active cell is. I just want the code to import it to my "detail" worksheet, not the worksheet I am currently on. This way if I'm on another worksheet and I run the macro it will import to the "detail" worksheet always.
Anastasia D. Gavanas

OK, try this - you need to activate at least a cell so your other array formula to work.

Sub LoadDataFromWorkbook03()
    On Error Resume Next
    On Error GoTo 0
Dim myws As Worksheet
mysheet = "detail"  'You specify your worksheet here
Set myws = Application.ActiveWorkbook.Sheets(mysheet)

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)
            myws.Range("A1").Activate 'You specify where you want the active cell to be
            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

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
Lawrence Salvucci

ASKER
Still getting the same error message on this line of code:

myws.Range("A1").Activate 'You specify where you want the active cell to be
Anastasia D. Gavanas

OK I see...maybe it is better to set the active cell outside the loop...
try this please
Sub LoadDataFromWorkbook03()
    On Error Resume Next
    On Error GoTo 0
Dim myws As Worksheet
mysheet = "DETAIL"  'You specify your worksheet name, need to insert exact name here
Set myws = Application.ActiveWorkbook.Sheets(mysheet)

Dim tArray As Variant, r As Long, c As Long
n = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(n, "A").Select
myws.Select
myws.Activate
myws.Range("A2").Select
myws.Range("A2").Activate 'You specify where you want the active cell to be
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

Open in new window

Anastasia D. Gavanas

if this does not work I will need a small sample of your MTD Sales file so I can have the same data and try to recreate the reading of the array into the spreadsheet, ok?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Lawrence Salvucci

ASKER
That worked but the problem now is I want it to copy this data to the first blank row on my "detail" worksheet. Right now it's overwriting everything starting from row A2. I need it to add this data to the first blank row so it doesn't overwrite the existing data.
Anastasia D. Gavanas

OK, we need a small function to find the first blank row, cell A<blankrow> correct?
ASKER CERTIFIED SOLUTION
Anastasia D. Gavanas

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Lawrence Salvucci

ASKER
Getting an error that says:

"Object Required"

It goes to this line in the code:

Set MyRange = ws.UsedRange.End(xlDown).Offset(1, 0)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy