Extracting data from various excel reports using VBA

[staffordalex]

Hi rbrhodes,

Here is my current code:

[VBA]
Private Sub GetData()
Dim LookupDate As Date

LookupDate = Range("C6").Value
GetWorkStreamData ThisWorkbook, LookupDate, "Vauxhall"
GetWorkStreamData ThisWorkbook, LookupDate, "Ford"
GetWorkStreamData ThisWorkbook, LookupDate, "Fiat"
GetWorkStreamData ThisWorkbook, LookupDate, "VW"
GetWorkStreamData ThisWorkbook, LookupDate, "Honda"
GetWorkStreamData ThisWorkbook, LookupDate, "Toyota"

End Sub

Private Sub GetWorkStreamData(wb As Workbook, LookupDate As Date, WorkStream As String)

Const ROOT_FOLDER As String = http://sharepoint.net/meetings/reports/
Dim LastRow As Long
Dim NextRow As Long

With wb.Sheets("WorkstreamReport")
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

Workbooks.Open ROOT_FOLDER & Format(LookupDate + 4, "yyyy-mm-dd") & Application.PathSeparator & "Report to PMT from " & WorkStream & ".xls"

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

Rows(2).Resize(LastRow - 1).Copy wb.Sheets("WorkstreamReport").Cells(NextRow, "A")
ActiveWorkbook.Close savechanges:=False

End Sub

[/VBA]

It opens up the Vauxhall report but doesnt copy any data into the summary report?

Thanks,
Alex

[rbrhodes]

Hi Alex,

You're still using Column A. Change it to B and you'll see results:

[VBA]
With wb.Sheets("WorkstreamReport")

'//This will return 2 if Column is A, Change to B for correct results
NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
End With

Workbooks.Open ROOT_FOLDER & Format(LookupDate + 4, "yyyy-mm-dd") & Application.PathSeparator & "Report to PMT from " & WorkStream & ".xls"
With ActiveSheet

'//This will return 1 if Col A, Change to B for correct results
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With

[/VBA]