UPDATE excel com documento fechado

[Anonymous]

Estou tendo problemas ao dar UPDATE em um documento excel, ele passa pelo comando, entende que esta com status alterado e vai para o proximo caso, mas quando eu paro a automação e olho no documento, ele esta com status inicial.

Quando roda com o documento excel aberto, ele consegue dar UPDATE  normalmente.

[Kaif_Siddique]

Use Bing translator and share query again. 

[VJR]

Translation:

UPDATE excel with closed document

I am having problems when UPDATE in an excel document, it goes through the command, understands that the status is changed and goes to the next case, but when I stop the automation and look at the document, it is with initial status.

When it runs with the excel document open, it can UPDATE normally.

[VJR]

Are you saving and closing the Excel instance in the end?

Please share screenshots of the flow and if you can mention the translated actions that would be great.

[Anonymous]

This robot was migrated from Winautomation to power, it worked correctly, but after the migration, it stopped working.

With the document open it does UPDATE, but closed it does not, this prevents the robo from working where it left off if it is necessary to stop.

Flowopen SQL connectionrun SQL statement

[VJR]

Could you share what you meant by Excel Open? 

How did you keep the Excel open and run the query?

[Anonymous]

I kept excel open and followed run the SQL command

[Anonymous]

before opening connection to excel I opened the document to run with it open.

[ThomasFonn]

Not sure if this is helpful or even make a difference, but the Connection String site provides some insights into the connection string

The file format your are using is a .xls rather than .xlsx which may lead to some inconsistencies when using Extended Properties="Excel 12.0" rather than Extended Properties="Excel 8.0"

You could try to change the connection string, OR try "Save As" on the file and change the format of the file to a newer file format to see if it makes a difference.

[Anonymous]

thank you very much, I will test and inform the result.

[Anonymous]

Winautomation had difficulties with the documents .xlsx, now with the migration to power automate, I do not think i have this problem.

I changed the document .xlsx, but still the same problem, what Extended Properties do I use for Office 365?

before that update has another one that works normally, but the one is still having problems.

[Anonymous]

Image correction "%CodCorretorSeparate% is a variable, I sent the wrong image above, but continues the error even correcting this detail, if the process run all of it acted as if it were giving update normally, but if the robo to and forth, the statuses are not saved and it starts from the beginning again.

run SQL statement

[Kaif_Siddique]

@VJR True Super User

[Kaif_Siddique]

Hi @Anonymous 

I tested the flow and working fine for me.

Is Excel file open as ReadOnly ? could you please check it.

Sample Flow:

Connection String:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=<<File Path>>;Extended Properties="Excel 12.0 Xml;HDR=YES";

Want to learn how to Run SQL queries on Excel files. Refer below link

https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel

Let me know if any queries.

Regards

Kaif

[Anonymous]

I found that when the Power Process is terminated by some error or when terminated, Power does all update at the end or after the error.

But when I click for the robo to stop rotating, it doesn't save the UPDATE.

That is, once started can only be terminated by error or terminated.