Edit all excel docs in a folder

[Anonymous]

Hi,

I'm trying to edit a few hundred excel docs which contain some incorrect formulas. 

How do i go about using Automate to open each doc and replace the contents of certain cells with this new formula?

I managed to get it working to an extent. It opens a single doc and updates it but I cant figure out how to get it to do so for all other docs.

[kostasc]

Hey @Anonymous 

Use the action "Get files in folder" to grab all items in the folder. Then, with a "For each" loop make the changes in each file that you wish.

[Anonymous]

Thanks. I managed to get it working with my test files.

My next issue is that the actual files I want to edit have certain parts which are protected. 

Obviously, the bits I want to edit are on the protected parts.

I see on the Launch Excel function, under Advanced there is a space for a password. I have filled that in but it still fails to write to the file. 

Since I am pasting a formula, I created a new variable and set its value as the formula.

The error is "Failed to write to excel" and includes the detail below:

Microsoft.Flow.RPA.Desktop.Modules.SDK.ActionException: Failed to write into Excel. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A03EC
--- End of inner exception stack trace ---
at System.RuntimeType.InvokeDispMethod(String name, BindingFlags invokeAttr, Object target, Object[] args, Boolean[] byrefModifiers, Int32 culture, String[] namedParameters)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
at Microsoft.Flow.RPA.Desktop.Modules.Excel.Actions.ExcelActions.SetProperty(Object o, String propertyName, Object value)
at Microsoft.Flow.RPA.Desktop.Modules.Excel.Actions.ExcelActions.WriteToExcel(Variant excelInstance, Variant startCol, Variant startRow, Variant valueToWrite, Int32 writeMode)
--- End of inner exception stack trace ---
at Microsoft.Flow.RPA.Desktop.Modules.Excel.Actions.WriteToExcel.Execute(ActionContext context)
at Microsoft.Flow.RPA.Desktop.Robin.Engine.Execution.ActionRunner.Run(IActionStatement statement, Dictionary`2 inputArguments, Dictionary`2 outputArguments)

[kostasc]

The password under the advanced in the Launch Excel action, is used when an Excel file is encrypted. If a file is protected, it doesn't apply. 

Currently, we don't support this scenario, however you could leverage UI automation to unprotect the file. I will bring your issue up in our next planning to see if we could enhance the action to support your senario.