Posts by EllieN

Thanks, but I have no clue what is wrong with the path. I copied it off the Explorer bar. Is my reference to the field incorrect?

Thanks, royUK, for the quick response.

The "Const" line has three constants, including wb$ and rng$.

I am trying to get a value from a closed workbook in Excel VBA on a Windows 10/64 PC with Office 365.

The data is in a single cell named "TestText".

Whenever I run the function below, instead of populating "getText$", Excel opens a file dialog box with the title

"Update Values: Data[Test2.xlsx]Sheet1". The dialog box is open to the My Documents folder.

What am I doing wrong, please?

Thank you for your help.

Function getText$()

Dim arg$

Const closedpath$ = "C:\Users\Ellie\Documents\Spreadsheets\Data", wb$ = "Test2.xlsx", rng$ = "TestText"

arg$ = "'" & closedpath$ & "[" & wb$ & "]Sheet1'!" & rng$


getText$ = ExecuteExcel4Macro(arg$)

End Function