Change Colors in Excel file with COM

[Sergio]

I have started to write files to excel through COM using the code below. My one issue now is that I'd like to be able to change background & foreground colors as well as font markup (to make it bold). Is that possible through COM as well? If so, can someone point me to a tutorial?

Spoiler

Code: Select all

NewFileName := "MyNewFile"
Directory := A_Desktop "\"

objExcel            := ComObjCreate("Excel.Application")
objWorkBook         := objExcel.Workbooks.Add
objExcel.Visible    := true

loop, read, %A_Desktop%\file.txt
{
    Row := A_Index
    newstr := RegExReplace(A_loopreadline, "\s+", "`t")
    loop, parse, newstr, `t
        objExcel.Worksheets(1).Cells(Row, A_Index).Value := A_LoopField   
}
objWorkBook.SaveAs(Directory NewFileName)
objExcel.Quit()
objExcel := ""
return

[Relayer]

Look for Excel VBA macros that do what you want. It is fairly easy to translate them into AHK once you see the objects, methods, and properties they use. Then use the MSDN online references to get the details.

Relayer

[Sergio]

I was hoping you'd say that it'd be easy! I have a macro I like. It just filters ranges of text & changes the background color for the most part (below).

I guess the big question is how do I implement a VBA macro on an Excel sheet?

Spoiler

Sub Macro2()
'
' Macro2 Macro
'

'
Selection.AutoFilter
ActiveSheet.Range("$A$1:$N$1572").AutoFilter Field:=1, Criteria1:="Amazon"
Range("A1:N1572").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
ActiveSheet.Range("$A$1:$N$1572").AutoFilter Field:=1, Criteria1:="Buy"
Range("A1:N1572").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveSheet.Range("$A$1:$N$1572").AutoFilter Field:=1, Criteria1:="eBay"
Range("A1:N1572").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A1").Select
ActiveSheet.Range("$A$1:$N$1572").AutoFilter Field:=1, Criteria1:="Local"
Range("A1:N1572").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Selection.AutoFilter
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Selection.Font.Bold = True
End Sub

[TheDewd]

Here's a script I had saved, which changed the cell color as well as adds borders, etc...

Code: Select all

#SingleInstance Force

xl := ComObjCreate("Excel.Application")
xl.Visible := True

wb := xl.Workbooks.Add(-4167)
wb.WorkSheets(1).Name := "Custom Name"

xl.Range("A1").Value := "STYLE"
xl.Range("B1").Value := "COLOR"
xl.Range("B2").Value := "TEMP"
xl.Range("C1").Value := "SIZE"

xl.Range("A1:C1").HorizontalAlignment := -4108
xl.Range("B2").HorizontalAlignment := -4108
xl.Range("A1:C1").Font.Bold := True

xl.Range("A1:C1").Interior.ColorIndex := 15

xl.Range("A1:C10").Borders.LineStyle := 1

VAR := xl.Range("B2").Value

MsgBox, %VAR%

[Guest]

[quote="TheDewd"]Here's a script I had saved, which changed the cell color as well as adds borders, etc...

[code=autohotkey file=Script.ahk]#SingleInstance Force

xl := ComObjCreate("Excel.Application")
xl.Visible := True

wb := xl.Workbooks.Add(-4167)
wb.WorkSheets(1).Name := "Custom Name"

xl.Range("A1").Value := "STYLE"
xl.Range("B1").Value := "COLOR"
xl.Range("B2").Value := "TEMP"
xl.Range("C1").Value := "SIZE"

xl.Range("A1:C1").HorizontalAlignment := -4108
xl.Range("B2").HorizontalAlignment := -4108
xl.Range("A1:C1").Font.Bold := True

xl.Range("A1:C1").Interior.ColorIndex := 15

xl.Range("A1:C10").Borders.LineStyle := 1

VAR := xl.Range("B2").Value

MsgBox, %VAR%[/code][/quote]
Very useful! Thanks. I think I see how it works.