Announcement

Collapse
No announcement yet.

Open Workbook, Run Macro, Save - Automate

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

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

  • #2
    Re: Open Workbook, Run Macro, Save - Automate

    Is this the sort of thing you had in mind?
    Code:
    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
    . . .

    Comment


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

      Comment


      • #4
        Re: Open Workbook, Run Macro, Save - Automate

        Good show, Frankie. Glad to help.
        Sid

        Comment

        Working...
        X