& ldquo; The name you entered is not valid & rdquo; error with Excel


I'm trying to run a simple VBScript example (not an Excel macro) and have run into the The name that you entered is not valid issue.

The name has an underscore, no spaces, and the workbook is brand new (the Name Manager shows nothing). Also, although most web examples show := that is a syntax error for me.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\test.xlsx")

'This line fails
objExcel.ActiveWorkbook.Names.Add Name="_myRange", RefersTo="Sheet1!$A$1:$D$3"

WScript.Echo "Finished."

Any ideas?

The Name:="_myRange"syntax is the named arguments syntax. As you observed right, you cannot use this with VBScript. But then you cannot simply change it to Name="_myRange". Name="_myRange" will result in FALSE and so you are trying to use FALSE as a name. Thats why the error.

You must put the arguments in their right order without naming them.

objExcel.ActiveWorkbook.Names.Add "_myRange", "=Sheet1!$A$1:$D$3"

Oh and the reference must begin with =.