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

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
--
AP

"vedran" a écrit dans le message de news:
...
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

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.



"Ardus Petus" wrote:

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
--
AP

"vedran" a écrit dans le message de news:
...
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