[UIPath] VBA macro for formatting headers in Excel

Here's a neat piece of code I use whenever I needed to apply a macro that formats headers on an Excel workbook. I always come across this step whenever I write a datatable to an Excel file. Datatables are being copied as plain text so you have to format it to make it look more presentable. The goal is to programmatically turn this unformatted workbook...

to a formatted one which is very much readable and ready for reporting:

Here you go:

Private Sub FormatHeaders()
    Dim ws as Worksheet
    For Each ws in Worksheets
        ws.Activate
        Cells.EntireColumn.Autofit
        Cells.EntireRow.Autofit
        Rows("1:1").Font.Bold = True
        Range(Range("A1"),Range("A1").End(xlToRight)).Interior.Color = RGB(173, 216, 230)
    Next
    Sheets(1).Select
End Sub

What this macro does is loop through all the sheets in the workbook and do the following:

• Autofit all columns
• Autofit all rows
• Bold the headers
• Add background color to the headers

Then it will focus back on the first sheet.

Now it's easy to read and ready to sent out. Remember to setup MS Excel's trust settings if you're going to run an external macro.

Hope this helps!

Share:

Read More

[UIPath] Error: Programmatic access to Visual Basic Project is not trusted

If you encounter this error on UIPath saying "Programmatic access to Visual Basic Project is not trusted", it could mean that your MS Excel's Macro Trust Settings is disabled.

By default, Excel blocks any programmatic access to the application unless the user permits it. You may be invoking an external VBA macro file in your UIPath workflow that's causing this error. To enable trust settings, simply do the following:

1. Open a blank workbook on MS Excel
2. Got to File >> Options
3. Click on Trust Center >> Trust Center Settings
   
   
   
   
   
4. Click on Macro Settings >> Check ‘Trust Access to the VBA project object model’ (under Developer Macro Settings)
   
   
   
   
   
5. Click OK to exit Trust Center window >> Then Click OK again to exit Excel Options window

Let me know if it worked! :)

Share:

Read More

[UIPath] Helpful VB expressions for date, string, and array/collection values

Here are my most commonly used VB expressions for handling date, string, and array/collection values. Will add more to these as I go along. UIPath activities essentially use VB.NET expressions so their API browser is extremely helpful in learning functions and syntax. .NET API Browser:
https://docs.microsoft.com/en-us/dotnet/api/?view=netframework-4.7.2

String

How to print new line on Message Box
Use vbCrLf
ie. "Hello" + vbCrLf + "World"

How to concatenate string and variable values
Use + operator
ie. "My name is " + var_name

How to split string value based on delimiter
Use the Split method to split a line of text into array values based on a delimiter
ie. Split("apple~bear~cat~dog","~") will return an array with 4 values.

To access them, you can loop through the array or for smaller and more defined formats you can simply retrieve the value based on its index number.

Split("apple~bear~cat~dog","~")(0) returns "apple"
Split("apple~bear~cat~dog","~")(1) returns "bear"
Split("apple~bear~cat~dog","~")(2) returns "cat"
Split("apple~bear~cat~dog","~")(3) returns "dog"

How to combine all array values into a string
Use the Join method
ie. var stringArray = {"Apple","Bear","Cat"}
String.Join("/",stringArray) will result to "Apple/Bear/Cat"

How to trim leading and trailing spaces from a text
Use the Trim method
ie. Trim("     This is a sentence    with lots of spaces.    ")
Note that Trim() removes only the spaces at the start and end, not the inside of the string.

How to replace certain characters in a string
Use the Replace method
ie. "apple~bear~cat~dog".Replace("~","!")
Replaces all instances of ~ with !, so that leaves me with "apple!bear!cat!dog"

Date/Time

How to format date value
Use ToString method
ie. Now.Date.ToString("MMMM yyyy")
will give to the current date displayed in this format: January 2018

For other custom date and time formats:
https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

How to convert string value to DateTime format

Use Convert class

ie. Convert.ToDateTime("January 31, 2019")

For more details about the Convert class:

https://docs.microsoft.com/en-us/dotnet/api/system.convert?view=netframework-4.7.2

How to get first day of the month

Use the Date function and just format the day value as 1

ie. Now.Date.ToString("MMMM 1, yyyy")

will get you January 1, 2019

How to get last day of the month

Use AddMonths method to get the first day of the next month, then subtract 1 day using the AddDays method. 

ie. Convert.ToDateTime(Now.Date.ToString("MMMM 1, yyyy")).AddMonths(1).AddDays(-1).ToString("MMMM d, yyyy")

Will give you January 31, 2019 (relative to date today)

Note that you have to convert the first day of the month back to DateTime in order for the AddDays and AddMonths method to work.

Array/Collections

How to get all files in a folder

Use Directory.GetFiles method

ie. Directory.GetFiles("C:\Data\UIPath","*.*",SearchOption.AllDirectories) 

returns a String array of all the files inside the UIPath\ folder, including its subfolders, that matches the search pattern indicated in the 2nd argument.

"*.*" basically takes all types of files. "*.pdf " will get only files with the .pdf extension.

SearchOption.AllDirectories searches even the subdirectories under UIpath\

Because this method returns a String array, use a For loop to run through the collection.

For more info:

https://docs.microsoft.com/en-us/dotnet/api/system.io.directory.getfiles?view=netframework-4.7.2#System_IO_Directory_GetFiles_System_String_

Share:

Read More