Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Automatically open, copy, and close multiple workbooks

  1. #1
    Join Date
    9th November 2005
    Location
    Indiana
    Posts
    85

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd November 2005
    Posts
    894

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    9th November 2005
    Location
    Indiana
    Posts
    85

    Re: Automatically open, copy, and close multiple workbooks

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd November 2005
    Posts
    894

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    9th November 2005
    Location
    Indiana
    Posts
    85

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    22nd November 2005
    Posts
    894

    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.

    VB:
    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 at 22:57.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    9th November 2005
    Location
    Indiana
    Posts
    85

    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    9th November 2005
    Location
    Indiana
    Posts
    85

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    22nd November 2005
    Posts
    894

    Re: Automatically open, copy, and close multiple workbooks

    Quote 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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    9th November 2005
    Location
    Indiana
    Posts
    85

    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.

    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. Close All Open Workbooks Except......
    By Andrew5 in forum EXCEL HELP
    Replies: 3
    Last Post: February 25th, 2008, 20:20
  2. Open, Update and Close Multiple Workbooks
    By insea in forum EXCEL HELP
    Replies: 5
    Last Post: September 5th, 2007, 09:09
  3. Open Multiple Workbooks & Copy Sheets
    By newonjob in forum EXCEL HELP
    Replies: 3
    Last Post: July 17th, 2007, 20:46
  4. Replies: 12
    Last Post: February 1st, 2005, 16:44
  5. Close all open workbooks in one go
    By XL-Dennis in forum OPEN SOURCE: Hey! That is Cool!
    Replies: 0
    Last Post: March 3rd, 2003, 00:00

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