Excel, Formatting Cells/Borders

[Taurillon]

Hello Autoiforum Users,

i just search trough the autoit help file but i couldnt find a way to format cells.
I started playing around with Autoit just recently. In the wiki file about the Excel UDF is a remark about using the excel Com on its own. 

Right now i am not sure how to do that. I would be happy about every bit of information regarding how to use the excel Com and how it works/ how to use it in autoit. 
I cant find a good site/link when searching for Excel Com

Regarding my specific case, i am having trouble changing the "format" of cells.
I added a picture to make it more clear what i am trying to say.

 

In another post it seemed like an Excel macro documents the changes well for others. 

expandcollapse popup

Sub Makro1()
'
' Makro1 Makro
'

'
    Range("B63:F63").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Color = -16776961
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("B64:F68").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$64:$F$68"), , xlYes).Name = _
        "Tabelle3"
    Range("Tabelle3[#All]").Select
    ActiveSheet.ListObjects("Tabelle3").TableStyle = "TableStyleMedium4"
End Sub

This is the macro i created. I merged cells, gave them a border and chose a range under that  to turn into a table with a predetermined template. 

Thanks a lot for your help. 

best wishes Taurillon

[Taurillon]

I just noticed that i posted in the wrong forum i think this belongs into general help. 
Is there a way to delete my post or move it over to general help ? 

best wishes Taurillon

[water]

The wiki explains how to do formatting: https://www.autoitscript.com/wiki/Excel_UDF#Format_a_range

[Taurillon]

thats for the link. I was hoping you could provice more information about the excel com. 

I am still unsure about how to format a range with a "table style".

I am referring to this bit

 ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$64:$F$68"), , xlYes).Name = _
        "Tabelle3"
    Range("Tabelle3[#All]").Select
    ActiveSheet.ListObjects("Tabelle3").TableStyle = "TableStyleMedium4"

thanks for your help taurillon
 

[JLogan3o13]

3 hours ago, Taurillon said:

thats for the link. I was hoping you could provice more information about the excel com. 

@Taurillon if you really would like to learn how things work, the object model reference is the place to start. There is no shortage of information out there.

 https://msdn.microsoft.com/en-us/library/office/ff834729.aspx - Working with Tables and Ranges

Edit: Bleh, removed my first link as I put in the wrong one. Water has the correct link for the Object Model Reference.

Edited August 11, 2016 by JLogan3o13

[water]

Can't test at the moment but it should be something like this:

; XlListObjectSourceType Enumeration - https://msdn.microsoft.com/en-us/library/ff820815(v=office.14).aspx
Global $xlSrcRange = 1
; XlYesNoGuess Enumeration - https://msdn.microsoft.com/en-us/library/ff838812(v=office.14).aspx
Global $xlYes = 1
$oExcel.ActiveSheet.ListObjects.Add($xlSrcRange, Range("$B$64:$F$68"), Default, $xlYes).Name = "Tabelle3"
$oExcel.ActiveSheet.ListObjects("Tabelle3").TableStyle = "TableStyleMedium4"

The COM Reference for Excel 2010 can be found here.
https://msdn.microsoft.com/en-us/library/ff846392.aspx

[Taurillon]

Thanks a lot. 

I am having real trouble taking information about the Objects and Methods and using it in autoit. 

I can use the VBA code just fine but i dont know how to put it in autoit 

Do you know of any rescources that can bridge that gap ?

I am learning from the autoit help file just fine. I am trying to find the missing pieces that allow me to learn what Excel Com has to offer on my own.

 

@water I am getting:  error 

Spoiler

$xlYes previously declared as a 'Const'

And error:

Spoiler

 Range() : undefined function

 

Taurillon

[water]

Seems $xlYes is being defined in the Excel UDF. Simply remove the line from your script

In AutoIt you always need to specify the whole "path" from an object to a method/property. Or you use With:

; XlListObjectSourceType Enumeration - https://msdn.microsoft.com/en-us/library/ff820815(v=office.14).aspx
Global $xlSrcRange = 1
With $oExcel.ActiveSheet
    .ListObjects.Add($xlSrcRange, .Range("$B$64:$F$68"), Default, $xlYes).Name = "Tabelle3"
    .ListObjects("Tabelle3").TableStyle = "TableStyleMedium4"
EndWith

Unfortunately I can't test at the moment - so the above code might still be buggy

Edited August 11, 2016 by water

[Taurillon]

Code works perfectly 

the listobjects.add method calls for an range object.

Is .Range(... ) a valid range object or is only $oExcel.activeSheet.Range(...) a valid range object in autoit? 

Taurillon

 

[water]

In this case a range object is either returned by

• $oExcel.ActiveSheet.Range(...)  or
• .Range(... ) when used in a With $oExcel.ActiveSheet / EndWith construct

With/EndWith prefixes all parts of a statement starting with a dot with the expression defined in the With statement.

A better description can be found in the help file: https://www.autoitscript.com/autoit3/docs/intro/ComRef.htm

Edited August 11, 2016 by water