merge excel files

i have a semi-regular task to merge excel files.
I am seeking a way to do this without having to open and copy each file.
One worksheet in each file.
The example files attached.
EEExample-1.xlsx
EEExample-2.xlsx
EEExample-3.xlsx
gregfthompsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
How do you wish to "merge" the contents of the three files?

In one resultant worksheet in another workbook, with the successive rows from each successive file in sequential order?

A resultant worksheet in a new workbook with the rows intermixed in as (as yet) unspecified sorted/ranked order?

In three separate worksheets in a resultant workbook without changing the contents of the individual source worksheets?

A result In one of the three existing workbooks?

...or something else?
0
aikimarkCommented:
to help clarify what you need, please post a workbook that is the result of you manually merging the three workbooks you have already posted.
0
gregfthompsonAuthor Commented:
Thanks aikimark,

I've attached a file showing how the file can be merged -
in one resultant worksheet in another workbook, with the successive rows from each successive file in sequential order.

Thanks again,

Greg
EEExample-manually-merged-.xlsx
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

aikimarkCommented:
in your manual stacking of the three worksheets, you have done some editing.  For example, there are a few rows at the top of the second worksheet that are not present in the merged worksheet.
0
gregfthompsonAuthor Commented:
Hi aikimark,

There's no need to include any editing.
The content of the entire worksheet can be copied.

Thanks,

Greg
0
[ fanpages ]IT Services ConsultantCommented:
You're welcome, Greg.
0
aikimarkCommented:
Please test this.
You will need to change the path constant and the pattern of workbook names being iterated.
Sub Q_28705779()
    Dim wkbSrc As Workbook
    Dim wksSrc As Worksheet
    Dim rngSrc As Range
    
    Dim wkbTgt As Workbook
    Dim wksTgt As Worksheet
    Dim rngTgt As Range
    
    Dim strFilename As String
    Const cPath As String = "C:\users\aikimark\downloads\"
    
    Application.ScreenUpdating = False
    
    Set wkbTgt = Workbooks.Add
    Set wksTgt = wkbTgt.Worksheets(1)
    Set rngTgt = wksTgt.Cells(1, 1)
    
    strFilename = Dir(cPath & "EEExample-*.xlsx")
    
    Do Until Len(strFilename) = 0
        Set wkbSrc = Workbooks.Open(Filename:=cPath & strFilename, ReadOnly:=True)
        Set wksSrc = wkbSrc.Worksheets(1)
        Set rngSrc = wksSrc.UsedRange
        rngSrc.Copy rngTgt
        Set rngTgt = wksTgt.Cells.SpecialCells(xlCellTypeLastCell)
        Set rngTgt = rngTgt.EntireRow.Cells(1, 1).Offset(1)
        
        wkbSrc.Close False
        strFilename = Dir
    Loop
    
    Application.ScreenUpdating = True
End Sub

Open in new window

0
gregfthompsonAuthor Commented:
Thanks aikimark,

I created a new excel file in the same directory as all other files. Copied the macro and ran it.

It appeared that nothing happened.

What did I do wrong?

Thanks,

Greg
0
aikimarkCommented:
Did you make the changes I specified?
Please post the code you executed.
0
gregfthompsonAuthor Commented:
Hi aikimark,

Here's my adjusted version:
Sub Q_28705779()
    Dim wkbSrc As Workbook
    Dim wksSrc As Worksheet
    Dim rngSrc As Range
    
    Dim wkbTgt As Workbook
    Dim wksTgt As Worksheet
    Dim rngTgt As Range
    
    Dim strFilename As String
    Const cPath As String = "C:\MergeFolder"
    
    Application.ScreenUpdating = False
    
    Set wkbTgt = Workbooks.Add
    Set wksTgt = wkbTgt.Worksheets(1)
    Set rngTgt = wksTgt.Cells(1, 1)
    
    strFilename = Dir(cPath & "3000 - 3010 A rent Apr 2015-*.xlsx")
    
    Do Until Len(strFilename) = 0
        Set wkbSrc = Workbooks.Open(Filename:=cPath & strFilename, ReadOnly:=True)
        Set wksSrc = wkbSrc.Worksheets(1)
        Set rngSrc = wksSrc.UsedRange
        rngSrc.Copy rngTgt
        Set rngTgt = wksTgt.Cells.SpecialCells(xlCellTypeLastCell)
        Set rngTgt = rngTgt.EntireRow.Cells(1, 1).Offset(1)
        
        wkbSrc.Close False
        strFilename = Dir
    Loop
    
    Application.ScreenUpdating = True
