Want to modify this VBA code snippet
 

I put some debug messages in the code and added an additional test to
prevvent an error that I found. But I found the problem. You files need to
be in a lower folder. You wanted to search multiple folders and I don't
think you want the code to search all of your C:\ folders.

for example if Root was c:\Temp

c:\temp\Folder1
abc.xls
def.xls
ghi.xls
folder1.xls - new file
c:\temp\Folder2
abc.xls
def.xls
ghi.xls
folder2.xls - new file
c:\temp\Folder3
abc.xls
def.xls
ghi.xls
folder3.xls - new file
c:\temp\Folder4
abc.xls
def.xls
ghi.xls
folder4.xls - new file




Sub Combinebooks()

Root = "c:\Temp"
Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(Root)
For Each sf In folder.subfolders
Set newbk = Nothing
MsgBox ("Searching Folder : " & sf)
First = True
FName = Dir(sf & "\*.xls")
Do While FName < ""
MsgBox ("Opening File : " & sf & "\" & FName)
Set bk = Workbooks.Open(Filename:=sf & "\" & FName)
For Each sht In bk.Sheets
If First = True Then
sht.Copy
Set newbk = ActiveWorkbook
First = False
Else
With newbk
sht.Copy _
after:=.Sheets(.Sheets.Count)
End With
End If
Next sht
bk.Close savechanges:=False
FName = Dir()
Loop
NewFName = sf & "\" & sf.Name & ".xls"
If Not newbk Is Nothing Then
MsgBox ("Creating File : " & NewFName)
newbk.SaveAs Filename:=NewFName
newbk.Close
End If
Next sf

End Sub


"Sam Commar" wrote:

Joel

Thanks for looking at this again however it still is not working for me. The
only change I made is the name of the Root from
Root = "c:\Temp"
to
Root = "c:\Fentasia"

Running the macro did not do any processin .I have posted a Video of my
process at:

https://download.yousendit.com/TTZrT0Njckk1UjdIRGc9PQ

so maybe that may assist in guiding me what I am doing wrong as you can
visually see my whole process in the vide.

thanks again for all your help

Sam

"joel" wrote in message
...
I don't know why I didn't receive any notice on April 1st when my original
code didn't work. Here is the code that I fully tested.

You siad the line

newbk.SaveAs Filename:=sf & "\" & _
sf.Name & ".xls"

failed.


1) Make Sure you change the ROOT in the code below.
2) the code is writing the New file to the same directory where the 3
files
are located. If the folder is write protected you coould get an error
3) The New file Name already exists. I added a msgbox to help isolate the
problem.

Sub Combinebooks()

Root = "c:\Temp"


Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(Root)

For Each sf In folder.subfolders
First = True
FName = Dir(sf & "\*.xls")
Do While FName < ""
Set bk = Workbooks.Open(Filename:=sf & "\" & FName)
For Each sht In bk.Sheets
If First = True Then
sht.Copy
Set newbk = ActiveWorkbook
First = False
Else
With newbk
sht.Copy _
after:=.Sheets(.Sheets.Count)
End With
End If
Next sht
bk.Close savechanges:=False
FName = Dir()
Loop
NewFName = sf & "\" & sf.Name & ".xls"
MsgBox ("Creating File : " & NewFName)
newbk.SaveAs Filename:=NewFName
newbk.Close
Next sf

End Sub




"Sam Commar" wrote:

This code snippet works very well in combining multiple workbooks in a
folder.
I wanted to request assitance in modifyin the snippet.

In my case every month I will have 24 directories and each directory will
have 3 workbooks in it -one with 2 worksheets, another with 2 worksheets
and
one with 1 worksheet.
I want to combine the three workbooks in each Direcotry automatically in
each of the 24 direcotires and name the workbook with the name of the
directory.


The code snippet below basically does the combining but only with the
user
interactive opeinig up a new workbook and then selecting the workbooks to
merge and it does not create a new workbook with the new name

I would really appreciate it if someone can assist me with modifying the
code for this

Option Explicit

'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal _
pszpath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) _
As Long

Public Type BrowseInfo
hOwner As Long
pIDLRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Function GetDirectory(Optional msg) As String
On Error Resume Next
Dim bInfo As BrowseInfo
Dim path As String
Dim r As Long, x As Long, pos As Integer

'Root folder = Desktop
bInfo.pIDLRoot = 0&

'Title in the dialog
If IsMissing(msg) Then
bInfo.lpszTitle = "Please select the folder of the excel files to
copy."
Else
bInfo.lpszTitle = msg
End If

'Type of directory to return
bInfo.ulFlags = &H1

'Display the dialog
x = SHBrowseForFolder(bInfo)

'Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
GetDirectory = Left(path, pos - 1)
Else
GetDirectory = ""
End If
End Function

Sub CombineFiles()
Dim path As String
Dim FileName As String
Dim LastCell As Range
Dim Wkb As Workbook
Dim WS As Worksheet
Dim ThisWB As String

ThisWB = ThisWorkbook.Name
Application.EnableEvents = False
Application.ScreenUpdating = False
path = GetDirectory
FileName = Dir(path & "\*.xls", vbNormal)
Do Until FileName = ""
If FileName < ThisWB Then
Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
For Each WS In Wkb.Worksheets
Set LastCell = WS.Cells.SpecialCells(xlCellTypeLastCell)
If LastCell.Value = "" And LastCell.Address =
Range("$A$1").Address Then
Else
WS.Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)
End If
Next WS
Wkb.Close False
End If
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

Set Wkb = Nothing
Set LastCell = Nothing
End Sub

Want to modify this VBA code snippet
 

Joel

Thanks a ton for this. Its going good

Thanks

Sam

"Sam Commar" wrote in message
...
Joel

Thanks so much. This seems to have done it.

