Sign in to follow this  
Followers 0

help with 2007 excel saveas command

5 posts in this topic

Posted · Report post

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

Share this post


Link to post
Share on other sites



Posted · Report post

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 :)

Share this post


Link to post
Share on other sites

Posted · Report post

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.

Share this post


Link to post
Share on other sites

Posted · Report post

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

Share this post


Link to post
Share on other sites

Posted · Report post

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.