Sign in to follow this  
Followers 0
water

Color management for Excel Charts

7 posts in this topic

#1 ·  Posted (edited)

Is anyone familiar with the color management of Excel with charts? Is there any good reading you can recommend?

MSDN just gives a listing of all objects, methods and properties but no explanation how they are related and how to use them?

Properties like Color, ColorIndex, SchemeColor, ObjectThemeColor, ThemeColor confuse me a bit.

Any hint is greatly appreciated.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2016-06-02 - Version 1.4.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-05-09 - Version 1.2.0.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
Tutorials:
ADO - Wiki

Share this post


Link to post
Share on other sites



#3 ·  Posted (edited)

Taietel, thanks for the reply.

The pdf is a quite good reading and gives a lot of information what color to use for which purpose.

Where I need information is how to use the COM objects, methods and properties.

Color: lets you specify the color as red, green, blue values

ColorIndex: lets you specify the color as a number from 0-56 of the current color palette.

SchemeColor: When I want to color a fill I have to use SchemeColor (values from 0-56) but it seems to use a different color palette because I get different colors compared to ColorIndex.

So I need a good reading explaining how to use the different methods and properties. It should explain Excel 2007 and Excel 2010.

Thanks

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2016-06-02 - Version 1.4.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-05-09 - Version 1.2.0.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
Tutorials:
ADO - Wiki

Share this post


Link to post
Share on other sites

Thanks a lot! Exactly what I need to start. Will keep me busy for quite some time.

Do you know of something similar for Excel 2007 and later? I know that Microsoft made big changes to the charting engine after Excel 2003. Charts created in Excel 2003 or earlier look completely different in Excel 2007 (in respect of colors). Themes were added so there are a lot of possibilities in the newer versions of Excel.

The findings will go into our ExcelChart UDF. As soon as we understand how colors are handled by Excel 2007 and Excel 2010 we will release the first alpha of our UDF.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2016-06-02 - Version 1.4.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-05-09 - Version 1.2.0.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
Tutorials:
ADO - Wiki

Share this post


Link to post
Share on other sites

