Automate excel data refresh

Hi,

I am new to UiPath and is trying to create a bot for excel data refresh. Currently it is refreshed via VBA script. I am trying to convert it to Uipath. However i have tried and unable to find any results from my refresh in google.

Hence I wonder, is it possible to do excel data refresh via Uipath as I am unable to find any useful information.

The excel is stored in a shared drive and i unable to find useful information on how to instruct the bot to go to the location. I included the direct link to the network drive but it is throwing error.

Anyone can shared some light on this topic?

Note: i tried to upload the uipath file i have however being new i cant

Thank you in advance for your kind support and guidance.

Rgds
HY

Hi @hyghan

You could simple use Invoke VBA activity to run your ready script on your Excel file using Excel Scope activity :slight_smile:

Wouldn’t it be the best solution?

Hi @loginerror,

Thank you for your reply and sincerely appreciate that. I am trying to search how to use the Invoke VBA activities. Hope to share some good news on my success!

Thank you!:grinning:

Rgds
HY

Try this sample, should work :slight_smile: (developed on 2018.4 beta, so might not work on pre-2018.3 versions).
InvokingVBA.zip (2.3 KB)

To run this you will have to allow executing all macros in Excel settings (otherwise it will throw an exception).

to invoke VBA it needs to be in the excel application scope. but the first line is to include a path to the excel. For my case is, there are a few excel in a folder that is in a shared drive. I was doing some research and it seems that to begin need to assign the folder path and I am exploring how to instruct it to open different excel. The VBA script is written to open all the excel file and refresh them and close them.

VBA Script:
Set fso = CreateObject(“Scripting.FileSystemObject”)
Set xl = CreateObject(“Excel.Application”)
xl.Visible = True

For Each f In fso.GetFolder("\bredsntp010\uk_cp_customsdutymanagement\05 Service Excellence and Operations\04 Performance Management Dashboard\Customs Duty Management - Qlik\Customs Duty Management\10_Source_Data").Files
If LCase(fso.GetExtensionName(f.Name)) = “xlsx” Then
Set wb = xl.Workbooks.Open(f.Path)
wb.RefreshAll
WScript.Sleep 10000
wb.Save
wb.Close
End If
Next
xl.Quit

thank you for sharing the workflow but I need to digest how it works because I am an infant in UiPath. Just using google and self learn to start and alot of things are not making sense as I am not technical :disappointed_relieved:

But I am grateful for your support to shed some light as i am struggling on my own! thank you.

From what I see here, some of your script is almost equal to UiPath’s Excel Application Scope :slight_smile:

In fact, see this new example :smiley:
InvokingVBA.zip (19.5 KB)

It solves your problem, but I hope it can on top of that show you how easy it is to solve this types of issues with UiPath :slight_smile:

How it works:

  1. I generate a list of files in a folder:
    image
    (The Directory.GetFiles("ExcelFilesToBeRefreshed") should be self-explainatory. It returns an array of strings, each string being a single path to a file in that folder)
  2. Then use For Each loop to loop through all strings in that array
  3. For each string/path to a file, open that file and execute the vba code. And if you check the VBA code, you will see that all there is is:
    Sub RefreshAll ()
    Application.ActiveWorkbook.RefreshAll
    End Sub

Thank you for your kind support and generosity in knowledge sharing and detailed guidance!

I am slowly following through and trying to create my workflow. I shall have more questions along the way - especially if it is not working for me.

Ya, from my search and see works that others do, it is so easy, but it is hard for me to start fresh. Thank you for providing some light and guidance for me to begin with. i will keep you posted. :smiling_face_with_three_hearts:

when i assign the list of files, do i put the folder name of where my excel file to replace “ExcelFilesToBeRefreshed”?

i update mine to be Directory.GetFiles(“10_Source_Data”), but there is an error as below.
Compile error(s) encountered processing expression “Directory.Get.Files(“10_Source_Data”)”. Value of type 1-dimensional array of String cannot be converted to ‘String’

Also for the listOfFiles variable, I am unable to select the System.String variable type. Eventhough i select it, but it only appear as String not String.

Refresh Excel_InvokeVBA.xaml (8.0 KB)

The path can either be full, starting from the drive letter:
C:\etc\etc\yourfolder
or relative, with the root folder being the one where your Main.xaml file is stored.
So if your folder 10_Source_Data is placed inside the folder with Main.xaml file, your good to go.

As to the variable type, the Directory.GetFiles method returns an Array of String, which should look like that:
String []
You can select it like that:
image
image

great! thank you for that!

my workflow didnt work with error “Programmatic access to Visual Basic Project is not trusted” :sob:

That is because you will need to allow those in the settings:

Hmm, I already did that. I double check again, the option is checked.

Can I kindly ask, if you have time, can you take a quick look and let me know what i’ve missed? especially the Invoke VBA activities, i put the path with the vb file name “\…\10_Source_Data_Dash_BOT(QLIKVIEW)”. then the second line, what do i put there? just “Main” by default or the name of the vb file? i have tried with “Main” and “_Dash_BOT(QLIKVIEW)” but it returns the same error.

thank you in advance!

Refresh Excel_InvokeVBA.xaml (8.4 KB)

The “Main” is the name of the Sub or Function from the .vb file. In my example, I put there RefreshAll, because that’s how I called my Sub:
image

As to the file name, it should be a full one, including the extension.

Also, make sure that you are only running this 1-line script from above. Your previous script is literally first getting the list of files, then opening each one and then doing the RefreshAll part. This is now all done in UiPath, so you only need to execute the RefreshAll part.
(well, I’ve just spotted that your old script is also doing a check for the file extension, but we will figure it out later on when we get this basic functionality to work :slight_smile: )

ok thanks! i have update the script and only run your VBA script. However it is still with the same error :confounded:

Refresh Excel_InvokeVBA.xaml (8.4 KB)

Note: it is my end of day, i will check later or try again tmr :thinking:
@loginerror, thank you for your support and help!

hi, any update on this?

previous post mention there is error.

hi @makaur, it still didnt work. I am still figuring out what is wrong. I had another more straight forward script that do exactly step by step as per what a user will do when refreshing excel file, which is not ideal but does the job as of now.

Hello,

it is bug use “'your makro adress” maybe it will work.