none
Need help in calling a macro from the vbscript RRS feed

  • Question

  • HI,

    I am new to vbscripting, we have a requirement to copy an excel file from the shared path to the local file system and to execute a macro into it.

    We are able to write a VBA macro in excel 2003 but we are not able to call this macro through a vbscript.

    Macro to create venn diagram:

    Sub createcircle()
    Set myDocument = Worksheets(1)

    Dim al As Integer
    Dim at As Integer
    Dim aw As Integer
    Dim ah As Integer

    Dim bl As Integer
    Dim bt As Integer
    Dim bw As Integer
    Dim bh As Integer

    Dim cl As Integer
    Dim ct As Integer
    Dim cw As Integer
    Dim ch As Integer


    al = Range("b5").Value
    at = Range("c5").Value
    aw = Range("d5").Value
    ah = Range("e5").Value

    bl = Range("b7").Value
    bt = Range("c7").Value
    bw = Range("d7").Value
    bh = Range("e7").Value

    cl = Range("b9").Value
    ct = Range("c9").Value
    cw = Range("d9").Value
    ch = Range("e9").Value

    With myDocument.Shapes.AddShape(msoShapeOval, _
            al, at, aw, ah)
        .Name = "A Data"
       
        .Fill.ForeColor.RGB = RGB(255, 0, 0)
        .Fill.Transparency = 0.3
        .TextFrame.Characters.Text = al
      
    End With
       
    With myDocument.Shapes.AddShape(msoShapeOval, _
            bl, bt, bw, bh)
        .Name = "B Data"
       
        .Fill.ForeColor.RGB = RGB(0, 255, 0)
        .Fill.Transparency = 0.3
        .TextFrame.Characters.Text = bl
      
    End With

    With myDocument.Shapes.AddShape(msoShapeOval, _
            cl, ct, cw, ch)
        .Name = "C Data"
       
        .Fill.ForeColor.RGB = RGB(0, 0, 255)
        .Fill.Transparency = 0.3
        .TextFrame.Characters.Text = cl
      
    End With

    End Sub

     

    VB script:

     Option Explicit

    'MsgBox "Hello World"

    'Step 1 : Copy the Excel File from Shared Drive to Common Location

    Dim objfso, objfilecopy
    dim strfilepath, strfiledestination

    set objfso = CreateObject("Scripting.FileSystemObject")

    strfilepath = "\\10.14.32.32\bicoe\Transformer_cubes\Scripts\Book1.xls"
    strfiledestination = "D:\SLO\Books.xls"

    set objfilecopy = objfso.GetFile(strfilepath)
    objfilecopy.Copy(strfiledestination)

    'Step 2 : Open the Excel File in the Local machine and Execute the Macros

    dim Excelsheet
    Set Excelsheet = CreateObject("Excel.Application")
    Excelsheet.workbooks.open ("D:\SLO\Books.xls")

    dim mydocument
    Set myDocument = Excelsheet.Worksheets(1)

    Dim al
    Dim at
    Dim aw
    Dim ah

    Dim bl
    Dim bt
    Dim bw
    Dim bh

    Dim cl
    Dim ct
    Dim cw
    Dim ch


    al = myDocument.Cells(2,5).value
    at = myDocument.Cells(3,5).value
    aw = myDocument.Cells(4,5).value
    ah = myDocument.Cells(5,5).value

    bl = myDocument.Cells(2,7).value
    bt = myDocument.Cells(3,7).value
    bw = myDocument.Cells(4,7).value
    bh = myDocument.Cells(5,7).value

    cl = myDocument.Cells(2,9).value
    ct = myDocument.Cells(3,9).value
    cw = myDocument.Cells(4,9).value
    ch = myDocument.Cells(5,9).value


    myDocument.Shapes.AddShape 9,al,at,aw,ah

    'With myDocument.Shapes.AddShape(9,al, at, aw, ah)
     '   .Name = "A Data"
       
      '  .Fill.ForeColor.RGB = RGB(255, 0, 0)
       ' .Fill.Transparency = 0.3
        '.TextFrame.Characters.Text = al
      
    'End With
       
    'With myDocument.Shapes.AddShape(9,bl, bt, bw, bh)
     '   .Name = "B Data"
       
      '  .Fill.ForeColor.RGB = RGB(0, 255, 0)
       ' .Fill.Transparency = 0.3
        '.TextFrame.Characters.Text = bl
      
    'End With

    'With myDocument.Shapes.AddShape(9,cl, ct, cw, ch)
     '   .Name = "C Data"
       
      '  .Fill.ForeColor.RGB = RGB(0, 0, 255)
       ' .Fill.Transparency = 0.3
        '.TextFrame.Characters.Text = cl
      
    'End With

    Excelsheet.Application.Quit
    Set Excelsheet = Nothing

    WScript.Quit


    When we run this command we are not able to generate a shape and we are getting an error related to the Addshape function. Can you please help me on how to call this VBA macro from the vbscript.

    Thursday, March 24, 2011 4:30 PM

All replies

  • Hi Arun,

    Thanks for posting in the MSDN Forum.

    It’s based on my experience we can export the vba project as a bas file from you original Excel file, and imports it in you new Excel file. Then you can call that sub.

    If I have misunderstood anything, please feel free to let me know.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Sunday, March 27, 2011 12:11 PM
    Moderator