Answered

Reading from an Excel Named Range into a string variable

(0) Share

Report

Posted on by 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.

Screenshot 2024-04-18 11.50.13.png

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?

Screenshot 2024-04-18 11.49.04.png

Thanks in advance.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (3)

Answers (1)

Sort by

GideonMitchell 12 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
VishnuReddy1997 2,666 Super User 2026 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
Verified answer

GideonMitchell 12 on at

Like
a
(0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No