ODBC to Excel worksheet and formula updates

[Phil_Kelsey]

I use ODBC to update via dml a row in a worksheet. It works great until I try to update a cell that contains a formula at which time it throughs an error saying the cell is not updatable.

for example, if the value in “LinkTo” column for this row in the worksheet is currently a formula of
=HYPERLINK(‘https://OldURL.com’,‘Click Here’)

either of the below in “Run Query” activity will fail

“UPDATE [Sales$]
Set
LinkTo = ‘’
Where rownumber = ?”

“UPDATE [Sales$]
Set
LinkTo = ‘=HYPERLINK(‘https://NewURL.com’,‘Click Here’)’
Where rownumber = ?”

where rownumber is a pk i added prior.

I could use subsequent VBA or add an excel scope and range, but the seems to defeat the purpose of using ODBC.

Does anyone have any ideas how to best solve this?

[Anil_G (Anil Gorthi)]

@Phil_Kelsey

Can you please shwo the detailed error

Also did you try with updating any single value like A

If its happening for any cel then try this

Excel as Database : Execute Non-Query : Update statement

@Sarath In Connection string Change “IMEX=1” value from 1 to 0 or -1, It will work. if IMEX value is 1 can only do read operation on data in excel. if IMEX value is 0 or -1 u can do read, write and update operation on data in excel.

Cheers

[Phil_Kelsey]

the connection string contains readonly=0

“Dsn=Excel Files;dbq=C:.…res.xlsm;defaultdir=C:.…\rstate;driverid=1046;maxbuffersize=2048;pagetimeout=5;readonly=0”

I do not see IMEX. But I am able to update cells that are not formulas

23.6.1-beta.13324+Branch.release-v23.6.1.Sha.52787b786620e97cba5555379da6e6bb423b3e6b

Source: Main

Message: ERROR [23000] [Microsoft][ODBC Excel Driver] Cannot update ‘(expression)’; field not updateable.

Exception Type: System.Data.Odbc.OdbcException

System.Data.Odbc.OdbcException: ERROR [23000] [Microsoft][ODBC Excel Driver] Cannot update ‘(expression)’; field not updateable. at UiPath.Database.Activities.ExecuteQuery.HandleException(Exception ex, Boolean continueOnError)
at UiPath.Database.Activities.ExecuteQuery.ExecuteAsync(AsyncCodeActivityContext context, CancellationToken cancellationToken)
at UiPath.Database.Activities.AsyncTaskCodeActivity.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

[Phil_Kelsey]

the solution for me was to use an encapsulating Excel Scope and File and post directly to the cell. So i use the the Update dml via ODBC to loop the large Excel dataset and make most of the updates. In that loop I save the row number from the worksheet. To do this I have a prior VBA script that includes the posting of a =row() to a column called rownumber. So the initial sql select statement gives me the row number of the current row of the cursor. With that I use the cell update activity to update that specific cell (row letter, column #). I also use the row number as the pk in the update statement.
Another challenge I am working on is getting the column letter from the worksheet as it may change. I find inside loops and if statements freeze in UiPath if the worksheet is large so at the onset of the entire workflow I initiate a python script that reads the header row and catalogs the column letters. I used Python as I had trouble getting UiPath after extracting a range consisting of just the header row to iterate that row horizontally.