Sign in to follow this  
Followers 0
rmock

Adding an Excel Worksheet?

21 posts in this topic

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!

Share this post


Link to post
Share on other sites



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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Thanks for your reply, water. Here's the structure of my code, excluding the details of the arrays that are being built:

#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

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

This worked great:

$oExcelDoc = _ExcelBookOpen($Template)

... instead of using ObjGet().

How would I make the new "Documentation" worksheet the 2nd worksheet (after "NEED")?

Share this post


Link to post
Share on other sites

Use _ExcelSheetMove to move sheet 2 before sheet 1.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Which version of Excel do you run?

$oExcel has to be the Excel application object.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Which parameters do you pass to _ExcelPictureInsert?

Can you post the relevant code line?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Yes, and I've replaced every instance of $oExcelDoc in my script, top to bottom.

Share this post


Link to post
Share on other sites

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 Gude
How 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

Share this post


Link to post
Share on other sites

#17 ·  Posted (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 by GMK

Share this post


Link to post
Share on other sites

#18 ·  Posted (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 by GMK

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Thanks to all! GMK your solution fixed the last problem and water - thanks for sticking with me.

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