Taurillon

Excel, Formatting Cells/Borders

10 posts in this topic

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. 

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

Excel Formatting.JPG

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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


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

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
 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by JLogan3o13

√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites

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


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

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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 by water

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

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

 

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by water
1 person likes this

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

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