Updating a workbook from many workbooks.
 

I am using excel 2003. I have a report workbook that has 27 other workbooks
that it gets information from. What is the easiest way to update the report
workbook with out me singularily opening each of the 27 other work books.
The are all under the same folder on the same drive. including the report
workbook.

Updating a workbook from many workbooks.
 

Hi Outrigger

You have not said what you want to do when all of the books are open.
The following code opens all 27 workbooks for you. If you could post
more information we might be able to get you a more specific result.
Change the file path to suit.

Take care

Marcus

Option Explicit
Sub OpenInFolder()

Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
Dim twbk As Workbook
Dim strFullName As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set twbk = ActiveWorkbook
sPath = "C:\Users\Smallman\Excel" 'Change to suit.
ChDir sPath
sFil = Dir("*.xls") 'All Excel files in folder

Do While sFil < "" 'will start LOOP
strFullName = sPath & "\" & sFil
Workbooks.Open Filename:=(strFullName), UpdateLinks:=0
'Your Code Here
oWbk.Close False 'Don't save
sFil = Dir
Loop ' End of LOOP

End Sub

Updating a workbook from many workbooks.
 

See
http://www.rondebruin.nl/merge.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"outrigger" wrote in message ...
I am using excel 2003. I have a report workbook that has 27 other workbooks
that it gets information from. What is the easiest way to update the report
workbook with out me singularily opening each of the 27 other work books.
The are all under the same folder on the same drive. including the report
workbook.

Updating a workbook from many workbooks.
 

The main workbook is linked to all those workbooks, so when they open it
updates the linked information. I then need to close the workbooks and print
the reports from the report workbook.

"marcus" wrote:

Hi Outrigger

You have not said what you want to do when all of the books are open.
The following code opens all 27 workbooks for you. If you could post
more information we might be able to get you a more specific result.
Change the file path to suit.

Take care

Marcus

Option Explicit
Sub OpenInFolder()

Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
Dim twbk As Workbook
Dim strFullName As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set twbk = ActiveWorkbook
sPath = "C:\Users\Smallman\Excel" 'Change to suit.
ChDir sPath
sFil = Dir("*.xls") 'All Excel files in folder

Do While sFil < "" 'will start LOOP
strFullName = sPath & "\" & sFil
Workbooks.Open Filename:=(strFullName), UpdateLinks:=0
'Your Code Here
oWbk.Close False 'Don't save
sFil = Dir
Loop ' End of LOOP

End Sub
.

Updating a workbook from many workbooks.
 

All the work books contain different information with different column
headings and rows and are not shared.

"outrigger" wrote:

The main workbook is linked to all those workbooks, so when they open it
updates the linked information. I then need to close the workbooks and print
the reports from the report workbook.

"marcus" wrote:

Hi Outrigger

You have not said what you want to do when all of the books are open.
The following code opens all 27 workbooks for you. If you could post
more information we might be able to get you a more specific result.
Change the file path to suit.

Take care

Marcus

Option Explicit
Sub OpenInFolder()

Dim oWbk As Workbook
Dim sFil As String
Dim sPath As String
Dim twbk As Workbook
Dim strFullName As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set twbk = ActiveWorkbook
sPath = "C:\Users\Smallman\Excel" 'Change to suit.
ChDir sPath
sFil = Dir("*.xls") 'All Excel files in folder

Do While sFil < "" 'will start LOOP
strFullName = sPath & "\" & sFil
Workbooks.Open Filename:=(strFullName), UpdateLinks:=0
'Your Code Here
oWbk.Close False 'Don't save
sFil = Dir
Loop ' End of LOOP

End Sub
.