Combining Multiple Excel Files form One Folder into a Single Worksheet

[itzou]

Hi All,

I am trying to copy Row 2 forward on multiple excels from a single into a single worksheet. I have used this macro previously, but I keep getting a "runtime error '1004'". What am I doing wrong or could someone give me a VBA code that I could use?

Sub MergeFiles()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
Dim RowofCopySheet As Integer

RowofCopySheet = 2 ' Row to start on in the sheets you are copying from

ThisWB = ActiveWorkbook.Name

path = ("Folder Location")

Application.EnableEvents = False
Application.ScreenUpdating = False

Set shtDest = ActiveWorkbook.Sheets(1)
Filename = Dir(path & "\*.xlsx", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
If Not Filename = ThisWB Then
Set Wkb = Workbooks.Open(Filename:=path & "" & Filename)
Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
CopyRng.Copy Dest
Wkb.Close False
End If

Filename = Dir()
Loop

Range("A1").Select

Application.EnableEvents = True
Application.ScreenUpdating = True

MsgBox "Done!"
End Sub

 

[itzou]

I got an error "License information for this component not found" after I changed the folder location

 

[mumps]

Replace this line of code:

Const strPath As String = "C:\Test\"

with this line:

Const strPath As String = "U:\Documents\New folder\"

and try the macro again.

 

[itzou]

"Run time error: 429'

ActiveX Component can't creat eobject

 

[mumps]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[itzou]

It worked! I restarted my computer and updated Active X controls in IE. Thanks for your help!!

 

[RasGhul]

Hi itzou

Folder paths should end with \ and filenames finish without the \ Ensure that your code completes the path(s) correctly hence the 1004 error.


Note in your code;

[COLOR=#333333]path = ("[/COLOR][COLOR=#0000ff]Folder Location"[/COLOR][COLOR=#333333]) -[/COLOR][COLOR=#0000ff]Make sure the Folder path is U:\Documents\New folder\ ending with the backslash[/COLOR][COLOR=#333333]
[/COLOR]
[COLOR=#333333]Application.EnableEvents = False[/COLOR]
[COLOR=#333333]Application.ScreenUpdating = False[/COLOR]

[COLOR=#333333]Set shtDest = ActiveWorkbook.Sheets(1)[/COLOR]
[COLOR=#333333]Filename = Dir(path & "[/COLOR][B][COLOR=#ff0000]\[/COLOR][/B][COLOR=#333333]*.xlsx", vbNormal)[/COLOR][COLOR=#333333]- [/COLOR][COLOR=#ff0000]Delete this backslash, as its already in path[/COLOR]

You can see this in mumps code. Otherwise your full path line output will be U:\Documents\New folder\ \*.xlsx

 

[mumps]

You are very welcome. I'm glad it worked out.