Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UPDATING LINKS TO OTHER WORKBOOKS :( | Excel Discussion (Misc queries) | |||
Updating a master workbook with 80 workbooks | Excel Discussion (Misc queries) | |||
Updating workbook from protected workbooks... | Excel Programming | |||
Updating Workbooks from multiple links Workbooks | Excel Worksheet Functions | |||
Updating Old Workbooks | Excel Worksheet Functions |