run .vbs script from excel

[abadilala]

I have macro file and i saved it as .vbs file. Lets called it macro.vbs, and here is the location on my local C:Dekstop\macro.vbs. here is the code inside:

<code>Sub Macro3()
Sheets("vlookup").Select
Range("B5").Select
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Range("B5").Select
ActiveCell.FormulaR1C1 = "Journal classification (name)"
Range("B6").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],LW0640!R2C5:R12163C6,2,TRUE)"
Selection.AutoFill Destination:=Range("B6:B6098")
Range("B6:B6098").Select
end sub

</code>What I want to do is, I want to call that .vbs script from my excel file. So I plan to create the button from developer in excel and call that .vbs file.
Is there a way to do it? please share!

 

[wideboydixon]

The good news is that yes, you can do this although I'm not sure it's the best solution for you. Here's what you need to do:

1. Go to File>Options
2. Click on Trust Center and then Trust Center Settings...
3. Select the Macro Settings tab
4. Check the box that says Trust access to the VBA project object model then click OK and OK again
5. Open up the VBA Editor using Alt+F11
6. Select Insert>Module
7. Go to Tools>References and add a new reference to Microsoft Visual Basic for Applications Extensibility 5.3
8. Paste in the following code:

Public Sub RunExternalMacro()

Dim vbComp As VBIDE.VBComponent
Dim codeMod As VBIDE.CodeModule

Set vbComp = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
vbComp.CodeModule.AddFromFile Environ("UserProfile") & "\Desktop\Macro.vbs"

Application.Run "Macro3"

ActiveWorkbook.VBProject.VBComponents.Remove vbComp

End Sub

Running that macro should then invoke the macro you have stored in your VBS file. However, why you wouldn't just have that as a macro in the current workbook is a bit of a mystery ...

WBD