Very often update query is not working when Excel is connected using connection string (oledb)

Help Activities
, , ,
#1

Hi all,

I have created a workflow where it updates a Status column after each steps are completed. Sometimes the update query is working and sometimes it’s not. I have tried using delay and disconnecting the db connection properly. Can anyone please help me to resolve this issue? The only problem is update query is not working consistently. As I mentioned above it’s not working all the time, rather it updates a blank value.

#3

Just to clarify the scenario:

  • You have a data connection in your Excel file that updates data in a sheet
  • When you start it using UiPath the data sometimes updates and sometimes doesn’t?
  • Is the automation doing an steps to trigger a refresh, or your expecting it to happen automatically?
  • Normally Excel doesn’t refresh data connections when the file is opened via Automation, so if you open the Excel file via an automation, and while it’s still open, if you manually click the refresh button, does it work as expected?
#4

Just to clarify the scenario:

  • You have a data connection in your Excel file that updates data in a sheet
    ->Yes, i have added connection string(OLE DB) to connect excel as DB.
  • When you start it using UiPath the data sometimes updates and sometimes doesn’t?
    ->Yes, When i run the workflow separately which contains the update query it works fine. But then when i run the workflow in a sequence with another workflow, it updates the value, but not reflecting in excel file.
  • Is the automation doing an steps to trigger a refresh, or your expecting it to happen automatically?
    ->i haven’t added any activity to do refresh. does it mandatory? if yes, please tell me how can i do that?
  • Normally Excel doesn’t refresh data connections when the file is opened via Automation, so if you open the Excel file via an automation, and while it’s still open, if you manually click the refresh button, does it work as expected?
    -> I haven’t tried the manual test you have mentioned above. do you mean a system refresh button? and one more thing i am not opening the excel file to update the values, everything happens in the background(without opening excel file).
#5

At the moment Excel’s default behavior is to not refresh data connections when opened for automation purposes. The way to force the refresh at this time would be to have the Excel application launch with a visible window, then use UI Automation activities to click the Refresh button for your data connections Refresh an external data connection in Excel - Excel.

We have a “Refresh Data Connections” activity coming in the near future to enable this scenario without the need for UI Automation.

#6

Hi,

I am not able to open an excel file when its already connected using OLE DB connection string(Even i am not able to connect the excel file using connection string after opening it). Then please help me how can i open and refresh the connection. I am getting an exception like “It is already opened exclusively by another user, or you need permission to view its data.”. I searched almost a day and didn’t get any solution. Hope you help me to resolve this issue. Thanks!

#7

Can you share more details on exactly how the connection is created, and all of the exact repro steps you are taking where you see that exception message?