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.

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?

8

Hi Andrew,
I’m also facing a similar issue. When I run the Oledb Update query on excel to update Status column, it is returning the No.Of Affected rows count correctly but, the data is not reflecting in the excel.
But, When I open the excel and touch any cell in and run the query, it is working fine and I can see the updated content in the excel.

9

I suspect the issue here is that when Excel is started by an automation it doesn’t refresh queries. The easiest way to fix this is to upgrade to the newest version of the Excel activities (2.10.4) and leverage the “Refresh Excel Data Connections” activity that was added to the StudioX->Business->Excel activities. If you are in Studio, see this for how to use the activity in Studio.

10

Hi Andrew,
We are not fetching data from another excel or database. We are using Excel itself as database. We are connecting to excel using oledb engine and running queries on excel. Please elaborate How to use Refresh Excel Data Connections activity to reflect the changes in the excel? This activity is expecting WorkBook object as input.

11

Apologies, I think I misunderstood your scenario that you had setup a second Excel file as a Data Source in another Excel file.

To confirm, you are using the Database activities to work against an Excel file?

12

yes Andrew. I’m using database activities.

13

Hello, may I ask if any one got the resolution for this issue. Unfortunately I am facing this issue too. Would be great if you can provide the details here. Thank you in advance!

14

Any solution for this? I am also facing same issue.

I am connecting excel as data base using connection string with “Connect” activity.
Then using “Execute non query” activity to update some column values based on some conditions.

No of Affected records returning correct value. But no update happening in excel.

16

@krishnakanth.k @AndrewHall @vigneshnkv @rakshith.m I am 3 years late to the party here but I am also encountering the same behaviour where the update query only takes affect when the excel file is open.

Did you ever find a solution for this? Thanks

17

Hi,

It’s so weird to mention but this is what I have observed.

When you try to update one or fewer columns in the excel file, then the changes wont be reflected. But when you try to update all the columns in the excel file, then the changes get reflected. By this I mean, lets say you have col1, col2, col3 and col4, then instead of updating just col4, in your Update query, update all the columns but for the rest of the columns you update the same value it holds currently and the desired value for col4. Then the row gets reflected.

As I said, this is a weird solution which I tried few years back. Just check if it can work for you.

Note: It should also be noted that with ODBC drivers its really difficult to manage processes in different VMs/PCs as it works differently on some VMs even though maintaining the same configurations and drivers.