ExcelCOM UDF

[randallc]

Hi,

No, I have checked 111 OK too.

What about Excelcom version [which are you using?]?

The zip file is up to 1.82 [in SQLAU zip at that link] ; the main site is still on 1.67

Perhaps I need to update the main site?

Randall

Edited March 6, 2006 by randallc

[blitzkrg]

Hi,

No, I have checked 111 OK too.

What about Excelcom version [which are you using?]?

The zip file is up to 1.82 [in SQLAU zip at that link] ; the main site is still on 1.67

Perhaps I need to update the main site?

Randall

I confirmed there is something wrong with my spreadsheet.

for some reason every time it jumps to the last tab, i made a copy and deleted that tab, but it still jumps to the last tab..

so your code is good and works fine on a newly created test spreadsheet

[blitzkrg]

@randallc

I have another feature request for ya.

Is it possible to set the cell color? (and while your at it, cell text font color)

[Simucal]

Yea, I think that would be great. Possibly being able to set column width and things like bold, italics, etc.

In anycase.. THANK you randall for making this all possible.

[seandisanti]

hey randall, want a hand coding some of the requests? i haven't actually used your UDF, but i use COM to control excel from autoit all the time, and i've been writing for excel for a very long time... it looks like you know what you're doing, just offering because i know how hard it can be to keep up with requests sometimes. (i do it all day at work)

[Simucal]

I've actually written SetColumnWidth, and Font, cell color functions.. I'll get them to randall.

[blitzkrg]

I've actually written SetColumnWidth, and Font, cell color functions.. I'll get them to randall.

Simucal - can you post your functions here? I'm not sure if randallc is still working on this and i could use the color functions

thanks

[Simucal]

Per request I am posting a few additional functions I have made to ExcelCOM in regards to cell formating.

If RandallC wants me to remove these I will.

Regards,

-Simucal

_XLSetColumnWidth(ByRef $s_FilePath, $s_i_Sheet, $s_i_Column, $s_i_ColumnWidth, $s_i_Visible)

Notes: $s_i_ColumnWidth can either be a number or "Autofit". For example: _XLSetColumnWidth($FilePath, 1, "A:J", "Autofit", 0)

_XLShowColorCodes()

Notes: Dont know the numbers for the color codes to set cell background color and font color? Simply call this function and it will lay them all out in an excel spreadsheet

_XLSetCellFontName(ByRef $s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, $s_FontName, $s_i_Visible)

Notes: Set a cells font, ie _XLSetCellFontName($FilePath,1,"C",10,"Wingdings",0)

_XLSetCellFontColor(ByRef $s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, $i_FontColor, $s_i_Visible)

Notes: _XLSetCellFontColor($FilePath,1,"C",10,5,0) Note. '5' is the color code

_XLSetCellColor(ByRef $s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, $i_FontColor, $s_i_Visible)

Notes: Sets a cell's background color, use in the same way as _XLSetCellFontColor

ExcelCom.au3

[randallc]

Hi,

I prefer the option of the $s_i_Column holding a range;

Func _XLSetColumnWidth(ByRef $s_FilePath, $s_i_Sheet, $s_i_Column, $s_i_ColumnWidth, $s_i_Visible)

$s_i_Column2=$s_i_Column

for $i=0 to 9

$s_i_Column2=StringReplace($s_i_Column2,String($i),"")

Next

if $s_i_Column2="" then $s_i_Column2=$s_i_Column

$var = _ExcelCOM($s_FilePath, $s_i_Sheet, $s_i_Column2, 1, "SetColWidth", "NoSave", $s_i_ColumnWidth, $s_i_Visible, "NOTExit", "NotLastRow", "NOTToColumn")

EndFunc ;==>_XLSetColumnWidth

I suggest a chnge to width mini-func to change a range to a column range;

then use ranges inside the main func.

.Application.Cells ($i_Row, $s_i_Column)

to

.activesheet.range ($Range)

If you are happy with that and confirm it works for you, i'll post the change on post 1 in the thread.

Thanks, , Randall

Example to test; attached

_XLSimuCal.au3

Edited April 30, 2006 by randallc

[Simucal]

Works great, I agree with what you said about the changes.

There are a few other's I was thinking about adding as well, I'll post them when I have them.

