vbscript to execute macro in excel

[vsantoro]

This is what I have so far for code.
The script goes online, click a button to generate an xls file, the file is in Tmp folder, it grabs the file and saves it to a created directory. then closes excel.
It then opens the saved xls file up. At this point I have a large macro to be run against the xls , is there a way to run the macro from the vbs ?
There is no way to have the macro already inside the xls as the xls is generated online and is a new xls each time.

Code:

Option Explicit

dim v1

v1=msgbox("Would you like to Generate the Overdue RFI data ?",4+32,"RFI Report")
if v1 = vbYes then


Dim objFSO, objFSOText, objFolder, objFile, objexcel2
Dim strDirectory, strFileName, IE, objExcel, filesys, objWorkbook,strFile, WshShell



strDirectory = "C:\RFI_XLS_Temp"
strFile = "\RFI.xls"

'delete the tmp folder & File if Exists
Set filesys = CreateObject("Scripting.FileSystemObject")
If filesys.FolderExists("c:\RFI_XLS_Temp") Then
filesys.DeleteFolder "c:\RFI_XLS_Temp"
End If



'create temp directory and xls file
Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFolder = objFSO.CreateFolder(strDirectory)

Set objFile = objFSO.CreateTextFile(strDirectory & strFile)




'open IE invisible and export data to xls

Set IE = CreateObject("InternetExplorer.Application")

IE.Navigate "

http://reportingportal/Reports/PNET_RFI_ORACLE/"

IE.Visible = True
Wscript.Sleep 5000
IE.Document.All.Item("Button1").Click

Wscript.Sleep 5000




Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
("C:\Tmp\Default.aspx.xls")


'save the tmp xls file
objWorkbook.SaveAs "C:\RFI_XLS_Temp\test.xls"

'close xls
objExcel.Application.Quit

'open the saved xls file
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\RFI_XLS_Temp\test.xls")
Wscript.Sleep 5000





else
wscript.quit
end if


'Close Excel
'objExcel.Application.Quit


WScript.Quit

Edit/Delete Message

 

[jges]

This will not answer your question directly, but I'll throw it out there for consideration. If you create a VBA macro in Excel and save it in your personal workbook, you can run it against any excel file that you open.

 

[mrmovie]

you say you open excel, then close it again, cant see the point in the open and close


Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = true
Set xlBook = objExcel.WorkBooks.Open(,,,youfile)
objExcel.Run(,,,yourmacroname)
'some code to save and close your workbook?
objExcel.quit