PDA

View Full Version : VBA Combine multiple workbooks into one master HELP!



cyy1315
06-06-2017, 03:36 AM
Hi I am trying to combine multiple workbooks into one master workbooks but it has an error of 54 that cannot find my file. I realized my dir() function does not work in my excel. Could anyone help please???

Sub copypastecolumns()


Dim directoryPath As String
Dim filepath As String
Dim filename As String
Dim Lastrow As Long, Lastcolumn As Long




directoryPath = "/Users/cathy/Documents/vba/"
filepath = directoryPath & "*.xlsx"
filename = Dir(filepath)




MsgBox ("hi")<---- it doesnt even get to here and shows the error of 54,



Do While Len(filepath) > 0
If MyFile = "workbook6.xlsx" Then
Exit Sub
End If



Workbook.Open (filepath & MyFile)
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(Lastrow, Lastcolumn)).Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 6))
MsgBox ("hi2")


MyFile = Dir
Loop




End Sub

mdmackillop
06-06-2017, 03:57 AM
You appear to be missing a drive letter such as
directoryPath = "C:/Users/cathy/Documents/vba/"

cyy1315
06-06-2017, 04:13 AM
I added it and it says path not founded

cyy1315
06-06-2017, 04:16 AM
error 76

mdmackillop
06-06-2017, 05:03 AM
I see you are using / instead of \; maybre try the backslash.
or
Copy the path from Explorer to ensure there are no typos and edit it accordingly.

Edit: Where to you get MyFile from?

Jan Karel Pieterse
06-06-2017, 06:41 AM
I expect you are using a re-directed path to your documents folder. If you open Explorer and go to your My Documents folder (the one you see as a sub-folder beneath your username, NOT the one under Libraries!) and right-click it and choose Copy address as text, what does it paste precisely?

cyy1315
06-06-2017, 06:59 AM
i looked it up at the Terminal of Macbook so I assume the address should be correct?

Jan Karel Pieterse
06-06-2017, 07:29 AM
I don't think MAC Excel allows the use of the Dir function in VBA I'm afraid.

cyy1315
06-06-2017, 07:31 AM
oh really?! Thanks for reminding me

cyy1315
06-06-2017, 08:48 AM
I used windows for this code now, but it only shows the message of "hi", not even the second one. Could you guys help with this please?

Sub copyandpastecolumns()
Dim directoryPath As String
Dim filepath As String
Dim filename As String
Dim Lastrow As Long, Lastcolumn As Long




directoryPath = "C:\Users\Kent\Documents\"
filepath = directoryPath & "*.xlsx"
filename = Dir(filepath)






MsgBox ("hi")


Do While Len(filename) > 0
If filename = "workbook6.xlsx" Then
Exit Sub
End If



MsgBox ("hi2")


Workbook.Open (filepath & MyFile)
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(Lastrow, Lastcolumn)).Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 6))
MsgBox ("hi2")


MyFile = Dir
Loop




End Sub

Jan Karel Pieterse
06-06-2017, 09:42 PM
I made a couple of changes.
I advise you to add the Option Explicit at the top of your modules to force variable declaration, this would prevent a couple of errors. (Tools, Options, second checkbox)


Option Explicit
Sub copyandpastecolumns()
Dim directoryPath As String
Dim filepath As String
Dim filename As String
Dim Lastrow As Long
Dim Lastcolumn As Long
Dim erow As Long

directoryPath = "C:\Users\piete\Documents\"
filepath = directoryPath & "*.xlsx"
filename = Dir(filepath)



MsgBox ("hi")

Do While Len(filename) > 0
'Think these are not needed
' If filename = "workbook6.xlsx" Then
' Exit Sub
' End If

MsgBox ("hi2")

Workbooks.Open directoryPath & filename
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(2, 1), Cells(Lastrow, Lastcolumn)).Copy
ActiveWorkbook.Close
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Cells(erow, 1)
MsgBox ("hi2")
'Note the parentheses and use of the correct variable:
filename = Dir()
Loop


End Sub

cyy1315
06-06-2017, 11:00 PM
Hi it works now, but how can i copy and paste the data from newly added workbook? How can i add the button and put it into the code?

Jan Karel Pieterse
06-07-2017, 12:22 AM
I am not sure what you are asking for precisely? The code already copies and pastes?