Thanks randall,

-Sim

[Dickb]

Hi,

I prefer the option of the $s_i_Column holding a range;

I suggest a chnge to width mini-func to change a range to a column range;

then use ranges inside the main func.

If you are happy with that and confirm it works for you, i'll post the change on post 1 in the thread.

Thanks, , Randall

Example to test; attached

@randallc

If have changed the UDF yesterday to make it so you have a choice. If the row is less than 1 then the UDF assumes the column has a range in it else it's a single cell.

Also added a few functions I needed.

What do you think about it?

expandcollapse popup

Case StringInStr($s_MEExcelCom, "SetCellFontColor")
                    If IsInt($s_i_ExcelValue) = 1 Then
                        If $i_Row > 0 Then
                            .Application.Cells ($i_Row, $s_i_Column).Font.ColorIndex = $s_i_ExcelValue
                        Else
                            .Application.Range ($Range).Font.ColorIndex = $s_i_ExcelValue
                        EndIf
                    EndIf
                Case StringInStr($s_MEExcelCom, "SetCellColor")
                    If IsInt($s_i_ExcelValue) = 1 Then
                        If $i_Row > 0 Then
                            .Application.Cells ($i_Row, $s_i_Column).Interior.ColorIndex = $s_i_ExcelValue
                        Else
                            .Application.Range ($Range).Interior.ColorIndex = $s_i_ExcelValue
                        EndIf
                    EndIf
                Case StringInStr($s_MEExcelCom, "SetFontType")
                    If StringInStr("Normal,Bold,Italic,Underline", $s_i_ExcelValue, "") Then
                        Switch($s_i_ExcelValue)
                            Case "Italic"
                                .Application.Range ($Range).font.italic = 1
                            Case "Bold"
                                .Application.Range ($Range).font.bold = 1
                            Case "Underline"
                                .Application.Range ($Range).font.underline = 2
                            Case "Normal"
                                .Application.Range ($Range).font.italic = 0
                                .Application.Range ($Range).font.bold = 0
                                .Application.Range ($Range).font.underline = 0
                        EndSwitch
                    EndIf
                Case StringInStr($s_MEExcelCom, "SetHorizontalAlign")
                    Switch($s_i_ExcelValue)
                        Case "Left"
                            .Application.Range ($Range).HorizontalAlignment = -4131; xlLeft
                        Case "Center"
                            .Application.Range ($Range).HorizontalAlignment = -4108; xlCenter
                        Case "Right"
                            .Application.Range ($Range).HorizontalAlignment = -4152; xlRight
                    EndSwitch

Then you also need

Func _XLSetFontType(ByRef $s_FilePath, $s_i_Sheet, $s_i_Column, $s_FontType, $s_i_Visible)
    $var = _ExcelCOM($s_FilePath, $s_i_Sheet, $s_i_Column, 1, "SetFontType", "NoSave", $s_FontType, $s_i_Visible, "NOTExit", "NotLastRow", "NOTToColumn")
EndFunc  ;==>_XLSetFontType
Func _XLSetHorizontalAlign(ByRef $s_FilePath, $s_i_Sheet, $s_i_Column, $s_Align, $s_i_Visible)
    $var = _ExcelCOM($s_FilePath, $s_i_Sheet, $s_i_Column, 1, "SetHorizontalAlign", "NoSave", $s_Align, $s_i_Visible, "NOTExit", "NotLastRow", "NOTToColumn")
EndFunc  ;==>_XLSetHorAlign

Just an idea, but it works for me.

Dick

[randallc]

_XLFontsAndFormatExample.au3Hi,

Thanks for your input; I'll look at the horizontal and bold and the way it works!

If have changed the UDF yesterday to make it so you have a choice. If the row is less than 1 then the UDF assumes the column has a range in it else it's a single cell.

[row would work; I usually ignore the row if there is a letter or range; inbuilt into all the functions here]

I thought, though, that it already worked a ssingle cell or range, as in all the other functions, so i don't know that that is needed?... Am I wrong [in the "simulcal.au3 example, I used both cell numbers and ranges at various points, as examples]

Do you agree?

Best, Randall

[PS xlRight seems to give Centre?...]

Edited May 1, 2006 by randallc

[blitzkrg]

