Convert VBA to VBScript

Question

I'm copying data form one workbook to another workbook and then run a macro from the copied workbook. The below VBA code works fine.

VBA Code

Sub test()

    Dim x As Workbook
    Dim y As Workbook

    Set x = Workbooks.Open("D:\mywork\work_data.xlsx")
    Set y = Workbooks.Open("D:\mywork\other_work_data.xlsm")

    x.Sheets("myworksheet").UsedRange.Copy

    y.Sheets("Sheet1").Range("A1").PasteSpecial

    y.Save
    x.Close

    Application.Run ("other_work_data.xlsm!checkDate")

    y.Close

End Sub

Now, I'm trying to change the following vba code to vbscript, but it doesn't work for me.

VBScript code

Sub test()

    Dim ExcelApp
    Dim x
    Dim y

    Set ExcelApp = CreateObject("Excel.Application")
    Set x = ExcelApp.Workbooks.Open("D:\mywork\work_data.xlsx")
    Set y = ExcelApp.Workbooks.Open("D:\mywork\other_work_data.xlsm")

    x.Sheets("myworksheet").UsedRange.Copy

    y.Sheets("Sheet1").Range("A1").PasteSpecial

    y.Save
    x.Close

    ExcelApp.Application.Run ("other_work_data.xlsm!checkDate")

    y.Close

    ExcelApp.DisplayAlerts = False
    ExcelApp.Application.Quit
    Set ExcelApp = Nothing

End Sub

Since I'm new to vba and vbscript, can anyone tell me what I'm doing wrong? I had gone through similar questions in stack overflow, but I didn't sort out the issue.

How does it not work? How far does it get? what error occurs? (Try passing .run a full path) — Alex K., Feb 26 '16 at 11:04
@AlexK. I didn't get any error. Also tried with full path in .run. Data didn't get copy from one book to another book. — Sivaprakash, Feb 26 '16 at 11:14
Do you need the additional `Application` statements after the `ExcelApp` references? ExcelApp is already set as an application. Try removing the `Application` from `ExcelApp.Application.Run ("other_work_data.xlsm!checkDate")` and `ExcelApp.Application.Quit` — Jordan, Feb 26 '16 at 11:19
Are you sure you specified the correct sheets? Does this work: `x.Sheets("myworksheet").UsedRange.Copy y.Sheets("Sheet1").Range("A1")` I assume not saving after running the macro is deliberate? — Rory, Feb 26 '16 at 11:23
@JordanBiddlecombe Tried removing `Application` from `.Run` and `.Quit` but again remains the same. — Sivaprakash, Feb 26 '16 at 11:30
before `.copy` you could add `ExcelApp.visible = true` to see if Excel is opening. You could add some `MsgBox "stop to check"` to stop action and check if everything is going ok at that stage of the macro. Or temporary `Exit sub` somewhere in the middle to test temp process results. — Kazimierz Jawor, Feb 26 '16 at 11:55
"Doesn't work" is an insufficient problem description. *What* "doesn't work"? How *exactly* does it "not work"? Is your application not opening? Are changes not saved? What result do you expect, and how is the result you're actually getting different from that? As KazimierzJawor mentioned, running the application visible should be your first step when debugging. Also, don't set `DisplayAlerts` to `False` while troubleshooting, as that might hide the issue. — Ansgar Wiechers, Feb 26 '16 at 12:42
remove the parenthesis from the argument in `.Run`. `other_work_data.xlsm!` is also unnecessary, I believe. — Scott Holtzman, Feb 26 '16 at 13:34
@ScottHoltzman It's unnecessary, but is unlikely to cause problems here, because in this context it's interpreted as "pass the argument (the string) by value". — Ansgar Wiechers, Feb 26 '16 at 14:04
Thanks for your time everyone. Sorted out the problem. Done a small mistake. I didn't call the `subroutine test()`. @AnsgarWiechers Thanks for your suggestions. — Sivaprakash, Feb 26 '16 at 14:33

score 2 · Accepted Answer · answered Feb 26 '16 at 14:30

I had sorted out the problem. Actually, the problem is very simple. I used the subroutine test() and I forgot to call. So, I had used Call test().

Sub test()

Dim ExcelApp
Dim x
Dim y

Set ExcelApp = CreateObject("Excel.Application")
Set x = ExcelApp.Workbooks.Open("D:\mywork\work_data.xlsx")
Set y = ExcelApp.Workbooks.Open("D:\mywork\other_work_data.xlsm")

x.Sheets("myworksheet").UsedRange.Copy

y.Sheets("Sheet1").Range("A1").PasteSpecial

y.Save
x.Close

ExcelApp.Run ("other_work_data.xlsm!checkDate")    'ExcelApp.Run is enough

y.Close

ExcelApp.DisplayAlerts = False
ExcelApp.Quit                                      'ExcelApp.Quit is enough
Set ExcelApp = Nothing

End Sub

Call test()

Otherwise, it also works without writing the code inside subroutine

Dim ExcelApp
Dim x
Dim y

Set ExcelApp = CreateObject("Excel.Application")
Set x = ExcelApp.Workbooks.Open("D:\mywork\work_data.xlsx")
Set y = ExcelApp.Workbooks.Open("D:\mywork\other_work_data.xlsm")

x.Sheets("myworksheet").UsedRange.Copy

y.Sheets("Sheet1").Range("A1").PasteSpecial

y.Save
x.Close

ExcelApp.Run ("other_work_data.xlsm!checkDate")    'ExcelApp.Run is enough

y.Close

ExcelApp.DisplayAlerts = False
ExcelApp.Quit                                      'ExcelApp.Quit is enough
Set ExcelApp = Nothing

Thanks for everyone helped me through comments. Thanks for your time.

Convert VBA to VBScript

1 Answers1