Excel OLE - List Named Ranges

Keywords:   Excel OLE - List Named Ranges 

---

Question:

I am trying to list all of the named ranges in an Excel Workbook using OLE.

Here's my code...

Decimals(0)

filename = AskFileName("Please select file to convert","h:\business technology\","*.xls|*.xls","",1)

objExcel=ObjectOpen("Excel.Application")

; DisplayAlerts Property - The default value is True. Set this property to False if you don’t want to be disturbed by prompts 
; and alert messages while a macro is running; any time a message requires a response, Microsoft Excel chooses the default response.
objExcel.DisplayAlerts = @FALSE

; ScreenUpdating Property -Turn screen updating off to speed up your macro code. You won't be able to see what the macro is doing, 
; but it will run faster.
objExcel.ScreenUpdating = @FALSE

; AskToUpdateLinks Property - @True if Microsoft Excel asks the user to update links when opening files with links. 
; @False if links are automatically updated with no dialog box. 
objExcel.AskToUpdateLinks = @FALSE

objWB=objExcel.Workbooks
objWB.Open(filename)

objWorkbook=objExcel.ActiveWorkbook
objNamesCollection=objWorkbook.Names
NamesCount=objNamesCollection.count
For i = 1 to NamesCount
NameValue=objNamesCollection(i).name

Next
ETc...

NamesCount returns 8

When I execute the statement NameValue=objNamesCollection(i).Name I get the error message "Expression continues past expected end..."

Can someone tell me what is wrong with this statement? Also, is there any way to trap OLE errors so that the program can handle the error appropriately?

Answer:

This may help you out - I'm not sure if it will include 'linked' ranges, or hidden ranges, but easy enough to test.

; enumerate Range Names -
; stan littlefield, February 4, 2003
cXLS = AskFileName("Select Excel WorkBook",".\","Excel Files|*.xls|","*.xls",1)
BoxOpen(cXLS,"Enumerating Range Names..")
DB             = ObjectOpen("Excel.Application")
DB.Visible     = @FALSE
DB.UserControl = @FALSE
oAPP = DB.Workbooks
oAPP.Open(cXLS)
oACT = DB.ActiveWorkbook
cNames =""
oNames = oACT.Names
n = oNames.Count
If ! n>0 Then Goto end

hEnum = ObjectCollectionOpen(oNames)
While 1
   oName = ObjectCollectionNext(hEnum)
   If oName == 0 Then Break
   cNames = StrCat( cNames,oName.Name," ",oName.RefersTo,@TAB )
   ObjectClose(oName)
EndWhile
ObjectCollectionClose(hEnum)
ObjectClose(oNames)

:end
ObjectClose(oACT)
oAPP.Close()
ObjectClose(oAPP)
DB.Quit()
ObjectClose(DB)
BoxShut()
cName = AskItemList( cXLS,cNames,@TAB,@SORTED,@SINGLE)
Exit