Invoke Code to color specific Excel Cells and take Screenshot

Hi community,

due to a common issue ( Possible solution in case of problems with Excel and UiPath ), I can’t use excel currently. Moreover, I do not have access to the registry to modify entries.

So I want to work with a workaround temporarily. In this workaround, I want to use VB to color specific excel cells (and optionally take a screenshot of the excel sheet).

My input is a dictionary containing a list of cell ranges assigned with a color and the excel file of course.

My code sketch looks like this:

Dim objExcel 

Set objExcel = CreateObject("Excel.Application")


objExcel.Application.Visible = True
objExcel.Application.DisplayAlerts = False

Dim objCombinedWb = objExcel.Workbooks.Open(in_FilePath)

Set ws = objCombinedWb.Worksheets(1)

For Each item As String  In in_Colors.Keys()
	If (in_Colors(item).ToLower().COntains("green"))
			ws.Range(item).Interior.Color = Color.Green
	Else
			ws.Range(item).Interior.Color = Color.Red
		End If
Next


ws.Save
Sys.Desktop.Picture.SaveToFile(in_ScreenshotFile)

However, i guess this is the VBScript approach and not the proper approach to the “Invoke code” command since this allows to directly input a dictionary.

How can i modify this code to be also suitable for the invoke code command?

Thank you

Hi @BennyS

we can use powershell script also

below powershell script is used color excel cells

$ExcelObject = new-Object -comobject Excel.Application
$strPath1="D:\Powershell\New Microsoft Excel Worksheet.xlsx"
$ActiveWorkbook = $ExcelObject.WorkBooks.Open($strPath1)
$ActiveWorksheet = $ActiveWorkbook.Worksheets.Item(1)
$ActiveWorksheet.Range(“C1”).Interior.Color = 8454080
$ActiveWorkbook.Save()
$ActiveWorkbook.close($true)
$ExcelObject.quit()

Replace the bold text as per your document

Thanks
Robin

Thank you robin for your fast response.

Is it possible to loop through my input dictionary in an efficient way in powershell? I guess I would prefer a code solution rather than a powershell solution. However, if this isn’t possible, your solution looks quite neat!

Hi @BennyS

Yes it is possible
Even powershellscript are faster than invoke code activity based on my personal experience

Okay, how would I invoke this code then? Creating a text file, giving it a powershell extension and then calling it via “Invoke Process”?

I tried the following script but it states that it can’t call a null-valued expression. Any ideas on that?

Param
(
[Parameter(Mandatory=$true)] [string]$in_ExcelFile,
[Parameter(Mandatory=$true)] [Hashtable]$in_Cells
)

$ExcelObject = new-Object -comobject Excel.Application
$ActiveWorkbook = $ExcelObject.WorkBooks.Open($in_ExcelFile)
$ActiveWorksheet = $ActiveWorkbook.Worksheets.Item(1)
foreach ($cell in $in_Cells.Keys) 
{
	if ( $in_Cells[$cell] -Contains "green")
	{

		$ActiveWorksheet.Range($cell).Interior.ColorIndex = 10
	}
	Else
	{
		$ActiveWorksheet.Range($cell).Interior.ColorIndex = 3
	}
}
$ActiveWorkbook.Save()
$ActiveWorkbook.close($true)
$ExcelObject.quit()

Hi @BennyS ,

We have a Invoke Power Shell activity you can use that.

Thanks