Reading from an Excel Named Range into a string variable

[GideonMitchell]

I think I have a very simple problem here but can't get the syntax right.

I have an Excel workbook that contains several single-cell Named Ranges. I'd like to read the values of the cells into output variables in Power Automate Desktop. In the first example, I use a Read from Excel worksheet action to retrieve the values of the named cells in the Name TmpInvoiceNumber into the Power Automate output variable InvoiceNumber.

Because named ranges can refer to more than one cell, InvoiceNumber is a DataTable. What I really want is just the single value that was in the Named cell, so I then use a Set variable action to attempt to extract the top left cell from the DataTable into a new output variable InvoiceNumberValue.

In the Value box of the action I have used the formula

=InvoiceNumber[0][0]

This doesn't work. It tells me that there are unexpected characters (i.e. a syntax error). What am I doing wrong?

Thanks in advance.

 

 

 

[GideonMitchell]

Just to clarify, the Excel Name TmpInvoiceNumber just refers to a single cell.

[VishnuReddy1997]

Hi @GideonMitchell ,

Firstly you building the flow in Power Fx Enabled Flow. Power FX is still in Preview Stage.I would sugest not use it in Production.

Please Disable the Power Fx as shown in below image.

For use issue i have developed the code in Power Fx disabled flow. Please find the solution.

Note: I don't have exact excel file, I have just tried to replicate.

Code:

Please copy the below code into your flow and make necessary changes.

FUNCTION 'Excel Data into list' GLOBAL
    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Downloads\\Sample_Template (2).xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
    Excel.ReadFromExcel.ReadNamedCell Instance: ExcelInstance CellName: $'''TmpInvoiceNumber''' ReadAsText: False RangeValue=> InvoiceNumber
    Variables.RetrieveDataTableColumnIntoList DataTable: InvoiceNumber ColumnNameOrIndex: 0 ColumnAsList=> InvoiceNumber
    SET InvoiceNumberValue TO InvoiceNumber[0]
END FUNCTION

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[GideonMitchell · Accepted solution]

Thanks @VishnuReddy1997 

There are a couple of things you have suggested. I applied the first one, disabling Power Fx mode. I then found a possibly simpler solution that seems to work and does not require your second suggestion of creating my own function.

In my new flow the first action is as before, except that I am using a Flow variable ExcelData (rather than an Output variable) to contain the value read from the Excel Name as a Data Table.

I follow that with a Set variable action, just as before, but the formula now seems to work, perhaps because it's using a flow variable rather than an Output variable.

Thanks a lot for your help. I will give you kudos but I'm not marking your response as the solution, as my solution is different and a little simpler.