How to copy data from closed workbook into a master workbook

0
votes

I need help copying data from a closed workbooks (without opening them) into a column in the master workbook using VBA. I keep getting the error:

Run-time Error 424: object required

Here is my code:

Set x = Workbooks.Open("C:\Users\DD\Desktop\EMS")

   x.Sheets("PO Report").Range("Y3:Y500").Copy

   y.Activate
   Sheets("Sheet1").Range("Q2").PasteSpecial

   Application.CutCopyMode = False
   x.Close

Thanks for the help in advance!

2
vbaexcel
What is y here, and which line gives the error ? – Tim Williams
That 'y' is the other workbook being pasted into by x=workbook. I am new to VBA and I just get the error at the end. – Cartolo

2 Answers

0
votes

this is the problem - you are not specifying the filename of the excel file

Set x = Workbooks.Open("C:\Users\DD\Desktop\EMS")

you cannot read data out of a closed file... it has to be open

you also need to Dim your x object

Dim x as object
0
votes

I altered the code posted here. Insert the following code in your "Sheet1" sheet module:

Option Explicit

Sub GetDataDemo()
    Dim FilePath$
    Dim i As Long
    Const FileName$ = "EMS.xlsx"
    Const SheetName$ = "PO Report"
    FilePath = "C:\Users\DD\Desktop\"

    DoEvents
    Application.ScreenUpdating = False
    If Dir(FilePath & FileName) = Empty Then
        MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
        Exit Sub
    End If
    For i = 3 To 500
        Range("Q" & i - 1) = GetData(FilePath, FileName, SheetName, Range("Y" & i))
    Next i
    ActiveWindow.DisplayZeros = False
End Sub

Private Function GetData(Path, File, Sheet, Rng)
    Dim Data$
    Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & Rng.Address(, , xlR1C1)
    GetData = ExecuteExcel4Macro(Data)
End Function