4 replies to this topic

[Dezypher]

hello all

i have been trying over and over to figure out what is going on with this, but i cant seem to get it

i have created a simple little script to save and excel document

the excel doc that I am opening is a templete and i want to save it with some date variables.

here is my code:

$oExcel = ObjCreate("Excel.Application")
$oExcel.visible=1
$oExcel.WorkBooks.open("C:\Users\keith.000\Documents\Work Intergration\dailyUtilization.xltx")
$oExcel.ActiveWorkBook.ActiveSheet.Cells(2,1).Value="test"
msgbox(4096, "test", "C:\Users\keith.000\Documents\Work Intergration\dailyUtilization1"&@MON&@MDAY&".xlsx")
$oExcel.ActiveWorkBook.SaveAs ("Filename:=C:\utilization\dailyUtilization1a.xlsm", "FileFormat:=xlOpenXMLWorkbookMacroEnabled", "CreateBackup:=False");, _FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ReadOnlyRecommended:=False, CreateBackup:=False)

when i get this to run i get an error at teh saveas function.

i have tried to use the excelcom_udf but i get the same error there too

the error is:

$oExcel.activeworkbook.saveas("filename:=C:\utilization\dailyutilization1a.xlsm", "fileformant:=xlopensmlworkbookmacroenabled", "createbackup:=false")^ERROR

error: the requested action with this object has failed.

can some one please shed some light here?

thanks

Dezypher

[kjactive]

I don't know if this helps - but I think that you deal with this component wrong as

.ActiveWorkBook.SaveAs("C:\temp\" & .ActiveWorkbook.Name)

as this would actually save a file, I think that the component in Svens nice interface do not like arguments buildin but these can be
treated like sepparate line commands but I do not know that much about the Excel component from ole32.dll

Try to make a pool and experiment a little

With $oExcel
   .ActiveWorkBook.SaveAs("C:\temp\" & .ActiveWorkbook.Name)
Endwith
$oExcel = 0; remember to free the object

kjactive

[Dezypher]

I don't know if this helps - but I think that you deal with this component wrong as

.ActiveWorkBook.SaveAs("C:\temp\" & .ActiveWorkbook.Name)

as this would actually save a file, I think that the component in Svens nice interface do not like arguments buildin but these can be
treated like sepparate line commands but I do not know that much about the Excel component from ole32.dll

Try to make a pool and experiment a little

With $oExcel
   .ActiveWorkBook.SaveAs("C:\temp\" & .ActiveWorkbook.Name)
Endwith
$oExcel = 0; remember to free the object

kjactive

thank you for your reply,

how could i save a file suing a variable date?

again i have tried different things and cannot get execel 2007 to do with with out error.

[Locodarwin]

This should cover you:

$oExcel.ActiveWorkBook.SaveAs("C:\Users\keith.000\Documents\Work Intergration\dailyUtilization1" & @MON & @MDAY & ".xlsx")

Unlike VBA, you cannot directly specify which parameters you want to send to the method with any kind of identifier. Rather, the order in which you specify the parameters (while using the keyword "Default" in place of parameters you don't want to specify) determines which values match with which parameters. It so happens that for the .SaveAs() method, the filename is the first parameter, and is the only required parameter. Thus, my above call should work.

A good way to get a list of all of the parameters of a method call, and thus figure out how to call the method using AutoIt COM, is to look up the method using Google or other search engine. For example, a search for "Excel .SaveAs()" brought up the appropriate MSDN article as the first returned link. In my experience, this has brought me the info I need almost every single time.

Good luck with your Excel endeavors!

-S

[H1T1]

here is an example that works. Creates a new workbook, adss data in sheet1, renames the tab and saves it.

$oExcelapp =ObjCreate("Excel.Application")
$oExcel=$oExcelapp.workbooks.add
$oExcel.sheets(1).cells(1,1).Formula="Sheet Name"
$oExcel.sheets(1).name="sddsd"

FileDelete("c:\test.xlsx")
$oExcel.saveas("c:\test.xlsx")
$oExcel.close (false)
$oExcel=0
$oExcelapp=0