End Sub

Open in new window

0
aikimarkCommented:
There needs to be a "\" separating the path from the file name
0
gregfthompsonAuthor Commented:
Hi aikimark,

I do not know what has happened here.

Please include the line where the backslash "\" is missing.

And what happened when you  "moved code to snippet"

Thanks.

Greg
0
[ fanpages ]IT Services ConsultantCommented:
Greg:

There are two possible places to add the "\" (Application PathSeparator), this is one of them:

Const cPath As String = "C:\MergeFolder"
...becomes...
Const cPath As String = "C:\MergeFolder\"

This is the other:

Set wkbSrc = Workbooks.Open(Filename:=cPath & strFilename, ReadOnly:=True)
...becomes...
Set wkbSrc = Workbooks.Open(Filename:=cPath & "\" & strFilename, ReadOnly:=True)


Change one, or the other, but not both.

"Moving code to snippet" means moving code statements, like:

    Set wkbTgt = Workbooks.Add
    Set wksTgt = wkbTgt.Worksheets(1)
    Set rngTgt = wksTgt.Cells(1, 1)

...to a dedicated "code" panel, thus:

    Set wkbTgt = Workbooks.Add
    Set wksTgt = wkbTgt.Worksheets(1)
    Set rngTgt = wksTgt.Cells(1, 1)

Open in new window


Nothing is changed other than the presentation on the page.
0
gregfthompsonAuthor Commented:
PS While I have been a grateful user of experts exchange, my knowledge of its website functions is limited.
0
gregfthompsonAuthor Commented:
I attempted to move code to snippet in excel without successs.

here is the code, revised with the "\".

It is not yet working..

Sub Q_28705779()
    Dim wkbSrc As Workbook
    Dim wksSrc As Worksheet
    Dim rngSrc As Range
   
    Dim wkbTgt As Workbook
    Dim wksTgt As Worksheet
    Dim rngTgt As Range
   
    Dim strFilename As String
    Const cPath As String = "C:\MergeFolder\"
   
    Application.ScreenUpdating = False
   
    Set wkbTgt = Workbooks.Add
    Set wksTgt = wkbTgt.Worksheets(1)
    Set rngTgt = wksTgt.Cells(1, 1)
   
    strFilename = Dir(cPath & "3000 - 3010 A rent Apr 2015-*.xlsx")
   
    Do Until Len(strFilename) = 0
        Set wkbSrc = Workbooks.Open(Filename:=cPath & strFilename, ReadOnly:=True)
        Set wksSrc = wkbSrc.Worksheets(1)
        Set rngSrc = wksSrc.UsedRange
        rngSrc.Copy rngTgt
        Set rngTgt = wksTgt.Cells.SpecialCells(xlCellTypeLastCell)
        Set rngTgt = rngTgt.EntireRow.Cells(1, 1).Offset(1)
       
        wkbSrc.Close False
        strFilename = Dir
    Loop
   
    Application.ScreenUpdating = True
End Sub
0
gregfthompsonAuthor Commented:
It creates a new file, but the file is empty.
0
aikimarkCommented:
It creates a new file
It should create a new workbook.  It is up to you to save it, creating a new file.

Please run the following and post the contents of the Immediate Window.
Sub Q_28705779()
    Dim wkbSrc As Workbook
    Dim wksSrc As Worksheet
    Dim rngSrc As Range
    
    Dim wkbTgt As Workbook
    Dim wksTgt As Worksheet
    Dim rngTgt As Range
    
    Dim strFilename As String
    Const cPath As String = "C:\MergeFolder\"
    
    Application.ScreenUpdating = False
    
    Set wkbTgt = Workbooks.Add
    Set wksTgt = wkbTgt.Worksheets(1)
    Set rngTgt = wksTgt.Cells(1, 1)
    
    strFilename = Dir(cPath & "3000 - 3010 A rent Apr 2015-*.xlsx")
Debug.Print strFilename
    Do Until Len(strFilename) = 0
        Set wkbSrc = Workbooks.Open(Filename:=cPath & strFilename, ReadOnly:=True)
        Set wksSrc = wkbSrc.Worksheets(1)
        Set rngSrc = wksSrc.UsedRange
        rngSrc.Copy rngTgt
        Set rngTgt = wksTgt.Cells.SpecialCells(xlCellTypeLastCell)
        Set rngTgt = rngTgt.EntireRow.Cells(1, 1).Offset(1)
        
        wkbSrc.Close False
        strFilename = Dir
Debug.Print strFilename
    Loop

Open in new window

0
gregfthompsonAuthor Commented:
Hi aikimark,

Thanks again.

It stopped. It looks like it is close.

Message said it "expected end sub"

Thanks,

Greg
0
aikimarkCommented:
sorry.  The last two lines didn't transfer.  Please add
    
    Application.ScreenUpdating = True
End Sub

Open in new window

0
aikimarkCommented:
Please use the Code link (above the edit textbox) to put your code into a snippet.
0
gregfthompsonAuthor Commented:
Thanks. (and for the Code link hint)

The macro created a new file, but empty.
0
aikimarkCommented:
What was in the Immediate window?
0
gregfthompsonAuthor Commented:
Not sure what is meant by immediate window,

Sheet 1 in the Merged 17 August 2015.xlsm file was empty. (ands Sheet 2 was also empty.

Book 3.xlsx Sheet 1 was empty.
0
aikimarkCommented:
That was in the Immediate window?!?
0
gregfthompsonAuthor Commented:
Hi aikimark,

I'm sorry, but I don't know what is meant by "immediate window".

Thanks,

Greg
0
aikimarkCommented:
Ctrl+G

or via the menu: View | Immediate Window
0
gregfthompsonAuthor Commented:
Thanks.

The immediate window is completely empty.
0
aikimarkCommented:
please verify that you have workbook files that match the 3000 - 3010 A rent Apr 2015-*.xlsx pattern in the directory.
0
gregfthompsonAuthor Commented:
I do not know what is meant by the "pattern".

Thanks Greg
0
aikimarkCommented:
pattern
The file names of the workbooks you are seeking.  That string is what the DIR function is using to return the subset of files to the program.

What are the actual names of the files in that directory?
0
gregfthompsonAuthor Commented:
3000 - 3010 A rent Apr 2015.xlsx
3011 - 3015.xlsx
3016 - 3020.xlsx
0
aikimarkCommented:
3000 - 3010 A rent Apr 2015-*.xlsx does not match any of these three file names

Please try this pattern:
30?? - 30??*.xlsx
0
gregfthompsonAuthor Commented:
I made the amendment to the script - nothing happened.

Amended script is attached.
merge-excel-file-script.txt
0
aikimarkCommented:
And you are sure that those files are in the c:\mergefiles folder?

Set a breakpoint on the first debug.print line and inspect the strFilename variable's value.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gregfthompsonAuthor Commented:
Thanks heaps.

All working.

Great job. Thanks for your patience.

Greg
0
aikimarkCommented:
Thanks for the points.

What did you do differently this run or with the code that made it work this time?
0
[ fanpages ]IT Services ConsultantCommented:
You're welcome, Greg.
0
gregfthompsonAuthor Commented:
Simply corrected the directory name...  (duh!).

More haste, less speed. I've reprimanded myself.
0
gregfthompsonAuthor Commented:
Hi,

I tried to run this again and it stopped at

" rngSrc.Copy rngTgt"

What did I do wrong?

Thanks,

Greg
0
aikimarkCommented:
@gregfthompson

Please open a new question and allow the experts to help you with your new question.  This thread has been closed for over a month.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.