Running vbs script using cmd

Question

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

Ok - maybe I'm wrong but the file name is "test.vbs" and I need to execute it using the cmd? by typing cscript c:\vb\test.vbs. — AzMar
– AzMar, Commented Jul 25, 2016 at 11:10
Because this is not VBS, obviously cscript can't execute it. The extension is only for Windows to display the icon and choose which app to open the file. Programs don't use extensions to know filetype. This is VBA so running from Excel will work — phuclv
– phuclv, Commented Jul 25, 2016 at 11:15
Ok - thanks for this but I've seen this happening where .txt files are saved as .vbs then executed using the cmd?. My intention of this is that I eventually would like to execute this script using selenium webdriver. — AzMar
– AzMar, Commented Jul 25, 2016 at 11:33

Nander Speerstra · Accepted Answer · 2016-07-25 12:14:49Z

2

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()

edited Jul 25, 2016 at 12:14

Nander Speerstra

1,5366 gold badges26 silver badges30 bronze badges

answered Jul 25, 2016 at 11:29

Mnimonic

465 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AzMar Over a year ago

thanks the code is doing what I wanted but the only thing is that once the date copied to the destination workbook successfully, the source workbook (i.e. X) is getting locked for some reason if I try to open it. Any idea how to avoid the source workbook from getting locked?. Thanks.

Schwarzie2478 Over a year ago

@Mnimonic, The code above works fine but I need to copy more columns ( A, B, C, D, E, F, I) to destination sheet . If I try to copy 3 columns or more im getting the error: 800A03EC ( copied from comment on my answer...)

Schwarzie2478 · Accepted Answer · 2016-07-25 12:12:42Z

2

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...

edited Jul 25, 2016 at 12:12

answered Jul 25, 2016 at 11:57

Schwarzie2478

2,28628 silver badges34 bronze badges

1 Comment

AzMar Over a year ago

@Mnimonic, The code above works fine but I need to copy more columns ( A, B, C, D, E, F, I) to destination sheet . If I try to copy 3 columns or more im getting the error: 800A03EC

Collectives™ on Stack Overflow

Running vbs script using cmd

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related