How to pass variables dynamically in connection string

Help IT Automation
1

Hi im using excel as automation and used OleDB connection and the below is my connection string

“Provider=Microsoft.ACE.OLEDB.12.0; Data Source=“C:\Users\Muthulakshmi\Downloads\Requester_1.xlsx”; Extended Properties=‘Excel 12.0 XML;HDR=YES;ReadOnly=False’”

In the above i have passed the data source as the Excel file path but i want to store the excel path in a variable and then use the variable in the connection string instead of the actual path, but i throws error as the Connection String is a String i cannot pass variables into that. Any Ideas please

1 Like
2

Not sure I fully understand your issue. But try putting the connection string into a text file with a key for where the variable is like XXVARXX. Then read the text file and save as variable, then use string replace to replace the key with your variable.

3

Hi I’ll explain it more clearly : see the below in is the connection string

“Provider=Microsoft.ACE.OLEDB.12.0; Data Source=“C:\Users\Muthulakshmi\Downloads\Requester_1.xlsx”; Extended Properties=‘Excel 12.0 XML;HDR=YES;ReadOnly=False’”

But I want to use it like

Assign ExcelPathVariable = “C:\Users\Muthulakshmi\Downloads\Requester_1.xlsx”

And then I want
The connection string be like the below:-

“Provider=Microsoft.ACE.OLEDB.12.0; Data Source=ExcelPathVariable; Extended Properties=‘Excel 12.0 XML;HDR=YES;ReadOnly=False’”

4

put the script in a text file
“Provider=Microsoft.ACE.OLEDB.12.0; Data Source=“XXVARXX”; Extended Properties=‘Excel 12.0 XML;HDR=YES;ReadOnly=False’”

Read the text file and store as variable.

Replace string activity XXVARXX with Your path.

The result is a variable with the entire connection string.

That is what I am explaining-not sure it works in your use case. I have used this method when I do not want to worry about double quotes or other switch characters.

Make sure you are using the correct double quotes and not the up and down ones. " " not “”

5

@Muthulakshmi_Thangamuthu

You can use like this.
string connection=“C:\Users\Muthulakshmi\Downloads\Requester_1.xlsx”
“Provider=Microsoft.ACE.OLEDB.12.0; Data Source='”+connection+“'+Extended Properties=Excel 12.0 XML;HDR=YES;ReadOnly=False”

Regards
Anand

1 Like
6

Thanks for ur time …ill try :slight_smile:

7

Thanks this one works :slight_smile:

8

:+1:t2:

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.