Alternative to ActiveSheet????

[staffordalex]

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?

[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

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
[/vba]

[RonMcK]

Alex,

Search the messages (search on top toolbar) for 'copy data from closed workbooks'. This will get you a number of threads and in them you'll find the techniques for getting data. The alternative you're looking for involves opening a file and accessing a sheet in it by name rather than activating a sheet and referencing it via activesheet.


HTH,

[Kenneth Hobs]

Try the closed workbook methods at Ron De Bruin's site. http://www.rondebruin.nl/copy7.htm

[Bob Phillips]

[vba]

Option Explicit

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 Filename:=ROOT_FOLDER & Format(LookupDate + 4, "yyyy-mm-dd") & _
Application.PathSeparator & "Report to PMT from " & _
WorkStream & " we " & ReportDate & ".xls", _
UpdateLinks:=0

Application.DisplayAlerts = False
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
Application.DisplayAlerts = True

ActiveWorkbook.Close savechanges:=False

End Sub
[/vba]

[staffordalex]

xld,

genius!

thank you so much!

Alex

[mdmackillop]

Don't forget to mark your threads Solved.