rmock Posted November 28, 2012 Share Posted November 28, 2012 I am just starting out with autoIT and have used minimally so far. I programmatically open an existing Excel spreadsheet which contains 1 worksheet called "NEED". I would like to add a 2nd worksheet called "Documentation" and insert an image stored within a specific directory. I have spent most of the morning searching the forum for good examples to no avail. Could someone provide me with a short example here so that I may have something to compare? Commands like _ExcelSheetAddNew($oExcel, "Documentation") have not worked for me and spur an error: C:\Program Files\AutoIt3\Include\Excel.au3 (957) : ==> The requested action with this object has failed.: $oExcel.ActiveWorkBook.WorkSheets.Add.Activate $oExcel.ActiveWorkBook^ ERROR Thanks in advance! Link to comment Share on other sites More sharing options...
water Posted November 28, 2012 Share Posted November 28, 2012 Welcome to AutoIt and the forum! Can you please post your whole script? How do you open the existing spreadsheet? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
rmock Posted November 29, 2012 Author Share Posted November 29, 2012 Thanks for your reply, water. Here's the structure of my code, excluding the details of the arrays that are being built: expandcollapse popup#include #include #include ; including Excel.au3 $NewFileName = 'test' $Template = @ScriptDir & "\Goals.xlsx" $NewFilePathName = @ScriptDir & "\" & $NewFileName & ".xlsx" $oExcelDoc = ObjGet($Template) $oExcelDoc.activesheet.SaveAs($NewFilePathName) ; here it connects to database and builds SQL in variable $adoSQL1 $adoRs.Open($adoSQL1, $adoCon) ; Run SQL $adoRs.movefirst $totalCount = 0 While Not $adoRs.eof $totalCount = $totalCount + 1 $adoRs.movenext WEnd $adoRs.movefirst $InstanceCount = 0 While Not $adoRs.eof ; here it builds arrays of data to be inserted in 1st worksheet WEnd With $oExcelDoc.activesheet ; here it inserts data into 1st worksheet EndWith $adoRs.close ProgressSet(100, "Done", "Complete") Sleep(50) ProgressOff() ; Here is the questionable code ; First just trying to create the new worksheet and print HI! ;------------------------------------------------------------------- $SheetName = "Documentation" _ExcelSheetAddNew($oExcelDoc, $SheetName) $oExcelDoc.activesheet.Cells(2, 1).value = "HI!" ;------------------------------------------------------------------- $oExcelDoc.Windows(1).Visible = 1 ; I want to make the first worksheet visible when complete $oExcelDoc.Application.Visible = 1 ; Make sure Excel stays open Exit Link to comment Share on other sites More sharing options...
water Posted November 29, 2012 Share Posted November 29, 2012 You should open the Excel Workbook using function _ExcelBookOpen. I'm sure _ExcelSheetAddNew will work then. Because _ExcelSheetAddNew needs the application object as parameter 1 and not the worksheet object which you pass. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
rmock Posted November 29, 2012 Author Share Posted November 29, 2012 This worked great: $oExcelDoc = _ExcelBookOpen($Template) ... instead of using ObjGet(). How would I make the new "Documentation" worksheet the 2nd worksheet (after "NEED")? Link to comment Share on other sites More sharing options...
water Posted November 29, 2012 Share Posted November 29, 2012 Use _ExcelSheetMove to move sheet 2 before sheet 1. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
rmock Posted November 29, 2012 Author Share Posted November 29, 2012 Learning a lot today: _ExcelSheetMove($oExcelDoc, 2) For the image that I was speaking of inserting, do I need to use GDI or is there an easier set of functions that I'm unaware of? Again, I would like to hard-code the directory location of the image instead of pasting. Link to comment Share on other sites More sharing options...
water Posted November 29, 2012 Share Posted November 29, 2012 This function taken from the is what you need:;=============================================================================== ; ; Description: Insert a picture from a separate file into the active sheet. ; Syntax: _ExcelPictureInsert($oExcel, $sFilePath, $iLeft, $iTop, $iWidth, $iHeight, $fLinkToFile = False, $fSaveWithDoc = False) ; Parameter(s): $oExcel - An Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() ; $sFilePath - The full path of the picture to be inserted ; $fLinkToFile - "True" to link the picture to the file from which it was created. ; "False" to make the picture an independent copy of the file. The default value is False. ; $fSaveWithDoc - "True" to save the linked picture with the document. The default value is False. ; $iLeft - The position (in points) of the upper-left corner of the picture relative to the upper-left corner of the worksheet ; $iTop - The position (in points) of the upper-left corner of the picture relative to the top of the worksheet ; $iWidth - The width of the picture, in points ; $iHeight - The height of the picture, in points ; Requirement(s): None ; Return Value(s): On Success - Returns an object representing the inserted picture ; On Failure - Returns 0 and sets @error on errors: ; @error=1 - Specified object does not exist ; Author(s): SEO <locodarwin at yahoo dot com> ; Note(s): None ; ;=============================================================================== Func _ExcelPictureInsert($oExcel, $sFilePath, $iLeft, $iTop, $iWidth, $iHeight, $fLinkToFile = False, $fSaveWithDoc = False) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If NOT FileExists($sFilePath) Then Return SetError(2, 0, 0) If $iLeft < 1 Then $iLeft = 1 If $iTop < 1 Then $iTop = 1 If $iWidth < 1 Then $iWidth = 1 If $iHeight < 1 Then $iHeight = 1 $oExcel.ActiveSheet.Shapes.AddPicture($sFilePath, $fLinkToFile, $fSaveWithDoc, $iLeft, $iTop, $iWidth, $iHeight).Select Return $oExcel.Selection.ShapeRange EndFunc ;==>_ExcelPictureInsert My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
rmock Posted November 29, 2012 Author Share Posted November 29, 2012 water, You have been a blessing so far. The following code gives me the error: C:\Users\me\Desktop\TEST.au3 (538) : ==> The requested action with this object has failed.: $oExcelDoc.ActiveSheet.Shapes.AddPicture($sFilePath, $fLinkToFile, $fSaveWithDoc, $iLeft, $iTop, $iWidth, $iHeight).Select $oExcelDoc.ActiveSheet.Shapes.AddPicture($sFilePath, $fLinkToFile, $fSaveWithDoc, $iLeft, $iTop, $iWidth, $iHeight)^ ERROR Func _ExcelPictureInsert($oExcel, $sFilePath, $iLeft, $iTop, $iWidth, $iHeight, $fLinkToFile = False, $fSaveWithDoc = False) If NOT IsObj($oExcel) Then Return SetError(1, 0, 0) If NOT FileExists($sFilePath) Then Return SetError(2, 0, 0) If $iLeft < 1 Then $iLeft = 1 If $iTop < 1 Then $iTop = 1 If $iWidth < 1 Then $iWidth = 1 If $iHeight < 1 Then $iHeight = 1 $oExcel.ActiveSheet.Shapes.AddPicture($sFilePath, $fLinkToFile, $fSaveWithDoc, $iLeft, $iTop, $iWidth, $iHeight).Select Return $oExcel.Selection.ShapeRange EndFunc ;==>_ExcelPictureInsert $Picture = @ScriptDir & "\mapping.jpg" _ExcelPictureInsert($oExcelDoc,$Picture,126,195,126,195) I also tried changing instances of $oExcel in the function to $oExcelDoc. Link to comment Share on other sites More sharing options...
water Posted November 29, 2012 Share Posted November 29, 2012 Which version of Excel do you run? $oExcel has to be the Excel application object. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
rmock Posted November 30, 2012 Author Share Posted November 30, 2012 Excel version 14.0.6123.5001. I changed all instances in the script of $oExcelDoc to $oExcel but I still get the same error. It doesn't like the ".Select" for some reason. Link to comment Share on other sites More sharing options...
water Posted November 30, 2012 Share Posted November 30, 2012 Which parameters do you pass to _ExcelPictureInsert? Can you post the relevant code line? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
rmock Posted November 30, 2012 Author Share Posted November 30, 2012 Sure - $Picture = @ScriptDir & "\mapping.jpg" _ExcelPictureInsert($oExcel,$Picture,126,195,126,195,False,False) Link to comment Share on other sites More sharing options...
GMK Posted November 30, 2012 Share Posted November 30, 2012 rmock, after replacing $oExcelDoc with $oExcel, are you still getting the following message? C:UsersmeDesktopTEST.au3 (538) : ==> The requested action with this object has failed.: $oExcel.ActiveSheet.Shapes.AddPicture($sFilePath, $fLinkToFile, $fSaveWithDoc, $iLeft, $iTop, $iWidth, $iHeight).Select $oExcel.ActiveSheet.Shapes.AddPicture($sFilePath, $fLinkToFile, $fSaveWithDoc, $iLeft, $iTop, $iWidth, $iHeight)^ ERROR Link to comment Share on other sites More sharing options...
rmock Posted November 30, 2012 Author Share Posted November 30, 2012 Yes, and I've replaced every instance of $oExcelDoc in my script, top to bottom. Link to comment Share on other sites More sharing options...
BrewManNH Posted November 30, 2012 Share Posted November 30, 2012 Have you tried it without the .Select? If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag GudeHow to ask questions the smart way! I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from. Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays. - ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script. - Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label. - _FileGetProperty - Retrieve the properties of a file - SciTE Toolbar - A toolbar demo for use with the SciTE editor - GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI. - Latin Square password generator Link to comment Share on other sites More sharing options...
GMK Posted November 30, 2012 Share Posted November 30, 2012 (edited) I've played with the function in Excel 2010 and I believe AddPicture doesn't like one of the values. I came up with the following that may work in your case (or you may want to edit the function): $Picture = @ScriptDir & "mapping.jpg" $oExcel.ActiveSheet.Pictures.Insert($sPicture).Select With $oExcel.Selection.ShapeRange .IncrementLeft(126) .IncrementTop(95) EndWith Edit: Sorry--forgot EndWith. Edited November 30, 2012 by GMK Link to comment Share on other sites More sharing options...
GMK Posted November 30, 2012 Share Posted November 30, 2012 (edited) OK, for some reason, if I use "True" for the $fSaveWithDoc parameter, it works. I don't know of any other way around it at the moment. Edit: Try this: $Picture = @ScriptDir & "mapping.jpg" _ExcelPictureInsert($oExcel, $Picture, 126, 195, 126, 195, False, True) Edited November 30, 2012 by GMK Link to comment Share on other sites More sharing options...
water Posted December 1, 2012 Share Posted December 1, 2012 Here is the link to the AddPicture method. $fLinkToFile and $fSaveWithDoc are not just True or False but of type MSoTriState. Looks like only MsoFalse (0) and MsoTrue (-1) are supported. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
rmock Posted December 3, 2012 Author Share Posted December 3, 2012 Thanks to all! GMK your solution fixed the last problem and water - thanks for sticking with me. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now