Announcement

Collapse
No announcement yet.

Access: VBA combining multiple excel files to 1 new sheet

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

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



    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!

  • #2
    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

    New users should read the Forum Rules before posting

    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.

    Comment


    • #3
      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



      Code:
      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, 09:03.
      Hope that Helps

      Roy

      New users should read the Forum Rules before posting

      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.

      Comment


      • #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.

        Comment


        • #5
          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.

          Comment


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

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

            Comment


            • #7
              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.

              Code:
               
              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, 06:13. Reason: add code tags

              Comment


              • #8
                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.

                Comment


                • #9


                  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

                  Comment

                  Working...
                  X