Adding an Excel Worksheet?

[rmock]

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!

[water]

Welcome to AutoIt and the forum!

Can you please post your whole script? How do you open the existing spreadsheet?

[rmock]

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

[water]

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.

[rmock]

This worked great:

$oExcelDoc = _ExcelBookOpen($Template)

... instead of using ObjGet().

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

[water]

Use _ExcelSheetMove to move sheet 2 before sheet 1.

[rmock]

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.

[water]

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

[rmock]

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.

[water]

Which version of Excel do you run?

$oExcel has to be the Excel application object.

[rmock]

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.

[water]

Which parameters do you pass to _ExcelPictureInsert?

Can you post the relevant code line?

[rmock]

Sure -

$Picture = @ScriptDir & "\mapping.jpg"
_ExcelPictureInsert($oExcel,$Picture,126,195,126,195,False,False)

[GMK]

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

[rmock]

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

[BrewManNH]

Have you tried it without the .Select?

[GMK]

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

[GMK]

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

[water]

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.

[rmock]

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