Update the Excel UDF that comes with AutoIt

[water]

I've added an >Example Scripts thread for this UDF.

It shows what can be done with Excel and the UDF.

[coffeeturtle]

Thank you water for these updates!

Coincidentally, I was working on manipulating an Excel sheet for work and was looking for autoit automation. I noticed 2013 was giving me some issues. So this is really great news!

Do you think it is possible to somehow bring back the ability to have Excel call up Outlook through a macro? 2013 broke my 2010 code. :-(

With your new Excel script, though, maybe I can come up with a workaround of sorts.

Thank you.

[water]

How do you let Excel call up Outlook in Office 2010?

[coffeeturtle]

Using VBA....here's a snippit of my code:

 Dim OutApp As Object
            Dim OutMail As Object
            Dim strbody As String

            Set OutApp = CreateObject("Outlook.Application")
            Set OutMail = OutApp.CreateItem(0)
            If OutApp Is Nothing Then
                varOutlookCheck = MsgBox("Sorry, Couldn't find or start Microsoft Outlook 2007-2010.", vbOKOnly + vbCritical, "Please verify that Outlook is installed and configured.")
                
            Else
            
            strNote = "* " & InputBox(Prompt:="You may include an additional note or comment below:", Title:="Additional Note") & "<br><br>"
            If strNote = "* <br><br>" Then strNote = ""
            
            strbody = "<FONT COLOR=#ff0000>" & strNote & "</Font>" & _
                        "<H3>Hi,</H3>" & _
                        "Hope all is well." & "<br><br>" & _
                        "Attached, please find my " & "<I>" & Range("A1") & "</I>." & "<br><br>" & _
                        "Please reply to this email to confirm receipt." & "<br><br>" & _
                        "Thank you," & "<br><br>" & Sheets("Summary").Range("O3") & _
                        "<br><br><br><br><br><br><br><br><br><br><br><br>" & _
                        "~Submitted in my behalf through the Reporter."
                        'Signature Name is Range("O3")
                        
            On Error Resume Next
            With OutMail
                .To = Sheets("Summary").Range("O1") 'Secretary's email address
                .CC = Sheets("Summary").Range("O2") 'Assistant
                .BCC = ""
                .Subject = Range("A1") & " - " & Sheets("Summary").Range("O3")
                .Importance = 2 '0 = Low; 1 = Normal (or just remove this line); 2 = High
                .ReadReceiptRequested = True
                .HTMLBody = "<br>" & strbody
                '.Attachments.Add ("C:samplefile")
                .Display 'Need to briefly display the message in order to paste the picture
                .GetInspector.CommandBars("Edit").Controls("Paste").Execute
                .Send

            End With
            On Error GoTo 0

            Set OutMail = Nothing
            Set OutApp = Nothing

[coffeeturtle]

I can use Autoit to use Send commands and do a Send as Attachment, but I don't want to send the entire workbook. Just a snippit of a few rows/columns.

Basically, the GetInspector.CommandBars functions have been killed in 2013.

[water]

Using VBA....here's a snippit of my code:

 Dim OutApp As Object

            Dim OutMail As Object

            Dim strbody As String

...

Should be possible by combining the Outlook UDF and the Excel UDF.

They operate on different objects so i see no problem to pack them into a single script.

[coffeeturtle]

I'll have to look into that. Thank you for the tip!

[water]

If you like I can produce a small example tomorrow or on Sunday.

[FlashpointBlack]

No, you didn't miss this functions. This UDF has no formatting functions at the moment.

As most of the Excel formatting methods would lead to an AutoIt function with a single line I haven't implemented any of them.

If anyone can show me a sensible way to implement formatting functions I will be happy do add them.

 

Thanks for the reply! I've done a small bit of digging but I can't seem to find any example code of any excel formatting lines. the best I can find is from the included excel UDF to ghetto my way through text formatting functions, but nothing for column width/row height. Could I ask for your help once more in pointing me in the right direction to find this information?

[water]

To set row height/column width use something like this:

$oExcel.ActiveSheet.Range("2:10").Height = $oExcel.CentimetersToPoints(1.5) ; Height (1.5 cm) for rows 2 to 10
$oExcel.ActiveSheet.Range("A:D").Width = $oExcel.InchesToPoints(2) ; Width in (2 inches) for columns A:D

[FlashpointBlack]

Thank you very much Water... again!

[water]

[water]

Do you think it would be a good idea to create a section in the Wiki for such "simple" tips and tricks?

[FlashpointBlack]

I'd say so, but that's just me being a fiddler. I think it would certainly help. I'm not quite up to the stage of being able to effortlessly work with objects and the integration with autoit when compared to what I find when googling isn't always... "drag and drop" easy.

Another thought:

Perhaps some of the simpler calls (like column width/row height/etc...) could be put into function much like the _IEPropertySet function in the IE library. It could be something fairly simple with a select statement that would take care of a large array of small functions and not clutter up the UDF with loads of one-line functions. Just a thought from someone without much foresight

back to topic though, yes, I would at least benefit from those being added to the wiki!

Thanks!

Edit: Just a side note, I used the code you provided above and I couldn't get it to work. I did a bunch of googling and I finally figured it out. Here is the code that I used that worked for me, since from what I saw, .width is read-only:

$oExcel.ActiveSheet.Range("J:J").ColumnWidth = 30

when using this code though, the number supplied is not in pt format, but something else. I found that a ratio of 1/7 works pretty well. The above code will change the column width to about 210 pt.

Edited November 19, 2013 by FlashpointBlack

[FlashpointBlack]

Just thought I'd drop a line to show something that I made for myself, since it isn't anywhere near up to par compared to the rest of the UDF. There's no error checking yet, just showing a concept. 

expandcollapse popup

#include <Excel Rewrite.au3>
#include <ExcelConstants.au3>

Global $FillArray[10][5] ;create an array to populate the spreadsheet
for $i = 0 to 4
    for $o = 0 to 4
        $FillArray[$i][$o] = Random(10000,99999,1)
    Next
next

$oExcel = _Excel_Open(true)
$oWorkBook = _Excel_BookNew($oExcel, 1)
$ArrSheetList = _Excel_SheetList($oWorkbook)
$oWorkSheet = $ArrSheetList[0][1]
_Excel_RangeWrite($oWorkbook, 1, $FillArray,"A1",TRUE,TRUE)

$CellArea = "A1:E5"

_Excel_FormatCell($oWorksheet, $CellArea, "ColumnWidth", 100)
_Excel_FormatCell($oWorksheet, $CellArea, "RowHeight", 50)
_Excel_FormatCell($oWorksheet, "A:A", "ColumnWidth", 150)
_Excel_FormatCell($oWorksheet, "1:1", "RowHeight", 70)
_Excel_FormatCell($oWorksheet, "1:1", "FontName", "WingDings")
_Excel_FormatCell($oWorksheet, "2:2", "FontSize", 15)
_Excel_FormatCell($oWorksheet, "2:2", "Bold", True)
_Excel_FormatCell($oWorksheet, "3:3", "Italic", True)
_Excel_FormatCell($oWorksheet, "3:3", "Align", $xlLeft)
_Excel_FormatCell($oWorksheet, "3:3", "VAlign", $xlTop)
_Excel_FormatCell($oWorksheet, "4:4", "Underline", True)
_Excel_FormatCell($oWorksheet, "4:4", "Align", $xlCenter)
_Excel_FormatCell($oWorksheet, "4:4", "VAlign", $xlCenter)
_Excel_FormatCell($oWorksheet, "5:5", "Bold", True)
_Excel_FormatCell($oWorksheet, "5:5", "Italic", True)
_Excel_FormatCell($oWorksheet, "5:5", "Underline", True)
_Excel_FormatCell($oWorksheet, "5:5", "Align", $xlRight)
_Excel_FormatCell($oWorksheet, "5:5", "VAlign", $xlBottom)

$ReportString = $CellArea & " - Set width to 100 and height to 50" & @CRLF & @CRLF
$ReportString &= "Column A width: 150" & @CRLF & @CRLF
$ReportString &= "Row 1 Height: 70" & @CRLF
$ReportString &= "Row 1 Font Face: WingDings" & @CRLF & @CRLF
$ReportString &= "Row 2 Font Size: 15" & @CRLF
$ReportString &= "Row 2 Bold: True" & @CRLF & @CRLF
$ReportString &= "Row 3 Italic: True" & @CRLF
$ReportString &= "Row 3 HAlignment: Left" & @CRLF
$ReportString &= "Row 3 VAlignment: Top" & @CRLF & @CRLF
$ReportString &= "Row 4 Underline: True" & @CRLF
$ReportString &= "Row 4 HAlignment: Center" & @CRLF
$ReportString &= "Row 4 VAlignment: Center" & @CRLF & @CRLF
$ReportString &= "Row 5 Bold, Italic, Underlined" & @CRLF
$ReportString &= "Row 5 HAlignment: Right" & @CRLF
$ReportString &= "Row 5 VAlignment: Bottom" & @CRLF & @CRLF
$ReportString &= "Click OK to close Excel."

msgbox(0,"Sheet Report",$ReportString)
_Excel_BookClose($oWorkBook, false)
_Excel_Close($oExcel)

Func _Excel_FormatCell($oWorkSheet, $Range, $Operation, $Value)
    select
        case $Operation = "ColumnWidth"
            $CalculatedValue = ($Value - 5) / 7
            $oWorkSheet.Range($Range).ColumnWidth = $CalculatedValue
        case $Operation = "RowHeight"
            $CalculatedValue = $Value / 1.33
            $oWorkSheet.Range($Range).RowHeight = $CalculatedValue

        case $Operation = "Bold"
            $oWorkSheet.Range($Range).Font.Bold = $Value
        case $Operation = "Italic"
            $oWorkSheet.Range($Range).Font.Italic = $Value
        case $Operation = "Underline"
            $oWorkSheet.Range($Range).Font.Underline = $Value
        case $Operation = "FontName"
            $oWorkSheet.Range($Range).Font.Name = $Value
        case $Operation = "FontSize"
            $oWorkSheet.Range($Range).Font.Size = $Value

        case $Operation = "Align"
            $oWorkSheet.Range($Range).HorizontalAlignment = $Value
        case $Operation = "VAlign"
            $oWorkSheet.Range($Range).VerticalAlignment = $Value
    EndSelect
EndFunc

I've also added some constants to ExcelConstants.au3 to add support for vertical alignment:

Global Const $xlTop = -4160 ; top
Global Const $xlBottom = -4107 ; bottom

I'm sure that it's more work than what it's worth, but it's helpful to me to keep everything in the same format (ie: calling a function to manipulate the sheet rather than manipulating it directly) This also helps make it simpler for me to get around the column width and row height problem I was having with them not being accurate. $CalculatedValue gets me an exact value, though I'm sure there's an easier way to do it that I just don't know about.

Edited November 19, 2013 by FlashpointBlack

[water]

RowHeight has to be specified in points. Excel offers two functions to "translate" centimeters and inches to points.

$oExcel.CentimetersToPoints($icm)
$oExcel.InchesToPoints($iInch)

As your example shows you need most of the code to process the parameters. The Excel function itself is a simple one liner.

So I think I will stick with the Wiki idea for the time being.

[FlashpointBlack]

I understand. It's easier for me to use that long bit of scrap code instead of the alternative since looking at the function gives me everything I need to process the request without knowing anything else about how to manipulate excel directly through objects. An All-In-One solution.

One thing I'm confused about though:

RowHeight has to be specified in points. Excel offers two functions to "translate" centimeters and inches to points.

 

I saw from your previous examples that you used the excel functions for translations, but those did not work for me. When trying to specify the row height directly:

$oExcel.ActiveSheet.Range("1:1").RowHeight= 100

The row height in excel is set to 133 pt (note pt, not another measurement)

and the same problem when setting the column width:

$oExcel.ActiveSheet.Range("A:A").ColumnWidth = 100

yields a column width of 705 pt (again, note pt), which was the reason for the extra math there on both height and width. I'd love to know if there's a way to do this properly, or if I'm the only one experiencing this issue.

Thanks!

[JLogan3o13]

FlashpointBlack, for what it is worth, this works fine for me. Have you tried something like what I have, on a simple test spreadsheet, just to rule out your main worksheet as the problem?

#include <Excel.au3>

$oExcel = _ExcelBookOpen(@DesktopDir & "\Test.xls")
    $oExcel.ActiveSheet.Range("1:1").RowHeight = 100
    $oExcel.ActiveSheet.Range("A:A").ColumnWidth = 100

[water]

Forget the bulls..t I've written above

That's how I understand it now.

According to MSDN:

Property "ColumnWidth": One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.

Property "Width": The width of a column in points

Property "RowHeight": Returns the height of all the rows in the range specified, measured in points

Property "Height": Returns or sets a Variant value that represents the height, in points, of the range.

So I understand the difference between ColumnWidth and Width. But not for RowHeight and Height.

Need to further investigate.

Edited November 19, 2013 by water

[FlashpointBlack]

 

FlashpointBlack, for what it is worth, this works fine for me. Have you tried something like what I have, on a simple test spreadsheet, just to rule out your main worksheet as the problem?

#include <Excel.au3>

$oExcel = _ExcelBookOpen(@DesktopDir & "\Test.xls")
    $oExcel.ActiveSheet.Range("1:1").RowHeight = 100
    $oExcel.ActiveSheet.Range("A:A").ColumnWidth = 100

 

I did test it, but I've just double tested it by copy-pasting your code and the numbers I gave earlier are the same. it makes sense for what water posted after you. From what I've read, Width and Height properties are read-only, otherwise using the method that deals only with point would be the way to go.

When you check in excel, your row height and column width are both 100 pt, making a perfect square in cell A1? I'm curious what version of excel you're running. I'm working with 2007 over here. Try to add in the following line to your own code since Height and Width return pt values:

msgbox(0,"Cell Sizes","Row Height: " & $oExcel.ActiveSheet.Range("1:1").Height & @CRLF & "Column Width: " & $oExcel.ActiveSheet.Range("A:A").Width)