Change Colors in Excel file with COM

Get help with using AutoHotkey and its commands and hotkeys
Sergio
Posts: 45
Joined: 29 Sep 2013, 16:36

Change Colors in Excel file with COM

26 Mar 2014, 15:00

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
User avatar
Relayer
Posts: 130
Joined: 30 Sep 2013, 13:09
Location: Delaware, USA

Re: Change Colors in Excel file with COM

26 Mar 2014, 16:18

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
Posts: 45
Joined: 29 Sep 2013, 16:36

Re: Change Colors in Excel file with COM

26 Mar 2014, 16:49

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
User avatar
TheDewd
Posts: 1391
Joined: 19 Dec 2013, 11:16
Location: USA

Re: Change Colors in Excel file with COM

27 Mar 2014, 08:42

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%
Image Bulldozer - Sokoban inspired game from 1994 recreated in AutoHotkey.
Guest

Re: Change Colors in Excel file with COM

27 Mar 2014, 09:48

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

Return to “Ask For Help”

Who is online

Users browsing this forum: Robbl, SpikeVanHellsing, Xtra and 55 guests