Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: VBA Code to Combine files

  1. #1
    Join Date
    24th March 2013
    Posts
    34

    VBA Code to Combine files

    Hi guys,

    I'm having problems creating an efficient code that will go in a folder and get 4 files and combine them into one workbook with 4 tabs for each file and then add two additional file title Total 1 and Total 2. My problem is that I can seem to be able to name my location variables and declare them.

    The four files are contained here C:\Users\Allan Johnson\Desktop\Project 13. and they are titled Vendor.xlsx, Employees.xlsx, Cleaners.xlsx, and Misc.xlsx. Each new tab will have the file name. The new file will be titled Operating Epenses.xlsx and closed at the end. The macro will be accessed from a file titled Master.xlsm.

    Thank you in advance.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    1st September 2010
    Posts
    7,883

    Re: VBA Code to Combine files

    If you have existing code, then please post it... preferably with copies of your workbook(s) with sample data.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    24th March 2013
    Posts
    34

    Re: VBA Code to Combine files

    Project 13.zip
    Quote Originally Posted by cytop View Post
    If you have existing code, then please post it... preferably with copies of your workbook(s) with sample data.
    Hi Cytop,

    I deleted the code that I had last night because I couldn't get it to move. However, I have attached some files that I have. Please note I took all the day to reduce space.

    Also there will be other files in that folder so I just want to get the named files.

    Thanks for the help.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    1st September 2010
    Posts
    7,883

    Re: VBA Code to Combine files

    Confused - you mention "... into one workbook with 4 tabs for each file" then upload empty workbooks with 1 named tab. Can only go on your workbooks as uploaded so the following code loads the named tab from each workbook into a new, unnamed, workbook.

    VB:
    Sub x() 
         
         '// Change to suit
        Const strPath As String = "C:\TEMP\" 
         
        Dim cFiles() As Variant 
        Dim cFile As Variant 
         
        Dim wb As Excel.Workbook 
        Dim wbOpExp As Excel.Workbook 
         
        cFiles = Array("Vendors.xlsx", "Employees.xlsx", "Cleaners.xlsx", "Misc.xlsx") 
         
        Set wbOpExp = Application.Workbooks.Add 
         
        For Each cFile In cFiles 
             
            Set wb = Workbooks.Open(Filename:=strPath & cFile) 
             
            wb.Sheets(Left$(cFile, InStr(cFile, ".") - 1)).Copy Before:=wbOpExp.Sheets(1) 
             
            wb.Close savechanges:=xlDoNotSaveChanges 
            Set wb = Nothing 
             
        Next 
         
         
    End Sub 
    
    
    As the workbooks are empty, no titles, no suggestion as to the data layout or anything, there's nothing more I can suggest.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 20
    Last Post: January 19th, 2013, 00:59
  2. Combine two .xlsx files into one
    By dimko in forum EXCEL HELP
    Replies: 4
    Last Post: March 13th, 2012, 01:28
  3. Replies: 4
    Last Post: April 14th, 2011, 00:56
  4. Combine several *.xls files into one
    By jonny in forum EXCEL HELP
    Replies: 12
    Last Post: October 6th, 2010, 17:58
  5. Combine Several Files Into One
    By vickyho1008 in forum EXCEL HELP
    Replies: 1
    Last Post: October 13th, 2006, 22:21

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno