How to pass variables dynamically in connection string

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

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.

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’”

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 “”

@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

Thanks for ur time …ill try :slight_smile:

Thanks this one works :slight_smile:

:+1:t2:

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