topic OLEDB SQL query Help-Dynamic Worksheet name in Product Forum

OLEDB SQL query Help-Dynamic Worksheet name

faheemsd — Thu, 30 Mar 2023 07:59:00 GMT

Hello,

I'm trying to get the data using the query from the OLEDB

"select * from [Sheet1$]"

but I have the worksheet Name as Dynamic as it changes everyday. This is my worksheet name Prepaid 9220 and it's changes everyday

Could you please help with the SQl query to get the data with the Dynamic worksheet ?

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------

RE: OLEDB SQL query Help-Dynamic Worksheet name

RamónRequena_L1 — Thu, 30 Mar 2023 12:55:00 GMT

If there is a logic behind the worksheet name, store the query in a text Data Item and the Worksheet name on another Data Item. Add some logic to set the name of the worksheet via a calculation stage. Add another calculation stage after that to amend the OLEDB query accordingly.

Another option would be to open the excel file and use the Action 'Get Worksheet Name' , assuming the target worksheet is always placed on the same position. Store the output of said action on a Data Item (e.g. 's_WorksheetName') and then close both workbook and excel instance (technically you only need to close the WB, but just to be on the safe side).

Then, build the query via calculation stage like this "SELECT * FROM ["& [s_WorksheetName] &"$]"

Hope this helps.

------------------------------
Ramón Requena López
RPA Developer
Magenta Telekom
------------------------------

RE: OLEDB SQL query Help-Dynamic Worksheet name

faheemsd — Thu, 30 Mar 2023 13:02:00 GMT

Hi Ramón Requena López,

Thanks for your response.

I have applied the above mentioned 2nd method and it's working fine for me.

Thank you.

------------------------------
SYED FAHEEM
RPA Developer
DAR-ME
Asia/Kolkata
------------------------------