staffordalex
11-06-2008, 04:52 AM
Hi,
I'm trying to copy data from multiple spreadsheets into one summary spreadsheet. Each spreadsheet that I'm looking to copy data from is stored within a folder, named as a date, within a sharepoint site.
Each report is named as 'Report to PMT from Vauxhall we 1031', 'Report to PMT from Ford we 1031', 'Report to PMT from Fait we 1031' etc etc. and the format of each report is exactly the same.
I've pretty much found a way of copying the data that I want from each report into the summary report, however at the moment it goes about it by actually opening up each report before it copies the data, using workbooks.open.
Issues encountered here are that I am presented with a message box asking me to "update or Don't update" links to data sources within the workbook, and also another message box asking me a yes/no question regarding copying the data.
Is there a way of getting rid of workbooks.open and hence activesheet so that it doesnt open up every report before it copies the data?
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
Function convertDate(dDate As Date) As String
convertDate = Format(dDate, "mmdd")
End Function
Private Sub GetWorkStreamData(wb As Workbook, LookupDate As Date, WorkStream As String)
Const ROOT_FOLDER As String = "http://sharepointsite.net/meetings/reports/"
Dim ReportDate As String
ReportDate = convertDate(LookupDate)
Workbooks.Open ROOT_FOLDER & Format(LookupDate + 4, "yyyy-mm-dd") & Application.PathSeparator & "Report to PMT from " & WorkStream & " we " & ReportDate & ".xls"
Select Case WorkStream
Case "Vauxhall": ActiveSheet.Range("D11:Q11").copy wb.Sheets("WorkstreamReport").Range("D19:Q19")
Case "Ford": ActiveSheet.Range("D11:Q11").copy wb.Sheets("WorkstreamReport").Range("D20:Q20")
' etc etc
End Select
ActiveWorkbook.Close savechanges:=False
End Sub
I'm trying to copy data from multiple spreadsheets into one summary spreadsheet. Each spreadsheet that I'm looking to copy data from is stored within a folder, named as a date, within a sharepoint site.
Each report is named as 'Report to PMT from Vauxhall we 1031', 'Report to PMT from Ford we 1031', 'Report to PMT from Fait we 1031' etc etc. and the format of each report is exactly the same.
I've pretty much found a way of copying the data that I want from each report into the summary report, however at the moment it goes about it by actually opening up each report before it copies the data, using workbooks.open.
Issues encountered here are that I am presented with a message box asking me to "update or Don't update" links to data sources within the workbook, and also another message box asking me a yes/no question regarding copying the data.
Is there a way of getting rid of workbooks.open and hence activesheet so that it doesnt open up every report before it copies the data?
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
Function convertDate(dDate As Date) As String
convertDate = Format(dDate, "mmdd")
End Function
Private Sub GetWorkStreamData(wb As Workbook, LookupDate As Date, WorkStream As String)
Const ROOT_FOLDER As String = "http://sharepointsite.net/meetings/reports/"
Dim ReportDate As String
ReportDate = convertDate(LookupDate)
Workbooks.Open ROOT_FOLDER & Format(LookupDate + 4, "yyyy-mm-dd") & Application.PathSeparator & "Report to PMT from " & WorkStream & " we " & ReportDate & ".xls"
Select Case WorkStream
Case "Vauxhall": ActiveSheet.Range("D11:Q11").copy wb.Sheets("WorkstreamReport").Range("D19:Q19")
Case "Ford": ActiveSheet.Range("D11:Q11").copy wb.Sheets("WorkstreamReport").Range("D20:Q20")
' etc etc
End Select
ActiveWorkbook.Close savechanges:=False
End Sub