The color functions work great!

Thanks guys..

[Simucal]

Nice additions randall.

As far as it setting the cells to center align rather than right align has to do with the "or" operator you have in your Switch.

Case StringInStr($s_MEExcelCom, "SetHorizontalAlign")
                    Switch($s_i_ExcelValue)
                        Case "Left"
                            .Application.Range ($Range).HorizontalAlignment = -4131; xlLeft
                        Case "Center";or "Centre"
                            .Application.Range ($Range).HorizontalAlignment = -4108; xlCenter
                        Case "Right"
                            .Application.Range ($Range).HorizontalAlignment = -4152; xlRight
                            MsgBox(0,"Test", "Attempting to right align")
                    EndSwitch
            EndSelect

After I commented out the ";or "Centre"" it worked great.

While we are doing all this, we might as well add RowHeight as well.

Thanks randall. Keep us update on your work,

-Sim

[Dickb]

_XLFontsAndFormatExample.au3Hi,

Thanks for your input; I'll look at the horizontal and bold and the way it works!

[row would work; I usually ignore the row if there is a letter or range; inbuilt into all the functions here]

I thought, though, that it already worked a ssingle cell or range, as in all the other functions, so i don't know that that is needed?... Am I wrong [in the "simulcal.au3 example, I used both cell numbers and ranges at various points, as examples]

Do you agree?

Best, Randall

[PS xlRight seems to give Centre?...]

You are right. I just tested it and it works. Great job.

Thanks, Dick

[blitzkrg]

I'm having trouble with ranges..

if i run the code to color each cell individually it works fine, but when i try to setup a range, i get errors.

