CODEWITHSUNDEEP
excelvba
AzMar
AzMar

Reputation: 71

Running vbs script using cmd

Could someone please tell me why the following VB script works fine if executed from within excel but won't work if it executed using the cmd: cscript c:\vb\test.vbs?. Below is my code I'm trying to get it working using the cmd. I'm using excel .xls (excel 97-2003).

    Private Sub CopyData()
    Dim x
    Dim y

    '## Open both workbooks first:
    Set x = Workbooks.Open("C:\VB\CopyDataTest.xls")

    'Now, copy what you want from x:
    Sheets("Sheet1").Range("A:B").Copy
    Set y = Workbooks.Open("C:\VB\Destination.xls")

    'Now, paste to y worksheet:
    y.Sheets("Sheet2").Range("A:B").PasteSpecial

    'Close x:
      y.Close
End Sub

Upvotes: 2

Views: 174

Answers (2)

Mnimonic
Mnimonic

Reputation: 46

If you need to run the script from cmd, you need to create an excel object. Try this:

Private Sub CopyData()
    Dim x
    Dim y

    Set xlApp = CreateObject("Excel.Application") 
    Set xlBook = xlApp.Workbooks.Open("C:\VB\Destination.xls", 0, True) 

    ## Open both workbooks first:
    Set x = xlApp.Workbooks.Open("C:\VB\Destination.xls")

    Now, copy what you want from x:
    xlApp.Sheets("Sheet1").Range("A:B").Copy
    Set y = xlApp.Workbooks.Open("C:\VB\Destination.xls")

    Now, paste to y worksheet:
    y.Sheets("Sheet2").Range("A:B").PasteSpecial

    Close x:
    y.Close
End Sub

CopyData()

Upvotes: 2

Schwarzie2478
Schwarzie2478

Reputation: 2276

Mnimonic already gave an perfectly usable workaround for this, but an explanation could be usefull too.

You have written a piece of code in VBA (Visual Basic for Applications).

You tried to run it as VBS (VB Script)

VB script doesn't know about Office and other libraries already loaded when running the code inside excel.

You'll need to learn how to interact with the COM interfaces from office in VBscript.

Better solution now would be to program in VB.NET and interact with excel inside .NET:

Link: VB.NET and excell

The code will still look very familiar, but it's what Microsoft would like you to do now.

Attention: You will always need to have Excel installed on the PC running the script! If you want to avoid that, maybe look at something like Aspose to do things without Office installed...

Upvotes: 2

Related Questions