Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    May 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Macro (Excel2000 SR3)

    Is there a way to run a macro when a workbook of a specific name is opened eg. Reports.xls?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel2000 SR3)

    Under what circumstances does the macro have to run:

    - when report.xls is opened by couble click (Excel is not open yet)
    - when report.xls is opened using file, open (from excel)
    - other possible way of opening a file

    Where is the macro located:

    - in Report.xls
    - NOT in report.xls
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel2000 SR3)

    In VB Editor, find the ThisWorkbook object in the Project Explorer. Right click on it and select "View Code" from the pop up menu. Select Workbook in the left drop down list at the top of the editor window, and then select Open from the right drop down list (it is probably already selected). You should now have a procedure shell like the one below in the editor window:

    <pre>Private Sub Workbook_Open()

    End Sub
    </pre>


    Any code that you put into that Sub should be run when the workbook is opened.
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    May 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel2000 SR3)

    the report is launched from another system as part of a script that is running and I would not want the macro to be stored in report.xls

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel2000 SR3)

    There is one thing that needs to be tested then.

    Normally, when Excel is opened using automation, it loads without any add-ins. To test whether this is the case:

    - start XL as usual
    - open the VBE (Alt-F11)
    - in the project explorer window, note which projects are listed. If just one (Book1), go to tools, add-ins in Excel and select at least a single add-in.
    - check the project explorer again and note a project should have been added
    - Now close XL
    - let your program open up the report file
    - repeat the above.

    If most of the projects listed in the first attempt are absent, you are out of luck and need some way to automate Excel from the application that generates the file.

    If all projects are there you're in luck.

    - Open the project of personal.xls
    - if there is no personal.xls, record a macro targetting it to your "Personal macro workbook"
    - double click it's Thisworkbook Object
    - paste in this code:

    Private sub Workbook_Open()
    Application.ontime now,"CheckForReport"
    End Sub

    - Now insert a module (Insert, module)
    - Paste in this:

    Option Explicit

    Sub CheckForReport()
    Dim sReportName as string
    Dim wbBook as Workbook
    sReportName="c:dataReport.xls"
    For Each wbBook in Workbooks
    If wbBook.Fullname=sReportName Then
    DoMacro
    End If
    Next

    Sub DoMacro()
    'This is the macro to run in case your report is there!!
    End Sub

    Finally:

    - close Excel
    - click Yes to save changes to your personal macro workbook.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Star Lounger
    Join Date
    May 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel2000 SR3)

    thanks for this - the problem is that it will be a brand new report everytime so I need something that will run each time the workbook is open. The program where the report is loaded from just creates a new report each time called the same thing (ie Report.xls) that overwrites the previous one. I need a report that will run with each new instance of the report.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Macro (Excel2000 SR3)

    OK, then create a new workbook that contains nothing but a macro in the Open Event routing. Have the user open this workbook. Then the macro can open the Report.xls and do whatever needs to be done. I believe that the workbook with the macro can even be hidden so the user never sees it.
    Legare Coleman

Posting Permissions

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