macro will not run from vbscript. help please

[vedran]

I have a vbscript below that opens a file, runs a macro and saves the file.
Everything works fine except the macro part. I usualy get a message that
macro has not been found. However, the macro exists and runs just fine when I
go into excel and run it manualy. I have tried changing the path to the file
in XLSTART folder and it doesn't help. Also I have lowered security to low
but no difference there. Can anyone offer any suggestions please? Thanks.


Dim objExcel
Dim objWorkBook
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open("File 1.xls")
objExcel.DisplayAlerts=False
objExcel.Run "PERSONAL.xls!FORMAT"
objWorkBook.SaveAs "file 2.xls",44
objExcel.DisplayAlerts=True
objWorkBook.Close True
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing

 

[Ardus Petus]

When activated by OLE (CreateObject), Excel does not automatically open
PERSONAL.XLS.

Why do you create a new instance of Excel, since you already have one fully
operational.
You could simply use the instance your code is running in.

'----------------
Sub test()
Dim objWorkBook As Workbook
Set objWorkBook = Workbooks.Open("Ranking.xls")
DisplayAlerts = False
Run "PERSO.xls!FORMAT"
objWorkBook.SaveAs "file 2.xls", 44
DisplayAlerts = True
objWorkBook.Close True
End Sub
'-----------------
HTH

 

[vedran]

Thanks for the explanation! However, your script gives me 'Expected End of
statement' error on line 2.
I have tried adding Set objWorkBook =
objExcel.Workbooks.Open("Personal.xls") to my original script in addition to
opening file 1.xls and it worked. Would this be correct way to do it? Is
objWorkBook.Close True going to close both worksheets?

Thanks for your very fast response.