Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Open Workbook, Run Macro, Save - Automate

  1. #1
    Join Date
    12th April 2005
    Posts
    6

    Open Workbook, Run Macro, Save - Automate

    I have 5 Excel files. The first one is Main, and the other 4 are subfiles, namely A1, A2, A3 & A4.

    I have a need to open Main, then click a button to start a Macro (in Main) which will do the following, sequentially:
    1. open A1
    2. run a Macro in A1
    3. close and save A1.
    4. open A2
    5. run a Macro in A2
    ...
    12 close and save A4.

    The files A1..A4 are a file server and being shared. If they're being used by another user, it cannot be saved - so I would have to skip it and continue with the next A.

    It would be great if start Excel, open Main, and run the Macro in Main fully automatically.

    Some pointers will be great. Thanks.

    Frankie LAU.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    20th April 2005
    Location
    Morgan Hill, CA, USA
    Posts
    37

    Re: Open Workbook, Run Macro, Save - Automate

    Is this the sort of thing you had in mind?
    VB:
    Dim ThisWkbkNm As String 
    ThisWkbkNm = ActiveWorkbook.Name 
    . . . 
     'Open/Close "WorkbookA.xls"
    Workbooks.Open Filename:="C:\Excel Documents\WorkbookA.xls" 
    Application.Run "'WorkbookA.xls'!MyMacroNameA" 
    On Error Resume Next 
    Workbooks("WorkbookA.xls").Close SaveChanges:=True 
     
     'Open & Close next workbk
    Workbooks.Open Filename:="C:\Excel Documents\WorkbookB.xls" 
    Application.Run "'WorkbookB.xls'!MyMacroNameB" 
    On Error Resume Next 
    Workbooks("WorkbookB.xls").Close SaveChanges:=True 
     
    Workbooks(ThisWkbkNm).Activate 'Reactivate this workbook
    . . . 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th April 2005
    Posts
    6

    Re: Open Workbook, Run Macro, Save - Automate

    thanks Cyberdude,
    it worked smoothly on a small test files.
    I'm ready to port it to a real case.

    I'd need to handle shared file(s) possibly opened by other users. Hope it does not pose new problems. Since I'd not be able to save the open'd file, I'd need to skip its processing and continue with others.

    regards,
    Frankie.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    20th April 2005
    Location
    Morgan Hill, CA, USA
    Posts
    37

    Re: Open Workbook, Run Macro, Save - Automate

    Good show, Frankie. Glad to help.
    Sid

    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. Save Copy Of Workbook & Keep Both Open
    By beegee in forum EXCEL HELP
    Replies: 4
    Last Post: June 24th, 2008, 06:04
  2. Open Workbook, Update, Save & Close
    By Tom Berling in forum EXCEL HELP
    Replies: 5
    Last Post: May 7th, 2008, 19:50
  3. Save prompt when 'workbook open' macro runs
    By pjc in forum EXCEL HELP
    Replies: 1
    Last Post: July 28th, 2006, 02:39
  4. VBA Open/Save Workbook over network
    By will40 in forum EXCEL HELP
    Replies: 11
    Last Post: June 30th, 2005, 21:38
  5. .bat commands to open / save workbook
    By WurzelGummidge in forum EXCEL HELP
    Replies: 4
    Last Post: May 3rd, 2005, 23:11

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