Jump to content
Taurillon

Excel, Formatting Cells/Borders

Recommended Posts

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. 

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

Share this post


Link to post
Share on other sites
water

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 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
iCal (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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
 

Share this post


Link to post
Share on other sites
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 by JLogan3o13

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

How to get your question answered on this forum!

Share this post


Link to post
Share on other sites
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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
iCal (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
iCal (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
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 by water
  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
iCal (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
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

×