Results 1 to 9 of 9

Open another workbook and run macro

This is a discussion on Open another workbook and run macro within the Excel Questions forums, part of the Question Forums category; Ok, so I currently have a workbook (Report2011) which records data each week (in a new sheet), and then dependant ...

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    659

    Default Open another workbook and run macro

    Ok, so I currently have a workbook (Report2011) which records data each week (in a new sheet), and then dependant upon the choices made in sheet1, constructs a summary sheet which returns the averages over the time period selected, as well as showing graphs which illustrate the trend over time for each category in the summary.

    What I would now like to do, is to create an almost identical workbook, which corresponds to the following year (Report2012). However I want to add a function where by the user can select a checkbox on sheet1 (We'll call it cbx2011), and when cbx2011 is true, the 2 procedures related to building the graph (to illustrate trend over time) would also be ran in Report2011, however the results would be added to the same graph in Report2012, to enable an easy comparison between the 2 years.

    So the first question I have is, how do I open a closed workbook, and then run a procedure in that workbook?


    Thanks in advance for any help

  2. #2
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,687

    Default Re: Open another workbook and run macro

    Try something like this

    [QUOTE]Sub openBk()
    Workbooks.Open "C:\Address.xls" 'Change address and name
    Application.Run "MacroName" 'Add your macro name here

    End Sub
    [QUOTE]
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2013
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  3. #3
    Board Regular
    Join Date
    Mar 2011
    Posts
    659

    Default Re: Open another workbook and run macro

    Thanks Trevor.

    I tried your way and I get the following error

    Run-time Error '1004':

    Cannot run the macro '!SheetDataChart(6)'. The macro may not be available in this workbook or all macros may be disabled.
    I also tried this code

    Code:
    Sub Open2011()
         'Macro purpose:  To use the application.run method to execute
         'a function or macro (with arguments) from another workbook
     
        Dim PathToFile As String, _
        NameOfFile As String, _
        wbTarget As Workbook, _
        MyResult As Variant, _
        CloseIt As Boolean
     
         'Set file name and location.
        NameOfFile = "Report_2011.xlsm"
        PathToFile = "C:\Users\Me\Desktop"
     
         'Attempt to set the target workbook to a variable.  If an error is
         'generated, then the workbook is not open, so open it
        On Error Resume Next
        Set wbTarget = Workbooks(NameOfFile)
     
        If Err.Number <> 0 Then
             'Open the workbook
            Err.Clear
            Set wbTarget = Workbooks.Open(PathToFile & "\" & NameOfFile)
            CloseIt = True
        End If
     
         'Check and make sure workbook was opened
        If Err.Number = 1004 Then
            MsgBox "Sorry, but the file you specified does not exist!" _
            & vbNewLine & PathToFile & "\" & NameOfFile
            Exit Sub
        End If
     
        On Error GoTo 0
     
         'Run the function.  Update the "FunctionName" to the name of your function
         'and change 1 & 2 to the arguments you need to pass to the function
        MyResult = Application.Run(wbTarget.Name & "!SheetDataChart", 6)
     
         'Give user the results
        MsgBox MyResult
     
        'Application.Run(wbTarget.Name & "!BuildGraph",6)
     
        If CloseIt = True Then
             'If the target workbook was opened by the macro, close it
            wbTarget.Close savechanges:=False
        Else
             'If the target workbook was already open, reactivate this workbook
            ThisWorkbook.Activate
        End If
     
    End Sub
    and get a similar error message

    Run-time error '1004':

    Cannot run the macro 'Report_2011.xlsm!SheetDataChart'. The macro may not be available in this workbook or all macros may be disabled.
    Any ideas? 'SheetDataChart' is definitely the name of the macro, and I'm trying to set the parameter for Y (type Long) as 6

  4. #4
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,687

    Default Re: Open another workbook and run macro

    That looks as though it is a sheet name?

    Have you placed the code behind a worksheet?

    Can you post the macro code for '!SheetDataChart(6)'
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2013
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  5. #5
    Board Regular
    Join Date
    Mar 2011
    Posts
    659

    Default Re: Open another workbook and run macro

    No the code is in a module

    Code:
    Sub SheetDataChart(Y As Long)
     
    Dim ws1         As Worksheet, _
        ws2         As Worksheet, _
        wsm         As Worksheet, _
        p           As String, _
        NumLoops    As Long, _
        WeekSNum    As Long, _
        X           As Long, _
        DT          As Date, _
        DF          As Date, _
        Yearstart   As Date
     
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    Set wsm = Sheets("Master")
     
    ws1.Range("C5") = CDate(Sheets("Sheet1").txtDateFrom.Value)
    ws1.Range("G5") = CDate(Sheets("Sheet1").txtDateTo.Value)
     
    DF = (ws1.Range("C5") + 7) - (Weekday((ws1.Range("C5") + 7), 2))
    DT = (ws1.Range("G5") + 7) - (Weekday((ws1.Range("G5") + 7), 2))
     
    Yearstart = ws1.Range("H23")
     
    With wsm
       .Cells.Clear
    End With
     
    NumLoops = (DT - DF) / 7 + 1
    ''Sets the value for NumLoops based on 2 date values in sheet1
     
        WeekSNum = (DF - Yearstart) / 7 + 1
    ''Sets the value for WeekSNum based on 2 values in sheet1
     
        X = 0
    ''Sets the start value for X
     
    Do Until X = NumLoops
    p = "Week" & WeekSNum + X
     
    On Error Resume Next
     
    With Sheets(p).Range("A1:A200")
    Set rng = .Find(ws2.Range("A" & Y))
    rng1 = rng.Row
    End With
     
    If Sheets(p).Range("S3") = "Total" Then
        Sheets(p).Range("C" & rng1, "Q" & rng1).Copy wsm.Range("C1").Offset(X, 0)
        Sheets(p).Range("S" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
    ElseIf Sheets(p).Range("U3") = "Total" Then
        Sheets(p).Range("C" & rng1, "S" & rng1).Copy wsm.Range("C1").Offset(X, 0)
        Sheets(p).Range("U" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
    ElseIf Sheets(p).Range("W3") = "Total" Then
        Sheets(p).Range("C" & rng1, "U" & rng1).Copy wsm.Range("C1").Offset(X, 0)
        Sheets(p).Range("W" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
    ElseIf Sheets(p).Range("Y3") = "Total" Then
        Sheets(p).Range("C" & rng1, "X" & rng1).Copy wsm.Range("C1").Offset(X, 0)
        Sheets(p).Range("Y" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
    ElseIf Sheets(p).Range("AA3") = "Total" Then
        Sheets(p).Range("C" & rng1, "Z" & rng1).Copy wsm.Range("C1").Offset(X, 0)
        Sheets(p).Range("AA" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
    ElseIf Sheets(p).Range("AC3") = "Total" Then
        Sheets(p).Range("C" & rng1, "AB" & rng1).Copy wsm.Range("C1").Offset(X, 0)
        Sheets(p).Range("AC" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
    ElseIf Sheets(p).Range("AE3") = "Total" Then
        Sheets(p).Range("C" & rng1, "AD" & rng1).Copy wsm.Range("C1").Offset(X, 0)
        Sheets(p).Range("AE" & rng1).Copy wsm.Range("AE1").Offset(X, 0)
    End If
     
    wsm.Range("CZ1").Offset(X, 0) = p
     
    X = X + 1
     
    On Error GoTo 0
     
    Loop
     
    End Sub

  6. #6
    Board Regular Trevor G's Avatar
    Join Date
    Jul 2008
    Location
    Tamworth, Staffordshire
    Posts
    5,687

    Default Re: Open another workbook and run macro

    If the name contains blanks, you need to enclose the file name
    within single quotes. E.g.,

    Application.Run "'Other Workbook Name.xls'!MacroName"
    Trevor
    I am on a learning curve of life, I know a little but like to share what I have learnt with others.
    I am using Microsoft Office 2003 to 2013
    Please remember everyone here is a volunteer, so if you have had a reply to your thread be courteous and acknowledge this.

  7. #7
    Board Regular
    Join Date
    Mar 2011
    Posts
    659

    Default Re: Open another workbook and run macro

    and if there aren't any spaces in the Workbook name?

  8. #8
    Board Regular
    Join Date
    Mar 2011
    Posts
    659

    Default Re: Open another workbook and run macro

    Ok, so I don't know what I changed, but I played around with it a bit and it seems to be working now.

    So I think the next step towards my aim, is adding a parameter which specifies the workbook name into the SheetDataChart(Y As Long) code that I posted earlier.

    And then amending the code so that when ran from the Report_2012, it takes the values from sheet1 in Report_2012 rather than sheet1 in Report_2011.


    I have changed the line
    Code:
    Sub SheetDataChart(Y As Long)
    to
    Code:
    Sub SheetDataChart(Y As Long, wb As Workbook)
    and

    Code:
    Set ws1 = Sheets("Sheet1")
    to

    Code:
    Set ws1 = wb.Sheets("Sheet1")
    Is this the correct way of using the workbook as a parameter?

    Tried adding ,"Report_2012" to the line

    Code:
    MyResult = Application.Run(wbTarget.Name & "!SheetDataChart", 6)
    but am getting a type mismatch error

  9. #9
    Board Regular
    Join Date
    Mar 2011
    Posts
    659

    Default Re: Open another workbook and run macro

    Solved that little problem

    had to change the line to

    Code:
    MyResult = Application.Run(wbTarget.Name & "!SheetDataChart", 6, Workbooks("Report_2012"))
    Now to figure out how to add these values as new series on my chart

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com