Collecting data from different workbooks into summary workbook

[Espen Rostad]

Hi there

I've got trouble collecting datas for different workbooks into a main
sumamry workbook. The WB are all saved onto a spesific folder on a server,
but the summary WB only collects the data when all workbooks are opened in
excel. How can i get the summary WB to collect data from other WB's without
opening them all?

Thank you in advance for your help

 

[Mike H]

Hi,

There are methods you can use on closed workbooks but using a macro it's
simple to open each workbook in turn and manipulate the data. The 2 subs
below open each workbokk in a folder in turn. The first opens the workbook
and then calls the second where you can work on your data.

Sub OpenFiles()
Dim FSO As Object
Dim Folder As Object
Dim File As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder("c:\") ' Change to suit
For Each File In Folder.Files
If File.Type = "Microsoft Excel Worksheet" Then

Workbooks.Open Filename:=Folder.Path & "\" & File.Name

'Call your macro and do things
Call dothings
ActiveWorkbook.Close True
End If
Next
End Sub


Sub dothings()
MsgBox ActiveWorkbook.Name
End Sub


Mike

 

[Espen Rostad]

Hi Mike, thank you for the effort

I am afraid your solution wont work for me since source WBs could be opened
by others. I guess the easiest way to solve this would be to put it all in
one WB, but I would like different departments to be able to register data
independent off each others. Can this be done?

Mike H skrev:

 

[Espen Rostad]

Hi Mike, thank you for the effort

I am afraid your solution wont work for me since source WBs could be opened
by others. I guess the easiest way to solve this would be to put it all in
one WB, but I would like different departments to be able to register data
independent off each others. Can this be done?

Mike H skrev:

 

[Gord Dibben]

See Ron de Bruin's site for collecting from closed workbooks.

http://www.rondebruin.nl/copy7.htm

Or this page at Ron's site.

http://www.rondebruin.nl/ado.htm


Gord Dibben MS Excel MVP