CODEWITHSUNDEEP
vbaexcel
Zahra AK
Zahra AK

Reputation: 53

how to save macro code as vbs?

I don't know why I can't call my macro code from R, This is my code that I'm trying to save as vbs file: (should I save it in Notepad application?)

 Sub vb()
       Dim xlApp
       Dim xlBook
       Set xlApp = CreateObject("Excel.Application")
       Set xlBook = xlApp.Workbooks.Open("path.xlsm", 0, False)
       xlApp.Visible = True
       xlApp.Run "Countries"
       xlApp.Quit
       Set xlBook = Nothing
       Set xlApp = Nothing
 End Sub

How can I save the above code as vbs ?

Upvotes: 5

Views: 4060

Answers (2)

nishit dey
nishit dey

Reputation: 458

Use the below VBS code as you cannot save VBA code as VBS as the schema or say they are not in the same page as C and C++ are different it is in the same way

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("path.xlsm")

objExcel.Application.Visible = True

objExcel.Application.Run "path.xlsm!Countries" 'Refer to the below if the code is under sheet
objExcel.ActiveWorkbook.Close
WScript.Echo "Finished."
WScript.Quit

If you have placed the code in the sheet. user this line

objExcel.Application.Run "path.xlsm!sheet1.dog"

Hope this resolve your query. Happy Coding.

Upvotes: 0

A.S.H
A.S.H

Reputation: 29342

You can export a code module from the project explorer, right-click on it and select Export File....

You can also do it with VBA, i.e. to export "Module1":

With ThisWorkbook.VBProject.VBComponents("Module1")
    .Export "c:\so\" & .Name & ".bas"
End With

Upvotes: 2

Related Questions