Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Open an Excel File and Run a Macro Q

Options
  • 06-10-2007 4:24pm
    #1
    1916
    Registered Users Posts: 480 ✭✭


    I am not literate in VB but have being looking on the web to try and
    automate a process. What I wish to do is

    1) Open MS Outllook (if it is not already open)
    2) Open an Excel file
    3) Run a Maco within this file)
    4) Save the opened Excel file and close it
    5) Leave MS Outlook & Excel applications opened



    From my look I have found the following and adapted it (saved as *.vbs), but it doesn't fully work. Could anyone assist me in achieving
    1-5 above?

    Thanks


    Dim objXL
    Set objXL = CreateObject("Excel.Application")
    on error resume next
    With objXL
    .Workbooks.Open ("F:\User\My Documents\Reports\Sales.xls")
    .Run "Sales.xls!copy_paste"
    .Quit
    End With
    Set objXL = Nothing


Comments

  • Options
    #2
    Evil Phil
    Registered Users Posts: 7,468 ✭✭✭Evil Phil


    When you say it doesn't fully work what exactly is and is not happening?


  • Options
    #3
    1916
    Registered Users Posts: 480 ✭✭1916


    I took out the line 'on error resume next' and a Windows script host dialog box appears, it details:-

    Line5
    Char5
    Error: The macro .... copy_paste cannot be found
    Code 800A03EC

    The macro is correct as when I go into the Excel file its there (and works). The location is also valid as I've copied from Windows Explorer the path

    Thanks


  • Options
    #4
    Evil Phil
    Registered Users Posts: 7,468 ✭✭✭Evil Phil


    Are you sure the macro is present?


Advertisement