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
    I'm about to be a PowerPoster! Joacim Andersson's Avatar
    Join Date
    Jan 1999
    Location
    Sweden
    Posts
    14,649

    Re: Calling macro from VB script

    Moved to the VBScript forum.

  3. #3
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    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
  •  



Click Here to Expand Forum to Full Width