CombineFiles - Move instead of Copy

[michael1284]

I found the awesome CombineFiles macro from the knowledge base but I need it to combine workbooks in a folder by moving instead of copying due to the number of characters that we have in each cell. Can anyone help?

Here is the code:

[vba]

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.Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
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
[/vba]


Thanks!!

[RonMcK]

Michael,

I believe the following should work for you. I'm on a Mac so I'm unable to test it, however, when you compare this code with the original, you'll see that all I had to do was eliminate part of the IF-THEN, keeping the operative piece of the ELSE.
[vba]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
WS.Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
Wkb.Close False
End If
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

Set Wkb = Nothing
Set LastCell = Nothing
End Sub
[/vba] The following change is just a slight editorial change:
[vba] 'Title in the dialog
If IsMissing(msg) Then
bInfo.lpszTitle = "Please select the folder of the excel files to combine."
Else
bInfo.lpszTitle = msg
End If

[/vba]
HTH,

[michael1284]

Ron,

Thanks for your help. After running it, I got an automation error. Clicking debug it highlighted the following:

[VBA]
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
WS.Move After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Next WS
Wkb.Close False
End If
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

Set Wkb = Nothing
Set LastCell = Nothing
End Sub
[/VBA]

[RonMcK]

Michael,

Did this program work for you, before? (Albeit that it was truncating cells with 'too much text' for Excel's liking.)

Just curious,

[michael1284]

Yeah it worked for me before. I actually just deleted the part that says:

Wkb.Close False


Now it seems to be working fine. I am still testing. So far I have not found any errors by taking that part of the macro out. Can anyone see any errors in the code?

Thanks for your help Ron!

[RonMcK]

Michael,

Please consider:

Replacing: wkb.close false
with: wkb.Close savechanges:=False, FileName:=FileName

The program wants to close each source workbook after it's done using it. This is probably mostly just good housekeeping but too many open large files may degrade your system's performance.

Cheers,

[mdmackillop]

The problem is that you cannot have a workbook with no sheets. It appears that when the last sheet is moved, the source workbook is closed without saving, causing the error.

[michael1284]

Gotcha, that would make sense that it would work once I removed that line. Either that or I could add a blank sheet to each workbook.

[mdmackillop]

Do you want to keep the workbooks with nothing in them? If not, just copy the sheets and then close and delete the workbooks.