Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Access: VBA combining multiple excel files to 1 new sheet

  1. #1
    Join Date
    3rd September 2003
    Posts
    5
    I have multiple worksheets spread across multiple Excel files (1 worksheet per file). All files are stored in the same folder, and all worksheets have the same column headers and structure. I need a block of code that will combine all of these worksheets into a single worksheet in a master Excel file. That is, the code needs to:

    1. Open the first Excel file.
    2. Copy the first worksheet's contents into the first worksheet of the master file, beginning at the next empty row it finds.
    3. Close the Excel file, and move on to the next file.
    4. Repeat.

    So in the end, ten worksheets residing on ten different Excel files will be combined into a single worksheet in a single file. No breaks are needed between them, instead, the last row of a worksheet would be followed by the first row of the next one immediately below it. No aggregate functions involved, no sums, nothing like that (which is why I don't think I can use the Consolidate function in Excel). Hope all this detail helps! Thanks in advance for the help!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,343
    Take a look at Dave's suggestion about pulling data from a closed Workbook.

    http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm

    It will need a little adapting for your purposes.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,343
    This code assumes you have a file named "Master.xls". It will open all Excel files in a given directory (C:Temp) and copy the first sheet of each file to the end of "Master". This can be used to compile the sheets from each workbook into a single Master workbook.After which you can copy and paste (use code if necessary) to one sheet



    VB:
    Sub Combine() 
         
         
        Fpath = "C:Temp" ' change to suit your directory
        Fname = Dir(FilePth & "*.xls") 
         
        Do While Fname <> "" 
            Workbooks.Open Fpath & Fname 
            Sheets(1).Copy After:=Workbooks("Master.xls").Sheets(Workbooks("Master.xls").Sheets.Count) 
            Workbooks(Fname).Close SaveChanges:=False 
            Fname = Dir 
        Loop 
         
    End Sub 
    
    
    Last edited by Dave Hawley; August 9th, 2005 at 09:03.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  4. #4
    Join Date
    18th July 2006
    Posts
    4

    Re: Access: VBA combining multiple excel files to 1 new sheet

    what if you want to copy multiple sheets from 10 different workbooks? So, the master workbook has 3 sheets and each other workbook has those 3 same sheets.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th July 2006
    Posts
    4

    Re: Access: VBA combining multiple excel files to 1 new sheet

    Sorry, I needed to mention that the other workbooks that I would be coping from have other worksheets in them that I do not want. I just need those three sheets from each workbook.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2004
    Posts
    10,539

    Re: Access: VBA combining multiple excel files to 1 new sheet

    Could you start a new thread - this one is almost 3 years old.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    11th January 2011
    Posts
    9

    Re: Access: VBA combining multiple excel files to 1 new sheet

    Hi. I am running the following code posted by royUK..but I am getting runtime error 2302 ..master.xls subscrot out of range.
    Error on this line :"Sheets(1).Copy After:=Workbooks("Master.xls").Sheets(Workbooks("Master.xls").Sheets.Count)"
    Thanks for your comments.

    VB:
     
    Sub Combine() 
         
        Fpath = "C:Temp" ' change to suit your directory
        Fname = Dir(FilePth & "*.xls") 
         
        Do While Fname <> "" 
            Workbooks.Open Fpath & Fname 
            Sheets(1).Copy After:=Workbooks("Master.xls").Sheets(Workbooks("Master.xls").Sheets.Count) 
            Workbooks(Fname).Close SaveChanges:=False 
            Fname = Dir 
        Loop 
         
    End Sub 
    
    
    Last edited by AAE; January 11th, 2011 at 05:13. Reason: add code tags

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    11th January 2011
    Posts
    9

    Re: Access: VBA combining multiple excel files to 1 new sheet

    I am creating variable amount of XL files using Access VBA and output into a directory. Each XL file
    has different columns with a single sheet. I wanted to copy each sheet from all the XL files in the
    directory into a master XL workbook with multiple sheets so there is only xl file. How can I do this?
    When I run the above code I get subscript out of range error.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Access: VBA combining multiple excel files to 1 new sheet

    Hello lonemane,

    Welcome to Ozgrid. While we're glad to have you on board, please note:

    Posting your question in threads started by others is a violation of the forum rules and is known as thread hijacking. Posting solutions is acceptable.
    ALWAYS start a new thread for your question and if you think it is helpful to clarify your thread you may include a link back this thread (or any other).

    Start a new thread and be sure to give it a title that is in compliance with the following:

    Thread titles are used in searching the forum, therefore, it is vital the be written to accurately describe your thread content or overall objective using ONLY search friendly key words.

    • The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    • The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters
    • The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different


    Also:

    All VBA code posted in the forum must be wrapped in code tags which you omitted. I've added the tags for you this time only. Be sure to use them in future posts.

    How to use code tags

    [code]
    your code goes between these tags
    [/code]
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Import Multiple Access Files
    By E.D.R in forum Excel and/or Access Help
    Replies: 1
    Last Post: January 23rd, 2008, 03:14
  2. Querry Access Records And Files From S/sheet
    By HarryBedi in forum Excel and/or Access Help
    Replies: 4
    Last Post: September 19th, 2006, 17:18
  3. Combining Access and Excel
    By Tiavas in forum EXCEL HELP
    Replies: 2
    Last Post: May 23rd, 2006, 02:09
  4. combining multiple sheets into one sheet
    By Swieduwi in forum EXCEL HELP
    Replies: 1
    Last Post: October 28th, 2005, 04:58
  5. Combining saved excel worksheet files to one w
    By dmbnd7 in forum EXCEL HELP
    Replies: 7
    Last Post: June 25th, 2003, 02:44

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