Thanks so much again. I really really appreciate your assistance

Regards

S Commar

"joel" < wrote in message
...
I put some debug messages in the code and added an additional test to
prevvent an error that I found. But I found the problem. You files need
to
be in a lower folder. You wanted to search multiple folders and I don't
think you want the code to search all of your C:\ folders.

for example if Root was c:\Temp

c:\temp\Folder1
abc.xls
def.xls
ghi.xls
folder1.xls - new file
c:\temp\Folder2
abc.xls
def.xls
ghi.xls
folder2.xls - new file
c:\temp\Folder3
abc.xls
def.xls
ghi.xls
folder3.xls - new file
c:\temp\Folder4
abc.xls
def.xls
ghi.xls
folder4.xls - new file




Sub Combinebooks()

Root = "c:\Temp"
Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(Root)
For Each sf In folder.subfolders
Set newbk = Nothing
MsgBox ("Searching Folder : " & sf)
First = True
FName = Dir(sf & "\*.xls")
Do While FName < ""
MsgBox ("Opening File : " & sf & "\" & FName)
Set bk = Workbooks.Open(Filename:=sf & "\" & FName)
For Each sht In bk.Sheets
If First = True Then
sht.Copy
Set newbk = ActiveWorkbook
First = False
Else
With newbk
sht.Copy _
after:=.Sheets(.Sheets.Count)
End With
End If
Next sht
bk.Close savechanges:=False
FName = Dir()
Loop
NewFName = sf & "\" & sf.Name & ".xls"
If Not newbk Is Nothing Then
MsgBox ("Creating File : " & NewFName)
newbk.SaveAs Filename:=NewFName
newbk.Close
End If
Next sf

End Sub


"Sam Commar" wrote:

Joel

Thanks for looking at this again however it still is not working for me.
The
only change I made is the name of the Root from
Root = "c:\Temp"
to
Root = "c:\Fentasia"

Running the macro did not do any processin .I have posted a Video of my
process at:

https://download.yousendit.com/TTZrT0Njckk1UjdIRGc9PQ

so maybe that may assist in guiding me what I am doing wrong as you can
visually see my whole process in the vide.

thanks again for all your help

Sam

"joel" wrote in message
...
I don't know why I didn't receive any notice on April 1st when my
original
code didn't work. Here is the code that I fully tested.

You siad the line

newbk.SaveAs Filename:=sf & "\" & _
sf.Name & ".xls"

failed.


1) Make Sure you change the ROOT in the code below.
2) the code is writing the New file to the same directory where the 3
files
are located. If the folder is write protected you coould get an error
3) The New file Name already exists. I added a msgbox to help isolate
the
problem.

Sub Combinebooks()

Root = "c:\Temp"


Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(Root)

For Each sf In folder.subfolders
First = True
FName = Dir(sf & "\*.xls")
Do While FName < ""
Set bk = Workbooks.Open(Filename:=sf & "\" & FName)
For Each sht In bk.Sheets
If First = True Then
sht.Copy
Set newbk = ActiveWorkbook
First = False
Else
With newbk
sht.Copy _
after:=.Sheets(.Sheets.Count)
End With
End If
Next sht
bk.Close savechanges:=False
FName = Dir()
Loop
NewFName = sf & "\" & sf.Name & ".xls"
MsgBox ("Creating File : " & NewFName)
newbk.SaveAs Filename:=NewFName
newbk.Close
Next sf

End Sub




"Sam Commar" wrote:

This code snippet works very well in combining multiple workbooks in
a
folder.
I wanted to request assitance in modifyin the snippet.

In my case every month I will have 24 directories and each directory
will
have 3 workbooks in it -one with 2 worksheets, another with 2
worksheets
and
one with 1 worksheet.
I want to combine the three workbooks in each Direcotry automatically
in
each of the 24 direcotires and name the workbook with the name of the
directory.


The code snippet below basically does the combining but only with the
user
interactive opeinig up a new workbook and then selecting the
workbooks to
merge and it does not create a new workbook with the new name

I would really appreciate it if someone can assist me with modifying
the
code for this

Option Explicit

'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal _
pszpath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) _
As Long

Public Type BrowseInfo
hOwner As Long
pIDLRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Function GetDirectory(Optional msg) As String
On Error Resume Next
Dim bInfo As BrowseInfo
Dim path As String
Dim r As Long, x As Long, pos As Integer

'Root folder = Desktop
bInfo.pIDLRoot = 0&

'Title in the dialog
If IsMissing(msg) Then
bInfo.lpszTitle = "Please select the folder of the excel
files to
copy."
Else
bInfo.lpszTitle = msg
End If

'Type of directory to return
bInfo.ulFlags = &H1

'Display the dialog
x = SHBrowseForFolder(bInfo)

'Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
GetDirectory = Left(path, pos - 1)
Else
GetDirectory = ""
End If
End Function

Sub CombineFiles()
Dim path As String
Dim FileName As String
Dim LastCell As Range
Dim Wkb As Workbook
Dim WS As Worksheet
Dim ThisWB As String

ThisWB = ThisWorkbook.Name
Application.EnableEvents = False
Application.ScreenUpdating = False
path = GetDirectory
FileName = Dir(path & "\*.xls", vbNormal)
Do Until FileName = ""
If FileName < ThisWB Then
Set Wkb = Workbooks.Open(FileName:=path & "\" & FileName)
For Each WS In Wkb.Worksheets
Set LastCell =
WS.Cells.SpecialCells(xlCellTypeLastCell)
If LastCell.Value = "" And LastCell.Address =
Range("$A$1").Address Then
Else
WS.Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt)
End If
Next WS
Wkb.Close False
End If
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

Set Wkb = Nothing
Set LastCell = Nothing
End Sub