Another Related Question/Issue (combining workbooks into Master file)

[mcsmith1974]

This has been an oustanding thread.

I am just learning VBA this morning as I am up against a similar challenge...pulling in multiple workbooks into on workbook. There is a piece I am trying to get at, but can't seem to figure out. Any help is really appreciated.
~
1. I want to pull in multiple workbooks (files) stored in same directory.

2. I want to have a few sheets in my 'Master Workbook File build charts and tables from the multiple workbooks.

3. I want the other workbooks to automatically update into the 'Master Workbook File' when I open the 'Master Workbook File'.

4. When I save the 'Master Workbook File', it saves all the imported worksheets ( ); however, if I want to update the file again later that day with new entered data in the individual workbooks, I have to rerun the Macro (goto Tools, etc..run Macro). This duplicates all the workbooks.

Basically, I want the imported workbooks to update automatically and copy over the previous "like" workbooks that were there at last saved point.

I have used the following code from above inporting my test case:
~~~
[VBA] Sub CombineFiles()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Dim Ch As Chart


ToggleStuff False

Path = "C:\Documents and Settings\Owner\My Documents"

With Application.FileSearch
.NewSearch
.LookIn = Path
.LastModified = msoLastModifiedAnyTime
.FileName = "LinkBook.xls"
If .Execute(msoSortByFileName, msoSortOrderDescending, True) > 0 Then
Workbooks.Open .FoundFiles(1), xlWindows

End If
End With
Set Wkb = ActiveWorkbook
For Each WS In Wkb.Worksheets
WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
For Each Ch In Wkb.Charts
Ch.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next Ch

Wkb.Close False

ToggleStuff True
End Sub

Sub ToggleStuff(ByVal x As Boolean)

With Application
.EnableEvents = x
.ScreenUpdating = x
.DisplayAlerts = x
.AskToUpdateLinks = x
End With

End Sub[/VBA]
~~~

[XLGibbs]

I split this new post from a solved topic for better visibility and exposure. The topic it was originally posted to is below:

http://www.vbaexpress.com/forum/show...9284#post59284

[mcsmith1974]

Thank you Gibbs. Is there anyone that has any suggestions on pulling in automatically multiple worksheets from different workbooks into a Master Workbook as described ?

I am very open to any thoughts, etc., as this is still a obstacle.

Thanks much.

[OBP]

If you want them updated when the workbook is opened wouldn't just Linking to them be a better option?
To have your code import the workbooks each time the workbook is opened place it in the Workbook's
WorkbookOpen Event.

[XLGibbs]

I agree that more fluid and labor free methods such as linking files might be a better option.

There are numerous ways to do this, and I have posted (as have others) in similar situations with coded responses...

To answer directly, if you place a call to the code you have in the workbook open event module, it would execute every time you open the workbook.

[mcsmith1974]

Thanks for the suggestions, however linking workbooks is sort of what I do now and it has caused some issues. Bringing in charts, formats of the linked data, and just too may links to manage. The workbooks I want to bring in (about 10 of them) have many rows/columns which change quite a bit.

Maybe there is an easier way, however I thought for my situation, it would be much easier to just bring in the workbooks into a Master every time I open the master...then I wouldn't have to perform links and update them as my workbook templates change.

Is this simply a matter of place the above code in the Workbook's WorkbookOpen Event in order to import the workbooks each time the Master workbook is opened ?

If you are talking about linking the entire workbook (and all worksheets) into a Master File (with all worksheets of the linked workbooks), then I am open to suggestions if you earnestly feel this makes more logical sense.



Thanks!!

[XLGibbs]

It entirely depends on what you mean by "bring the workbooks in".

It is the end result that is of importance to the design.

For example, I have a Master workbook that probably has 28 or more external source files that it links to.

This master file sits between the source data and the eventual report that it produces. Source===>master====>report.

If you are having issues with charts and formatted data, there are many,many ways to resolve that.

Again, it depends on what the end result needs to be. If you just need to see the data from the 10 source workbooks, then importing them will do...

If you need to then spend time reformatting what you get...then perhaps another option involving linking is in order.

Lots of things make logical sense, but most logical is the one that best serves the purpose you need with minimal manual intervention...

[XLGibbs]

PS. You can just call that code module from the workbook open event

[VBA]

Call CombineFiles

[/VBA]

in the Open event module of the workbook would execute the procedure named CombineFiles everytime you open the workbook

[lucas]

I too have found links in worksheets to be a problem and I have been using this code developed with help from others in the forum...Malcolm and Zack both contributed and others too but I can't remember who at the moment.

This looks for a specific sheet in a specific workbook in a specific path(path can be changed in the code to be thisworkbook path). It first deletes the specific sheet from the master if it exists and then imports the one from the other workbook to the same position in the tabstrip of the master....I find it to be much more reliable than depending on linked workbooks....but that's just me. It's well commented in the code so it should be fairly easy to use.....you do need the funtion too so be sure to copy it as well as the sub.
[vba]Sub ImportSalesSheet()
Dim Wkb As Workbook, BookKeep As Workbook
Dim strPath As String
Dim FileName As String
Dim strFullName As String
Dim IsOpen As Boolean
Dim i As Long
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Set sheetname to be copied
Const SheetToCopy = "Sales"
'look for the Invoice workbook in this documents path
strPath = ThisWorkbook.path
'comment line above and uncomment line below to use hard coded path for invoice.xls
'strPath = "C:\Documents\Test\"
'name of the file you wish to copy the Sales sheet from, change as needed
FileName = "Invoice.xls"

Set BookKeep = ActiveWorkbook
'Locate and delete the Sales Sheet from this workbook
On Error GoTo NotFound
i = Sheets(SheetToCopy).Index
Sheets(i).Delete
NotFound:
'Set value for i if sheet previously deleted
If i = 0 Then i = 1
strFullName = strPath & "\" & FileName
If IsWbOpen(FileName) Then
Set Wkb = Workbooks(FileName)
IsOpen = True
Else
Set Wkb = Workbooks.Open(strFullName)
IsOpen = False
End If
'Following line adds Sales sheet as the last sheet in the workbook
'Wkb.Sheets("Sales").Copy After:=BookKeep.Sheets(ThisWorkbook.Sheets.Count)
'comment line above and uncomment line below to copy sheet to same location
Wkb.Sheets(SheetToCopy).Copy Before:=BookKeep.Sheets(i)
If Not IsOpen Then Wkb.Close False
Application.ScreenUpdating = True
MsgBox "Latest version of Sales Sheet successfully copied to this workbook.", vbInformation
Application.EnableEvents = True
Application.DisplayAlerts = True
Set BookKeep = Nothing
Set Wkb = Nothing
End Sub
'Zack contributed functions to check if workbook and sheet is open and/or exists
Function IsWbOpen(wbName As String) As Boolean
On Error Resume Next
IsWbOpen = Len(Workbooks(wbName).Name)
End Function[/vba]