Can IDEA identify colour formating in Excel?

ericardo79

Forums

Hi,
I normally use vscript (macros) and I wanted to create a script to pick up the value on fields which are shaded in colour in Excel.
Background: I sent out a survey in Excel to a number of people. Response came back with various fields being shaded (eg Agree, Disagree, etc). These have values of 1-5 in them. However, I need to assess the value provided on each question as a whole, hence, having them shaded doesnt help.
This brought me to the question of: How can I use IDEA to identify the value in each of the questions that was shaded in colour?
Pls do let me know what your experiences are in these scenarios. Thank you!
 
 

ravisdxb Sun, 04/21/2019 - 07:43

Try out this macro in the Excel file containing your data. Before executing the macro, please set a few variables such as maximum rows, column number with colors and where to insert the new column.
 
Sub Macro1()
 max_rows = 9
 ans_col = 2
 new_col = 3
 For i = 2 To max_rows
  Cells(i, new_col).Value = Cells(i, ans_col).DisplayFormat.Interior.Color
 Next
End Sub
 
Based on the color values, you could arrive at the required responses such as Agree, Disagree etc, either by using a VLOOKUP or by creating a nested IF formula in Excel. Then, just import into IDEA.
 
Note: An Excel file with a macro has to be saved as .xlsm file. So, you just execute this macro and save the file as .xlsx file to be imported in IDEA

ravisdxb Sun, 04/21/2019 - 23:59

Another option is to use a Python script.
 

  • open the excel_color.py in a text editor and set the required variables and save
  • launch a command prompt
  • enter the command pip install openpyxl (this allows programmatic access to an Excel file)
  • change directory to where your Excel file resides in the command prompt
  • at the command prompt enter python excel_color.py to run the script
  • this creates a new file with the color values populated in a new column after the last column
  • import into IDEA as usual
  • define a calculated field to translate color codes into required values, such as Agree, Disagree etc