CODEWITHSUNDEEP
excelvbavbscript
Sivaprakash
Sivaprakash

Reputation: 473

Convert VBA to VBScript

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.

Upvotes: 3

Views: 2478

Answers (1)

Sivaprakash
Sivaprakash

Reputation: 473

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.

Upvotes: 2

Related Questions