Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Automatically open, copy, and close multiple workbooks

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Automatically open, copy, and close multiple workbooks

    I have 77 excel workbooks that are created each week, they are all in the same directory. I have to open each one and copy the data into a single workbook. The file names change slightly each week.

    example: DIST_91124_GROWTH_PRODUCT XXX _07072006.xls The 07072006 is the week ending date and will change. Each of the 77 files has a different DIST_number.

    Is there a way to automate this process, it takes me about 4 hours to do it manually.

  • #2
    Re: Automatically open, copy, and close multiple workbooks

    Hi,

    Are all the files in the same directory? Are there any other files in that directory that aren't part of equation? What is it that you want to copy? Can you post a small workbook sample to work with?

    JMAN

    Comment


    • #3
      Re: Automatically open, copy, and close multiple workbooks

      Yes they are all in the same directory and NO other files are in that directory

      Comment


      • #4
        Re: Automatically open, copy, and close multiple workbooks

        What is that you want to copy? This would be a lot easier if you attached a sample of your data or were a little more descriptive.

        Comment


        • #5
          Re: Automatically open, copy, and close multiple workbooks

          What I have to do is open every one of the files in this directory, Select one of 3 tabs, copy that data into a new workbook. All the files have to be copied into the same workbook each below the previous copy. When done I have one worksheet with all the data from all workbooks. I have about 5000 rows of data when done. I use the data with macros I have written to produce several reports.

          Comment


          • #6
            Re: Automatically open, copy, and close multiple workbooks

            TJE,

            I can't help you with the code if I don't know what range you want copy, which sheet to copy from, what range to copy to, what sheet to copy to. All these small details matter, that's why I asked for a small workbook sample. See the sample code below and you can fill in the necessary data.

            Code:
            Sub RunCodeOnAllXLSFiles()
            Dim lCount As Long
            Dim wbResults As Workbook
            Dim wbCodeBook As Workbook
            
            Application.ScreenUpdating = False
            Application.DisplayAlerts = False
            Application.EnableEvents = False
            
            On Error Resume Next
            
            Set wbCodeBook = ThisWorkbook
            
                With Application.FileSearch
                    .NewSearch
                     'Change path to suit
                    .LookIn = "C:\MyDocuments\TestResults"
                    .FileType = msoFileTypeExcelWorkbooks
                    '.Filename = "Book*.xls"
                    
                        If .Execute > 0 Then 'Workbooks in folder
                            For lCount = 1 To .FoundFiles.Count 'Loop through all.
                             'Open Workbook x and Set a Workbook variable to it
                             Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                                              
                             'DO YOUR CODE HERE
                             
                             wbResults.Close SaveChanges:=True
                         
                             Next lCount
                        End If
                End With
                
                On Error GoTo 0
                Application.ScreenUpdating = True
                Application.DisplayAlerts = True
                Application.EnableEvents = True
            End Sub
            Last edited by JMAN; July 26th, 2006, 22:57.

            Comment


            • #7
              Re: Automatically open, copy, and close multiple workbooks

              Thankyou, I'll try to figure out how to use that code. I am also sending examples of my files.

              WBExample.xls is the one to copy data from. All the data on the geography tab

              CopytoWB.xls is the one to copy into

              They are all in a directory c:\My Documents\Weeklies

              Thanks again for your help

              Comment


              • #8
                Re: Automatically open, copy, and close multiple workbooks

                I uset the code you posted, inserted my data and it works perfectly. Will be a wonderful time saver. Thank you soooo much

                Comment


                • #9
                  Re: Automatically open, copy, and close multiple workbooks

                  Originally posted by TJE
                  I uset the code you posted, inserted my data and it works perfectly. Will be a wonderful time saver. Thank you soooo much

                  Glad to help, but you can thank Dave Hawley..That sample came from his macros page

                  Comment


                  • #10
                    Re: Automatically open, copy, and close multiple workbooks

                    I have one little problem, when the workbooks are created, the first time they are opened a macro runs to populate the workbook. It doesn't run when I use your code and all I copy is a blank sheet. What code do I need to insert to ensure the macro runs on startup? The code works great on books I have already opened and saved.
                    Confusing I know, long story short I need code to force the auto macro to run before the copy and paste in my macro.

                    Comment


                    • #11
                      Re: Automatically open, copy, and close multiple workbooks

                      If I understand right, you have another macro that you created that needs to run before this runs right? If that's the case then you can either insert
                      Code:
                      Call "Your macro name here" 'Without the quotes
                      then continue with the new code or you can insert your other macro as a workbook event that will fire as soon as the workbook is opened.

                      JMAN
                      Last edited by JMAN; July 26th, 2006, 23:53.

                      Comment

                      Trending

                      Collapse

                      There are no results that meet this criteria.

                      Working...
                      X