dcsimg
Results 1 to 3 of 3

Thread: Calling macro from VB script

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2007
    Posts
    8

    Calling macro from VB script

    Hi I am trying to call a macro in test.xls using the following VB script

    Option Explicit

    Dim returnVal
    returnVal = 0
    WScript.Echo returnVal

    Dim xlApp, xlBook

    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open("D:\test.xls", 0, True)
    xlApp.Run "macro1"
    xlBook.Close false
    xlApp.Quit

    Set xlBook = Nothing
    Set xlApp = Nothing

    WScript.Echo returnVal
    ''WScript.Quit returnVal
    The macro defined in test.xls is :

    Dim returnVal as Boolean
    sub macro1()
    returnVal = 1
    Exit Sub
    ...
    End Sub
    When I try to run the VB script I get a pop up window with value 0 (that is for the echo I am doing at the start). Then I get a pop up window with value 0 again. Looks like the value from the macro is not being returned.

    Where am I going wrong here.

  2. #2
    Super Moderator Joacim Andersson's Avatar
    Join Date
    Jan 1999
    Location
    Sweden
    Posts
    14,649

    Re: Calling macro from VB script

    Moved to the VBScript forum.
    Joacim Andersson
    If anyone's answer has helped you, please show your appreciation by rating that answer.
    I'd rather run ScriptBrix...
    Joacim's view on stuff.

    MVP

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,382

    Re: Calling macro from VB script

    Looks like you defined it twice:
    Dim returnVal
    Dim returnVal as Boolean

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Tags for this Thread

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width