How to pass credentials from UIPath into an excel macro to log in to a database

#1

Hi,

I am attempting to pass credentials saved in UI Path from an in-string argument into a globally declared variable within excel’s VBA to log into a database as the excel macro is executed to pull data. I can’t figure out how to pass the credential variables for in_str_EssbaseUsername and in_sstr_EssbasePassword through an Execute Macro or Invoke VBA activity to be placed within the global variables MyID and MYPass so that the code will log-in automatically.

Thanks for your help. I attached photos to help with context.

Essbase%20Pull%20Macro
Essbase Pull Macro.PNG1048×890 27.3 KB
image

#2

Hello.

You might think about using the UiPath.Database.Activities
image

Also, depending on the scale of your project, you may want to encrypt that password. The ConnectionString requires a password, so maybe you can decrypt it directly in the ConnectionString creation.

Anyway, if you still want to use a VB script/macro, you can do that in one of two easy ways.

  1. create a .vbscript file using a script editor, which may have syntax differences than vba. You can then use the .vbs file on the “Invoke VBA” inside an Excel Scope. - since it is inside the Excel scope, it will attach to the file as the activeworkbook. First test the script externally though.
  2. Place the code in the excel file as a macro-enabled file. Then, use Execute Macro.

both ways will require that you arguments in the top of the function.
For .vbs, it will be like Sub Retrieve_Essbase_USD(user, pass)
and for vba, I think it’s like Sub Retrieve_Essbase_USD(user As String, pass As String) //or SecureString

My vb is rusty sometimes, so you can look up for syntax on these languages if you still have trouble.

for getting it working in UiPath, post what you are trying, and maybe someone can help identify the solution.

Regards.

#3

Thanks for the response. I will try to mess with the database activities tomorrow but I’m not sure they would work with a proprietary/customized database.

What I want to attempt is to use invoke code to pass the credentials from an in argument into global variables by writing in the code so that it is inserted into the macro such that

MyID = “Username”
MyPass = “Password”

from variables used within UI Path. Then once the macro has been ran it will delete the code. I’d like to change the excel model as little as possible so that we can pull it from the bot if necessary and run it manually. Also, the model is very structured already so creating the automation should be simple aside from this login portion.

I’ll play with it more tomorrow and get some screenshots of my process so that it’s more clear.

Thanks again!