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.
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!
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
But I am grateful for your support to shed some light as i am struggling on my own! thank you.
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
How it works:
I generate a list of files in a folder:
(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)
Then use For Each loop to loop through all strings in that array
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.
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.
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:
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.
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:
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 )
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.
sharazkm32
ashokkarale
V_Roboto_V
Anil_G
adilhassanpost
Hosam_Alzahrani
gorby
Sreekumar_Sedharth
adi.mehare
stary