CODEWITHSUNDEEP
excelvba
CJK
CJK

Reputation: 119

VBA - Retrieving Data from Closed Excel Workbook

I am trying to create a VBA script that will gather data from four different Workbooks. For now, I am just testing the code with one Workbook, but I am receiving an error when I try to acquire the data. While I would like to retrieve the data from the four Workbooks without opening them, I will need to open them in order to find the last row of data. Here is my current code:

Public Sub GetData()

Application.ScreenUpdating = False

Dim LastRow As Integer
Dim WB As Workbook
Dim xlsPath As String
Dim xlsFilename As String
Dim SheetName As String

xlsPath = "C:\Users\a27qewt\My Documents\Document Retention\FI_DocumentRetention.xlsm"

Set WB = Workbooks.Open(xlsPath)

'Workbooks("FI_DocumentRetention.xlsm").Sheets("S&S Document Locations").Unprotect

LastRow = Workbooks("FI_DocumentRetention.xlsm").Sheets("S&S Document Locations").Cells(Rows.Count, "A").End(xlUp).Row

Workbooks("SS_Index.xlsm").Sheets("Document Index").Range(Cells(2, 1), Cells(LastRow, 5)).Value = _
Workbooks("FI_DocumentRetention.xlsm").Sheets("S&S Document Locations").Range(Cells(2, 1), Cells(LastRow, 5)).Value

WB.Close False

End Sub

I am receiving a 1004 application/object defined error in the Workbooks("FI_DocumentRetention.xlsm").Sheets("S&S Document Locations").Range... line. Any suggestions why?

Upvotes: 1

Views: 5125

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

You already solved your problem, but here's how I'd approach it

Public Sub GetData()

    Dim LastRow As Long '<< not Integer
    Dim WB As Workbook
    Dim xlsPath As String
    Dim xlsFilename As String
    Dim SheetName As String
    Dim shtSrc As Worksheet, shtDest As Worksheet, rngSrc As Range

    Application.ScreenUpdating = False

    xlsPath = "C:\Users\a27qewt\My Documents\Document Retention\FI_DocumentRetention.xlsm"

    Set WB = Workbooks.Open(xlsPath)

    Set shtSrc = WB.Sheets("S&S Document Locations")
    Set shtDest = Workbooks("SS_Index.xlsm").Sheets("Document Index")

    LastRow = shtSrc.Cells(shtSrc.Rows.Count, "A").End(xlUp).Row

    Set rngSrc = shtSrc.Range(shtSrc.Range("A2"), _
                              shtSrc.Cells(LastRow, 5))

    shtDest.Range("A2").Resize(rngSrc.Rows.Count, _
                               rngSrc.Columns.Count).Value = rngSrc.Value

    WB.Close False

End Sub

Upvotes: 1

Related Questions