Invoke Code to color specific Excel Cells and take Screenshot

Help Studio
, ,
1

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

2

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

3

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!

4

Hi @BennyS

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

5

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

6

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()
7

Hi @BennyS ,

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

Thanks