I assume from the notes in the UDF, that when setting up a range, the row field is ignored (that's what the notes say)

so i have this line

$colorme = _XLSetCellColor($app1,$asheet,"A"&$ini1&":AM"&$ini1,$color,1)

it does not work

this one does (i specified single column and $ini1 = my chosen row)

$colorme1 = _XLSetCellColor($app1,$asheet,"A",$ini1,$color,1)

my row is specified earlier in my script (via input box - i.e. "what row would you like to process)

so $ini1 = that row number put into the input box

so i input row 25 and wanted color row 25 yellow from column A through AM

it should be ,"A"&$ini1&":AM"&$ini1 (which no row is specified becasue i'm specifiying it in the column field

correct? or what am i doing wrong?

thanks

Edited May 1, 2006 by blitzkrg

[Dickb]

Nice additions randall.

As far as it setting the cells to center align rather than right align has to do with the "or" operator you have in your Switch.

Case StringInStr($s_MEExcelCom, "SetHorizontalAlign")
                    Switch($s_i_ExcelValue)
                        Case "Left"
                            .Application.Range ($Range).HorizontalAlignment = -4131; xlLeft
                        Case "Center";or "Centre"
                            .Application.Range ($Range).HorizontalAlignment = -4108; xlCenter
                        Case "Right"
                            .Application.Range ($Range).HorizontalAlignment = -4152; xlRight
                            MsgBox(0,"Test", "Attempting to right align")
                    EndSwitch
            EndSelect

After I commented out the ";or "Centre"" it worked great.

While we are doing all this, we might as well add RowHeight as well.

Thanks randall. Keep us update on your work,

-Sim

The helpfile says that

Case "Center" or "Centre

should be

Case "Center", "Centre"

I've test it and it works for me.

Dick

[randallc]

Hi

@Blitzkrig

$colorme = _XLSetCellColor($app1,$asheet,"A"&$ini1&":AM"&$ini1,$color,1)

need s the row, even though it will be ignored;

$colorme = _XLSetCellColor($app1,$asheet,"A"&$ini1&":AM"&$ini1,"whatever",$color,1)

I hope that works?

best, Randall

[randallc]

Hi,

I have posted correct justify syntax; sorry.

I guess coding for combined fonttypes too at some stage? [and Rowheight, centre across , etc?... anyone?]

Best, Randall

[PS someone did borders once, too....?]

Edited May 2, 2006 by randallc

[Dickb]

Hi,

I have posted correct justify syntax; sorry.

I guess coding for combined fonttypes too at some stage? [and Rowheight, centre across , etc?... anyone?]

Best, Randall

[PS someone did borders once, too....?]

Hi Randall,

because I needed it I have made this yesterday. It's not perfect yet but it works quite well.

expandcollapse popup

Case StringInStr($s_MEExcelCom, "SetBorders")
                    $a_Border = StringSplit($s_i_ExcelValue, ",;")
                    If IsArray($a_Border) Then
                        For $i_Bindex = 1 to ($a_Border[0] -1) Step 4
                            Switch($a_Border[$i_Bindex])
                                Case "EdgeLeft", "LeftEdge"
                                    $i_Border = 7; xlEdgeLeft
                                Case "EdgeTop", "TopEdge"
                                    $i_Border = 8; xlEdgeTop
                                Case "EdgeBottom", "BottomEdge"
                                    $i_Border = 9; xlEdgeBottom
                                Case "EdgeRight", "RightEdge"
                                    $i_Border = 10; xlEdgeRigh
                                Case "InsideVertical"
                                    $i_Border = 11; xlInsideVertical
                                Case "InsideHorizontal"
                                    $i_Border = 12; xlInsideHorizontal
                                Case "Left"
                                    $i_Border = -4131; xlLeft
                                Case "Right"
                                    $i_Border = -4152; xlRight
                                Case "Top"
                                    $i_Border = -4160; xlTop
                                Case "Bottom"
                                    $i_Border = -4107; xlBottom
                                Case Else
                                    ContinueLoop
                            EndSwitch
                            If $a_Border[$i_Bindex + 1] <> "Default" Then; Line Style
                                .Application.Range ($Range).borders($i_Border).LineStyle = Number($a_Border[$i_Bindex + 1])
                            EndIf
                            If $a_Border[$i_Bindex + 2] <> "Default" Then; Weight
                                .Application.Range ($Range).borders($i_Border).Weight = Number($a_Border[$i_Bindex + 2])
                            EndIf
                            If $a_Border[$i_Bindex + 3] <> "Default" Then; Color Index
                                .Application.Range ($Range).borders($i_Border).ColorIndex = Number($a_Border[$i_Bindex + 3])
                            EndIf
                        Next
                        $a_Border = ""
                    EndIf

and

Func _XLSetBorders(ByRef $s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, $i_Border, $s_i_Visible)
    $var = _ExcelCOM($s_FilePath, $s_i_Sheet, $s_i_Column, $i_Row, "SetBorders", "NoSave", $i_Border, $s_i_Visible, "NOTExit", "NotLastRow", "NOTToColumn")
EndFunc  ;==>_XLSetBorders

To use it use:

_XLSetBorders($XLSFile, 1, "A2:D4, 0, "Right,1,2,Default", 0)

It is also possible to add more groups of parameters in one command, like:

_XLSetBorders($XLSFile, 1, "A2:D4, 0, "Right,1,2,Default;Bottom,1,2,3", 0)

Four paramaters are passed as one string, seperated by comma's or semicolons.

1. Side of selection or cell.

2. Line style

3. Weight

4. Color index.

To ignore a parameter I have used the text 'Default' because the parameters can also be negative, so -1 can't be used.

Example:

$XLSFile = @TempDir & "\Blank.xls"
_XLCreateBlank($XLSFile)
_XLPaste($XLSFile, 1, "A", 1, "File Name" & @TAB & "Size" & @TAB & "Date" & @TAB & "Time" & @TAB & "Version", 1)
_XLPaste($XLSFile, 1, "A", 2, "This is a file name" & @TAB & "1.234.567" & @TAB & "12/10/2005" & @TAB & "11:19" & @TAB & "1..0.0.9", 1)
_XLSetCellColor($XLSFile, 1, "A1:E1", 0, 27, 1)
_XLSetColumnWidth($XLSFile, 1, "A:E", "Autofit", 1)
_XLSetHorizontalAlign($XLSFile, 1, "A1:E1", 0, "Center", 1)
_XLSetFontType($XLSFile, 1, "A1:E1", 0, "Bold", 1)
_XLSetFontType($XLSFile, 1, "A1:E1", 0, "Italic", 1)
_XLSetBorders($XLSFile, 1, "A1:E1", 0, "BottomEdge,1,3,Default;RightEdge,1,3,Default;InsideVertical,1,2,3", 1)