Thanks, I will give it a try tomorrow.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2016-06-02 - Version 1.4.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2016-05-09 - Version 1.2.0.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
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
Sign in to follow this  
Followers 0

  • Similar Content

    • SorryButImaNewbie
      Excel workbook is an object, and not [SOLVED]
      By SorryButImaNewbie
      Hello wise and respected users of this forum!
      I  haven't used autoit for a long time, but now I try to use it to automate a process in excel (I may be better of using VBA, but I would like to refresh my knowladge)
      I can open/close excel documents, and can interact with them with Send key commands, but I can't really use the _Excel functions
       
      Func ExcelRead() ;Local $oExcel = WinGetHandle("Excel") ;WinActivate($oExcel) ;sleep(1000) Local $oExcel = ObjGet("", "Excel.Application") WinActivate($oExcel) WinActivate("Excel") ;$oExcel.Visible = 1 ;$oExcel.workbooks.add If IsObj($oExcel) Then MsgBox($MB_SYSTEMMODAL, "", "The variable is an object") Else MsgBox($MB_SYSTEMMODAL, "", "The variable is not an object") EndIf Local $aResult = _Excel_RangeRead($oExcel, 1, "A1", 1) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Data successfully read." & @CRLF & "Please click 'OK' to display the formulas of cells A1:C1 of sheet 2.") _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 2 - Cells A1:C1 of sheet 2") #cs Local $ValamiExcel = WinGetHandle("Excel") Sleep(1000) WinActivate($ValamiExcel) Local $oRange = $ValamiExcel.ActiveSheet.Range("A").EntireColumn #ce EndFunc This is one of my function and I have a problem here.
      IsObj tells me the $oExcel is an object, right after that I got the 
      1 - $oWorkbook is not an object or not a workbook object  error
      Honestly I'm not sure what to do with that, I may have some problems with excel terms (workbook is the "entire" excel file that can have different worksheets right?), but i think my code should work and can't really work around this object not object problem.
      Thank you for any insight or help that you can share with me! and of course have a nice day
       
    • MrCheese
      Big data set excel
      By MrCheese
      Hi Guys,
      Appreciate the help!
      Without getting into details... basically,
      So, I have a large data set (about 10k rows, and 10 columns).
      I need autoit to select A2, then paste it into an external program.
      then, i need it to select B2 and paste it into another field, then C2, into another, D2 into another etc.
      Then move onto row 3 and repeat.
      Is there an easier way to cycle the row numbers in the loop aside from what I have done before on smaller data sets (ie. <30) where $run is the reference to the addition. Coding up 20k of if $run = blar then blar blar blar seems a little tedious.
      I used _Excel_RangeCopyPaste before as shown below.... i am also not sure if some of my code is not necessary. Any help on that would be appreciated also.
       
      Thanks for your help!
      #include <Excel.au3> ;assist variables etc to specific excel books etc. Func cellselect() if $run = 1 then $data1 = A2 $data2 = B2 $data3 = C2 endif if $run = 2 then $data1 = A3 $data2 = B3 $data3 = C3 endif EndFunc $run=0 while 1 $run = $run+1 winactivate("spreadsheet title that is open already") cellselect() local $oRange = $oWorkbook.ActiveSheet.Range($data1) _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oRange, Default) Winactivate ("other program") mouseclick(Left,$pos1x,$pos1y) send("^v") sleep(1000) local $oRange = $oWorkbook.ActiveSheet.Range($data2) _Excel_RangeCopyPaste($oWorkbook.ActiveSheet, $oRange, Default) Winactivate ("other program") mouseclick(Left,$pos2x,$pos2y) send("^v") ;do stuff here with data ,3,4,5 etc, flicking between excel and program, pasting specific data fields and then exit WEnd  
    • AndyS01
      Need to convert Excel time values to actual time string
      By AndyS01
      I have an Excel file that I want to read and display the dates and times from each row, but the time value is a small decimal number.
      I want to convert that number to an actual time string.
      The Excel data is:
      Col A Col B Col C 6/17/2016 1:00:00 Date is 6/17/2015, time is 1:00:00 AM 6/17/2016 1:00:01 Date is 6/17/2015, time is 1:00:01 AM 6/17/2016 2:00:00 Date is 6/17/2015, time is 2:00:00 AM 6/17/2016 3:00:00 Date is 6/17/2015, time is 3:00:00 AM My test code is:
      #include <Excel.au3> #NoTrayIcon #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Icon=THTracker.ico #AutoIt3Wrapper_UseUpx=n #AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 #AutoIt3Wrapper_UseX64=N #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** test() Func test() Local $fn, $ffn, $obj, $ndx, $ndx2, $ar $fn = "C:\Util\AutoIT-src\myStuff\THTracker\Copy of 2016 Easton 100.xlsx" ConsoleWrite("+++: $fn ==>" & $fn & "<==" & @CRLF) $ffn = FileGetShortName($fn, 1) ConsoleWrite("+++: $ffn ==>" & $ffn & "<==" & @CRLF) $obj = _ExcelBookOpen($ffn, 0, 1) ; open excel in the background ConsoleWrite("+++: isObj($obj) = " & IsObj($obj) & @CRLF) $ar = _ExcelReadSheetToArray($obj) _ExcelBookClose($obj) ConsoleWrite("+++: $ar[0][0] = " & $ar[0][0] & @CRLF) ConsoleWrite("+++: $ar[0][1] = " & $ar[0][1] & @CRLF) For $ndx = 1 To $ar[0][0] ConsoleWrite("+++: Date = " & $ar[$ndx][1] & @CRLF) ConsoleWrite("+++: Time = " & $ar[$ndx][2] & @CRLF) For $ndx2 = 1 To $ar[0][1] - 1 ConsoleWrite("+++: [" & $ndx & "][" & $ndx2 & "] = " & $ar[$ndx][$ndx2] & @CRLF) Next Next EndFunc ;==>test The console output is:
      +++: $fn ==>C:\Util\AutoIT-src\myStuff\THTracker\Copy of 2016 Easton 100.xlsx<== +++: $ffn ==>C:\Util\AUTOIT~1\myStuff\THTRAC~1\COPYOF~1.XLS<== +++: isObj($obj) = 1 +++: $ar[0][0] = 4 +++: $ar[0][1] = 4 +++: Date = 20160617000000 +++: Time = 0.0416666666666667 +++: [1][1] = 20160617000000 +++: [1][2] = 0.0416666666666667 +++: [1][3] = Date is 6/17/2015, time is 1:00:00 AM +++: Date = 20160617000000 +++: Time = 0.0416782407407407 +++: [2][1] = 20160617000000 +++: [2][2] = 0.0416782407407407 +++: [2][3] = Date is 6/17/2015, time is 1:00:01 AM +++: Date = 20160617000000 +++: Time = 0.0833333333333333 +++: [3][1] = 20160617000000 +++: [3][2] = 0.0833333333333333 +++: [3][3] = Date is 6/17/2015, time is 2:00:00 AM +++: Date = 20160617000000 +++: Time = 0.125 +++: [4][1] = 20160617000000 +++: [4][2] = 0.125 +++: [4][3] = Date is 6/17/2015, time is 3:00:00 AM +>12:52:23 AutoIt3.exe ended.rc:0 +>12:52:23 AutoIt3Wrapper Finished. >Exit code: 0 Time: 1.206 Note that the time values [n][2] are all decimal values like  0.0416666666666667, 0.125, etc.
    • gajoltomee
      How to avoid save violation error?
      By gajoltomee
      How to avoid save violation error in excel? 
    • PINTO1927
      open excel and update pivot
      By PINTO1927
      hello guys, I'm doing one excel pivot connected to a mysql script to display it in a table array . I need you to open the excel file , the pivot is updated .
       
      where do I fix it?