Collecting data from different workbooks into summary workbook

E

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
 
M

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
 
M

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
 
E

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:
 
E

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:
 
E

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:
 
E

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:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

summarizing data from various workbooks 3
Link future workbooks to a summary workbook... 1
Remove workbook reference but keep worksheet references 2
Merge Multiple workbook sheets into one workbook 0
Can mulitple Excel workbooks be summarized into 1 workbook? 1
Excel How to capture data to a specified excel sheet(first workbook) of two different workbooks from the 0
Creating multiple workbooks from summary workbook 2
All Workbook Saved except This Workbook 3

Top