Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

help with 2007 excel saveas command


  • Please log in to reply
4 replies to this topic

#1 Dezypher

Dezypher

    Seeker

  • Active Members
  • 17 posts

Posted 25 September 2007 - 06:52 PM

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







#2 kjactive

kjactive

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 697 posts

Posted 26 September 2007 - 01:55 PM

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

#3 Dezypher

Dezypher

    Seeker

  • Active Members
  • 17 posts

Posted 26 September 2007 - 09:01 PM

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.

#4 Locodarwin

Locodarwin

    Origin of Ouijas

  • Active Members
  • PipPipPipPipPipPip
  • 667 posts

Posted 27 September 2007 - 12:50 AM

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
(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...

...specialization is for insects." - R. A. Heinlein


#5 H1T1

H1T1

    Seeker

  • Active Members
  • 6 posts

Posted 21 November 2008 - 03:01 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users