• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA macro to combine 2 workbooks into 1 workbook

B

brian w

New Member
Hello!

This is my first time posting here.

I am teaching myself to write VBA macros, and encountered this requirement:

I am joining hundreds of pairs of client workbooks from the same directory, one for the Type 1 commission workbook and one for the Type 2 commission workbook, for each client, into 1 workbook with two tabs.

What’s the correct code in VBA to accomplish this?

I have attempted to piece together solutions, but have not figured out how to open, copy and close many unique workbooks, while looping through a directory containing similar pairs of files.

I can send examples of the workbooks to be joined, if needed.

Any help you can offer would be greatly appreciated!

Thanks!
 
They follow this pattern:

Client1 Month Year Statement.xlsx
Client1 R Month Year Statement.xlsx
Client2 Month Year Statement.xlsx
Client2 R Month Year Statement.xlsx
Client3 Month Year Statement.xlsx
Client3 R Month Year Statement.xlsx
....
Client299 Month Year Statement.xlsx
Client299 R Month Year Statement.xlsx

where filename without "R" is commission Type 1 and filename with "R" is commission type 2.
 
And your final output would be one workbook with two worksheets, one for type 1 and the other for type 2? If yes, will each sheet have data for each client coming one below the other?

to make the assumptions clear, would you be able to post a few samples of input and output?
 
Sam,

Thanks for your help!!

All of the files following this pattern are in the same directory.

Client1 Month Year Statement.xlsx
Client1 R Month Year Statement.xlsx

The result could be combined into Client1 Month Year Statement.xlsx, and left in the same directory, or put in a new directory. Whichever is simplest.

The result could also a third file called Client1 Month Year Statement Client Copy.xlsx. Again, whatever is simplest.

The attached copies are examples of the two types of data files to be combined, and one of what they look like when combined.
 

Attachments

  • Company C June Statement.xlsx
    12.4 KB · Views: 9
  • Company C June Statement Client Copy.xlsx
    108 KB · Views: 7
  • Company C R June Statement.xlsx
    103 KB · Views: 6
OK, and do you have a list of the Clients somewhere? Or does this have to be picked from the folder dynamically?

By the way, pretty late here. If you don't get help by tomorrow, I'll get back to you.
 
The Client name is part of the file name, e.g. Microsoft Aug 2013 Statement.xlsx, Microsoft R Aug 2013 Statement.xlsx
 
Brian, got a bit delayed. Just wanted to check, what about the name of the months in the folder? Will there be multiple months? How would the code determine whether you're looking at Aug or Sep, or any other month for that matter?

Also, can we assume that if the type 1 file is present, then it is guaranteed that the type 2 file will also be present?
 
Sam,

There will only be one month of data at a time. I will use this macro monthly, but only for the current month.

Yes, if type 1 is present, type 2 is always also present.
 
Here's your code Brian

Code: 
Sub SMC()

    Const strFolder As String = "C:\Users\Sam Mathai\Downloads\"
    Const strMonthYear As String = "June 2013"
    Const strPattern As String = "* R " & strMonthYear & " Statement.xlsx"
    Dim strFile As String
    Dim wbk(1 To 2) As Workbook
    
    strFile = Dir(strFolder & strPattern)
    While strFile <> ""
        Set wbk(1) = Workbooks.Open(strFolder & strFile, , True)
        wbk(1).Sheets(1).Copy
        Set wbk(2) = ActiveWorkbook
        wbk(1).Close
        Set wbk(1) = Workbooks.Open(strFolder & Replace(strFile, " R ", " "), , True)
        wbk(1).Sheets(1).Copy Before:=wbk(2).Sheets(1)
        wbk(1).Close
        wbk(2).SaveAs strFolder & Replace(Replace(strFile, " R ", " "), ".xlsx", " Client Copy.xlsx")
        wbk(2).Close
        strFile = Dir
        Set wbk(1) = Nothing
        Set wbk(2) = Nothing
    Wend
    Erase wbk
    
End Sub
 
Thanks for your help, Sam!

However, I have another request: please help me to figure out why the code above is not doing anything with my files.

I tried it in my application, by changing the path to my target , and pasting the code into a module, in the first file in the target directory.

Also, I created a new directory, with a path that looks like the one you give in the code, containing the files

Client1 Month Year Statement.xlsx
Client1 R Month Year Statement.xlsx
Client2 Month Year Statement.xlsx
Client2 R Month Year Statement.xlsx
Client3 Month Year Statement.xlsx
Client3 R Month Year Statement.xlsx

and pasted the code a module in the first file.

What am I doing incorrectly?

Do I need a different syntax with "dir"?
 
Thanks for your help, Sam!

However, I have another request: please help me to figure out why the code above is not doing anything with my files.

I tried it in my application, by changing the path to my target , and pasting the code into a module, in the first file in the target directory.

Also, I created a new directory, with a path that looks like the one you give in the code, containing the files

Client1 Month Year Statement.xlsx
Client1 R Month Year Statement.xlsx
Client2 Month Year Statement.xlsx
Client2 R Month Year Statement.xlsx
Client3 Month Year Statement.xlsx
Client3 R Month Year Statement.xlsx

and pasted the code a module in the first file.

What am I doing incorrectly?

Do I need a different syntax with "dir"?
 
You must log in or register to reply here.
Back
Top