Stand alone VBA script files
This is how you import a standalone exported VBA Module. This imports a
module named WriteData.bas and runs a macro named WriteAsText in that
module :
Set objwb = ThisWorkbook
Set oVBC = objwb.VBProject.VBComponents
Set CM = oVBC.Import(GetPath() & "\WriteData.bas")
objwb.Application.Run "WriteAsText"
This clip shows how you add VBA code from a String using CM defined
above :
CM.CodeModule.AddFromString "Public Const TEXT_FILE = chr(34) &
"C:\file.txt" & r(34) & chr(10)
Yes, one can execute the code above from VBScript.
In the most general case, if you have a worksheet that uses only VBA
you can convert your Worksheet into a Worksheet created by a VBScript.
This VBScript code shows how you create your Excel, Workbook, and
access Worksheets:
Dim objXL,objwb,objws 'Put this at top of VBScript file for global
scope
Set objXL = CreateObject("Excel.Application")
Set objwb = objXL.Workbooks.Add
Set objws = objwb.Worksheets("Sheet1")
Sub ExcelSetUp()
objws.Name = "ActiveDirectory"
objws.Activate
objXL.Visible = True
data = Array ( "employeeID", "sAMAccountName")
objws.Range(objws.Cells(1,1),objws.Cells(1,2)).Val ue = data
End Sub
tishoo wrote:
Question: Is it possible to have a standalone VBA script file that Excel can
open, or does the VBA have to be written into an Excel document? If so how
is it done?
Ta
T
|