alienclone

set Excel tab color from variable (SOLVED)

7 posts in this topic

#1 ·  Posted (edited)

Hopefully someone familiar with Excel COM in AutoIt will say "Oh, that's easy, you just..."

Or if someone knows how to set the tab color with the Excel UDF, either way.

specifying the hex directly works...

$oExcelDoc.WorkSheets($Driver).Tab.Color=0x00FF00

declaring the variable directly works...

$Acolor=0x00FF00
$oExcelDoc.WorkSheets($Driver).Tab.Color=$Acolor

BUT... if my variable is set by output from functions or read from ini file (which is what i am trying to do) it doesn't apply the color...

$Acolor=IniRead ( "Colors.ini", "A", "Color", "" )
$oExcelDoc.WorkSheets($Driver).Tab.Color=$Acolor

 

I have quadruple checked that the ini is being read correctly and googled til my eyes popped out and have now gone mad.

Edited by alienclone
SOLVED

if @error Then
	MsgBox(262192,"",@ComputerName&" slaps "&@UserName&" around a bit with a large trout!")
	EndIf

>Exit code: 0

REAL search results

"Yeah yeah yeah, patience. How long will that take?"  -Ed Gruberman

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

Looks like it's because you're getting "0x00FF00", a string, from the file instead of 0x00FF00, a hex value. You're going to have to convert the data type.

From IniRead():

Quote

Return Value

Success: the requested key value as a string.
Failure: the default string if requested key not found.

Try

#include <String.au3>
$Acolor=IniRead ( "Colors.ini", "A", "Color", "" )
$Acolor=_StringToHex($Acolor)
$oExcelDoc.WorkSheets($Driver).Tab.Color=$Acolor

 

Edited by rcmaehl
1 person likes this

My UDFs are generally for me. If they aren't updated for a while, it means I'm not using them myself. As soon as I start using them again, they'll get updated.

MY PROJECTS


Active: IRC UDF, WindowEx UDF
Discontinued: GithubBubbleSort UDF

Share this post


Link to post
Share on other sites
54 minutes ago, rcmaehl said:

Looks like it's because you're getting "0x00FF00", a string, from the file instead of 0x00FF00, a hex value. You're going to have to convert the data type.

From IniRead():

Try

#include <String.au3>
$Acolor=IniRead ( "Colors.ini", "A", "Color", "" )
$Acolor=_StringToHex($Acolor)
$oExcelDoc.WorkSheets($Driver).Tab.Color=$Acolor

 

Oh my goodness, that didn't even occur to me. Of course Excel doesn't know what color 'some random string of letters and numbers' is supposed to be.

It's bed time right now but when I get up I will figure out how to use _HextoString() and _StringtoHex() to convert and report back on whether or not this is the problem, sounds like it is though.

thank you rcmaehl, your script suggestion seems to be converting to the hex value of the string, I may have to play around with hextostring before storing the value, then using stringtohex to bring it back.


if @error Then
	MsgBox(262192,"",@ComputerName&" slaps "&@UserName&" around a bit with a large trout!")
	EndIf

>Exit code: 0

REAL search results

"Yeah yeah yeah, patience. How long will that take?"  -Ed Gruberman

Share this post


Link to post
Share on other sites

YAY!  After much confusion I realized I still don't understand binary/hex/dec.

BUT... thanks to rcmaehl pointing me in the right direction, i have solved the problem with...

$Acolor=IniRead ( "Colors.ini", "A", "Color", "" )
$Acolor=Execute($Acolor)
$oExcelDoc.WorkSheets($Driver).Tab.Color=$Acolor

 


if @error Then
	MsgBox(262192,"",@ComputerName&" slaps "&@UserName&" around a bit with a large trout!")
	EndIf

>Exit code: 0

REAL search results

"Yeah yeah yeah, patience. How long will that take?"  -Ed Gruberman

Share this post


Link to post
Share on other sites

<rant> having to convert a color MULTIPLE times in one script because I need to trade hexadecimal colors between a program that uses BGR, and one that uses RGB </rant>


if @error Then
	MsgBox(262192,"",@ComputerName&" slaps "&@UserName&" around a bit with a large trout!")
	EndIf

>Exit code: 0

REAL search results

"Yeah yeah yeah, patience. How long will that take?"  -Ed Gruberman

Share this post


Link to post
Share on other sites
28 minutes ago, alienclone said:

I need to trade hexadecimal colors between a program that uses BGR, and one that uses RGB

Func RGB2BGR($iColor)
    Local $sH = Hex($iColor,6)
    Return '0x' & StringRight($sH, 2) & StringMid($sH,3,2) & StringLeft($sH, 2)
EndFunc  ;==>RGB2BGR

i think the BGR2RGB func should be no problem for you

Share this post


Link to post
Share on other sites
21 hours ago, AutoBert said:
Func RGB2BGR($iColor)
    Local $sH = Hex($iColor,6)
    Return '0x' & StringRight($sH, 2) & StringMid($sH,3,2) & StringLeft($sH, 2)
EndFunc  ;==>RGB2BGR

i think the BGR2RGB func should be no problem for you

converting was not a problem, i was just complaining about different programs using different color formats.


if @error Then
	MsgBox(262192,"",@ComputerName&" slaps "&@UserName&" around a bit with a large trout!")
	EndIf

>Exit code: 0

REAL search results

"Yeah yeah yeah, patience. How long will that take?"  -Ed Gruberman

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

  • Similar Content

    • breakbadsp
      By breakbadsp
      I  want to create a excel file from my script if it does not exist.
      _ExcelBookOpen throws error=2 if file does not exist, after this error i want to create new file at this point.
      can i use _FileCreate()?
      _Logger($sLogPath, "{INFO}------: Opening Excel File: " & $sExcelPath& "") While 1 Local $oExcelTestResult = _ExcelBookOpen($sExcelPath) If @error = 2 Then If not _FileCreate($sResExcelPath) Then MsgBox(0, "Error", "Error In Opening REsult Excel File: Error: " & String(@error)) _Logger($sLogPath, "{ERROR}------: Result Excel File does not exist.. tried to create new but :ERROR : " & String(@error) & "") ExitLoop Else _Logger($sLogPath, "{INFO}------: Result Excel File does not exist.. **Created New**: ") EndIf Else ExitLoop EndIf WEnd  
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to write a value into an excel column.
      I know where it starts from, but i don't know what the end is, last non-empty cell.
      How can i get the number of last non-empty cell?
      Thanks in advance.
      Regards 
    • Nareshm
      By Nareshm
      Hi All,
      I have excel file like this
      and i want to cut cell/text from excel to other software.

       
      I have to cut the cell of B column one by one and past into other software
      If Winexists("No Data Found")
      then restore cuted cell and goto next/down side cell
      How to do it ?
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.