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-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (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-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (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-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (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-08-18 - Version 1.4.6.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (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

    • jloyzaga
      create combo box dynamically
      By jloyzaga
      I'm trying to use GUICtrlCreateCombo but I need it to get the list values from an excel spreadsheet column - that makes it dynamic. My method is to prompt for the Module name (this gives me the application to run the automation test) this also gives me the name of the folder that stores all the spreadsheets.
      1.I then get the list of spreadsheet names and use that as input for another list (this can be select 1 or many or all - don't know which control to use...)
      2.On selection I then go to the spreadsheets selected and display columns C, E from TestController tab in a further combo box to select 1 or many or all.
      3. keep doing point 2 above until all selected in 1 are complete. I'm wondering if I just display as many combo boxes as items selected in point 1 or loop thru them somehow.
      Very complex for my little brain - any advice to simplify is greatly appreciated
      Joe
       
      Accounts_TestFlow.xls
    • SorryButImaNewbie
      [SOLVED] _Excel_BookOpen how to connect to already opened Excel Application, without knowing the filepath
      By SorryButImaNewbie
      Hello dear users!
      I have a bit of a problem with my excel script I try to make. My goal is that my script would read through an excelfile's columns and rows, identifiying key words, then I would like to reorganize the rows and colums in the a specified "way", "format" (we get different kind of excels in and i wish to produce a uniform excels from them, the data is the same, so I know that there is an XY column somewhere, I just have to find it, and put it in the right place)
      Now I can read values from cells (or range of cells) but only if I know the exact file name and path.
      Can I make autoit to somehow attach to the already opened excel? This way I would be able to make the script, so the user just open the excel which he/she wants to format, clicks on go, and viola.
      Instead now I'm thinking adding 2 textboxes to my GUI so the user can write down the filename and file path, which isnt that ideal.
      My original idea, was to make the script find the "borders" of the filled out excel form, copy it away to the right, delete the original data, reorganize it the way it should be (to the area which it cleared), and thats it. The user can then quickly check if everything is okey, and he/she can delete the copied original data set if its needed.
       
      So in short my problem is, how to connect my script to an excel thats presumaby already opened, without knowing the filename and path?
      Thank you for your help! (and sorry if there is already a post about this I havent found it)
    • SorryButImaNewbie
      _ExcelRangeFind error in line 656
      By SorryButImaNewbie
      Good day everyone!
      I'm having a problem with my underconstruction excel script (again... , sorry haven't used autoIT for a while, pretty sure this is a basic error as well)
      If $xls2 = 1 Then $ExcelName = $aWorkBooks[2][1] $ExcelPath = $aWorkBooks[2][2] MsgBox($MB_SYSTEMMODAL, "Excel", " Excel file path: " & $ExcelPath) MsgBox($MB_SYSTEMMODAL, "Excel", " Excel file name: " & $ExcelName) EndIf If $xls3 = 1 Then $ExcelName = $aWorkBooks[3][1] $ExcelPath = $aWorkBooks[3][2] ;MsgBox($MB_SYSTEMMODAL, "Excel", " Excel file path: " & $ExcelPath) EndIf $ExcelObject = _Excel_BookAttach($ExcelName, "filename") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 2", "Error attaching to '" & $ExcelObject & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 2", "Search by 'filename':" & @CRLF & @CRLF & "Successfully attached to Workbook '" & $ExcelObject & "'." & @CRLF & @CRLF & "Value of cell A2: " & $ExcelObject.Activesheet.Range("A2").Value) $knkod = _Excel_RangeFind($ExcelObject, "knkod") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value 'knkod' (partial match)." & @CRLF & "Data successfully searched.") _ArrayDisplay($knkod, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment") I ask my users, to only run one excel instances while starting my script (so I can develope a simple a script I can, and then upgrade it once I code like i was able to, once again...), thats why I dont use UBound but give an array of 4 for possible running excel detection. If its needed I can easly add to array size for now.
      I check for given file extensions and also have an error+exit if I found more then 1 running excel. Of course I know that my code is pretty childish for now.
      I use Excel_BookAttach to attach to the excel object, the script succesfully run, and gives back the value of A2 cell (code basicly copy pasted from help file) but then I get an error in the console that reads:
      --> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
      "C:\ToolBox\AutoIT\AutoIt3\Include\Excel.au3" (656) : ==> The requested action with this object has failed.:
      $aResult[$iIndex][1] = $oMatch.Name.Name
      $aResult[$iIndex][1] = $oMatch^ ERROR
      ->17:53:47 AutoIt3.exe ended.rc:1
      +>17:53:47 AutoIt3Wrapper Finished.
      >Exit code: 1    Time: 24.24
      (sorry if i should have wrote this some other way)
      I suspect that I screw something up with the workbook/worksheet handling or that $ExcelObject isn't an object somehow. I'm not sure, but IsObj return 1 if i check $excelobject
      Edit: Maybe there is some kind of problem with the name of the excel? It has "á" in it's name, could that be a problem?
      Thank you for your insight! and sorry for the elementary questions
    • Revelation343
      Mailing from Excel/variable storing
      By Revelation343
      Purpose of script: To send emails in Outlook based on data in an excel spreadsheet. From: fields are entered for purposes of sending on behalf of (delegate), copies A2 cell from excel for To: field, Subject field is a static value and entered, returns to excel spreadsheet to read A1 and copy the first name and insert into the body of a template at an insertion point. The From, and the body of the email change based on region, so currently I have 6 different scripts that do essentially the same thing with some minor changes and want to consolidate into one script to save time.
      Question:
      To expedite the process of this and cut down on the amount of scripts, 6 in total I use daily, is it possible for me to somehow add the region to column C in excel, have autoit read column C values per row, and then decide which function, within a master script, to execute and loop this until there is no value in column C field?
      Example spreadsheet:
      Rob | rob@annuity.com |Midwest
      Annie | annie@agency.com | Midwest
      Kyle | kyle@agency.com | MidAtlantic
      Rick | rick@megasales.com | MidAtlantic
      Blank | Blank | Blank |
      Example execution:
      Run Birthday.au3, execute loop part through hotkey
      Reads row 1, C1, value is Midwest, calls Midwest(), script runs as Midwest Birthday.au3 does currently
      Reads row 2, C2, value is Midwest, calls Midwest(), script runs as Midwest Birthday.au3 does currently
      Reads row 3, C3, value is MidAtlantic, calls MidAtlantic(), script runs as MidAtlantic Birthday.au3 currently
      Reads row 4, C4, value is MidAtlantic, calls MidAtlantic(), script runs as MidAtlantic Birthday.au3 currently
      Reads row 5, C5, value is null or blank, ends script through Exit
       
      Everything I have coded in my time in AutoIt has been based mostly on mouse based movements and I don't have variable programming knowledge so I feel like I'm close to understanding how to do this, but the reading/storing variables part is beyond my current skill set. Help is appreciated.
      Mail Merges don't work as delegated in Outlook 07, for those that might be questioning why I just don't do that.
       
      MidAtlantic Birthday.au3
      Midwest Birthday.au3
    • jonson1986
      Writing array into excel cells with line breaks automatically
      By jonson1986
      I'm trying to write data from array into .text file with line break and i did it successfully.
      but now I want to write from the same array into already empty excel with with line breaks means each value of array should be written in next cell automatically as it's happening in text file using "Filewrite"
      Looking for guidance.
      Here is my code;
      $file = fileopen(@scriptdir & "\source.txt", 10) $IE = _IECreate("http://www.example.com") $source = _IEDocReadHTML($IE) FileWrite($file, $source) $target_source1 = _StringBetween($source, '<span>', '</span>') If Not @error Then For $i=0 To UBound($target_source1) -1 FileWrite (@scriptdir & "\save.txt", $target_source2[$i] & @crlf) ; I want to write above array onto excel file Next EndIf