Another - Combining Multiple Workbooks Into One Workbook w/ Mutiple Tabs

[TSparlin]

First, I want to say that I just gained an interest in macros and VBA and have taken on a task at work that is over my head. This website is great and seems to be so very helpful to folks in my situation! have researched this site and have found posts that almost get me where I need to go, but not all the way. The following are the details of the task:

• Using Excel 2007
• Need to combine 70 or so Workbooks into separate tabs in one Workbook - once a month process
• All 70 or so files will be in one folder - nothing else is in the folder except those files
• Name the Tab the same name as the Workbook (i.e. Workbook name "123 Contract" results in Tab name "123 Contract")
• All the 70 or so files have the same format and structure
• Bring over print formats, etc. into each of the tabs
• I assume that once I run the process once that I will have to manually delete the tabs the next time before I run the process again. It would be great to have a process to delete the tabs before I start again.

Thanks for all your help!

Todd

[GTO]

Greetings Todd and welcome to VBAX

[quote=TSparlin]
Name the Tab the same name as the Workbook (i.e. Workbook name "123 Contract" results in Tab name "123 Contract")[quote]

Is there only one sheet in ea of the source workbooks?

Mark

[TSparlin]

Yes. The source workbooks will on have one sheet. Thanks.

Todd

[GTO]

Greetings Todd,

In a Standard Module:

[VBA]Option Explicit

Sub MergeData()
Dim FSO As Object '<-- FileSystemObject
Dim fsoFol As Object '<-- Folder
Dim fsoFil As Object '<-- File
Dim WB As Workbook
Dim wks As Worksheet
Dim ShNames() As String
Dim Path As String
Dim PathNew As String
Dim i As Long

With ThisWorkbook

Path = .Path & "\"

Application.ScreenUpdating = False

If .Worksheets.Count > 1 Then
ReDim ShNames(1 To .Worksheets.Count)
For i = 1 To .Worksheets.Count
ShNames(i) = .Worksheets(i).Name
Next
.Worksheets.Add Before:=.Worksheets(1), Type:=xlWorksheet
Application.DisplayAlerts = False
.Worksheets(ShNames).Delete
Application.DisplayAlerts = True
End If

Set FSO = CreateObject("Scripting.FileSystemObject")
Set fsoFol = FSO.GetFolder(.Path & "\")

For Each fsoFil In fsoFol.Files

If Mid(fsoFil.Name, InStrRev(fsoFil.Name, ".") + 1) Like "xls*" _
And Not fsoFil.Name = .Name Then

Set WB = Workbooks.Open(fsoFil.Path, False)
WB.Worksheets(1).Copy After:=.Worksheets(.Worksheets.Count)

.Worksheets(.Worksheets.Count).Name = _
Left(Left(fsoFil.Name, InStrRev(fsoFil.Name, ".") - 1), 31)

WB.Close False
End If
Next

Application.DisplayAlerts = False
.Worksheets(1).Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True

Set WB = Nothing

If MsgBox("Save Me now?...", _
vbQuestion Or vbYesNo Or vbDefaultButton1, _
"You wanna save?") = vbYes Then
.Save
End If
End With
End Sub[/VBA]

[TSparlin]

Wow! That was fast. When I run the macro, I receive the following error message before the process completes:

Run time error "1004"

Excel cannot open the file '~$Combine.xlsm' because the file format or file extension is not valid. Verfiy that the file has not been corrupted and that the file matches the format of the file.

The file named "Combine" is the Excel file that I created to house the macro. When I tried to get out of it, it made me save it as a "macro-enabled worksheet".

When I debug, the follow code is highlighted:

Set WB = Workbooks.Open(fsoFil.Path, False)

In the end, it looked like it worked because all of the files were combined onto one tab with correct names. Just the error message thing. Please give me your thoughts. Thanks.

Todd

[GTO]

Wow! That was fast. When I run the macro, I receive the following error message before the process completes:

Run time error "1004"

Excel cannot open the file '~$Combine.xlsm' because the file format or file extension is not valid. Verfiy that the file has not been corrupted and that the file matches the format of the file.

The file named "Combine" is the Excel file that I created to house the macro. When I tried to get out of it, it made me save it as a "macro-enabled worksheet".

When I debug, the follow code is highlighted:

Set WB = Workbooks.Open(fsoFil.Path, False)

In the end, it looked like it worked because all of the files were combined onto one tab with correct names. Just the error message thing. Please give me your thoughts. Thanks.

Todd

Hi there,

I am unable to test currently, but by my reckoning, I believe you could fix the error by several different methods.

• Move the Combine.xlsm file and update the path to the correct folder. A simple way would be to move it one folder up in the hierarchy. By example:

Let's say the files (and Combine.xlsm) are currently in C:\Data\MyFiles\

Move Combine.xlsm to C:\Data\ and append Path like:
[VBA]Path = .Path & "\MyFiles\"[/VBA]

• Ignore the error:

[VBA]On Error Resume Next
Set WB = Workbooks.Open(fsoFil.Path, False)
On Error GoTo 0[/VBA]

• Include the "temp file"* with a test:

[VBA]If Mid(fsoFil.Name, InStrRev(fsoFil.Name, ".") + 1) Like "xls*" _
And Not fsoFil.Name = .Name _
And Not fsoFil.Name = "~$" & .Name Then
'OR...
If Mid(fsoFil.Name, InStrRev(fsoFil.Name, ".") + 1) Like "xls*" _
And Not fsoFil.Name = .Name _
And Not fsoFil.Name Like "*" & .Name Then[/VBA]

Of those, I personally would probably just move the file and append the Path, it just seems most assured and easy. If you want to keep the file (sorry, workbook) in with the ones being ripped from, I would try the last suggestions (include checking for the temp name in the IF), and specifically, the first of these two if there may be another file in the folder with a filname ending in Combine.

I personally try and avoid ignoring errors, unless I know what the exact error will be and can use that error to tell me something (like if a certain workbook is not open that should be). I suppose we do know the exact error to expect, but I would just try the other methods first.

* - Clarication Sought?

Hi all,

Just in case anyone has a moment to expound (and or correct me) on this, my belief is that the ~$name is a temp file in memory, right? I notice that on the PC I am at, when I open Todd's wb (or other wb's), I don't see this in Explorer?!? I still see .doc's if I have them open. Todd's system obviously must be able to see these. I tested both a wb on a flash drive or a network drive. Any ideas?

[GTO]

ACK! Unfortunately your "helper" is a moron. If the last suggestion is used, it would just be:
[VBA] If Mid(fsoFil.Name, InStrRev(fsoFil.Name, ".") + 1) Like "xls*" _
And Not fsoFil.Name Like "*" & .Name Then [/VBA]