Bob - would you be kind enough to offer up a suggestion? Create a form with an editline, "EXCEL_FILENAME", two buttons, one for each of the procedures below and something to display the results. OI 8.0.3, XP Pro and Excel 2003. Would the Excel ODBC Driver be the way to go? Would be nice to get this working….

This opens an excel spreadsheet:

Function Excel_Open(ExcelFileName)

Common /Excel_Ole/ objExcel, objWorkbooks, objSheet

If Assigned(objExcel) Else objExcel='

If Assigned(objWorkbooks) Else objWorkbooks='

If Assigned(objWorkbook) Else objWorkbook='

if dir(ExcelFileName) then

• Load sheets into memory

objExcel=OleCreateInstance('Excel.Application')

objWorkBooks=OleGetProperty(objExcel, 'WorkBooks')

if Not(OleStatus()) then

 	objSheet=OleCallMethod(objWorkBooks, 'Open', ExcelFileName)

End

objExcel-]Visible=1

End

Return 0

This gets the col-hdr (First Row)

Function Excel_Get_Col_Hdr()

* Returns First Row in a spreadsheet

Common /Excel_Ole/ objExcel, objWorkbooks, objSheet

Result = "Row"

For c=1 to 19

Range	= "$":Char(64+C):"$1"

BF_Range= OleGetProperty(objExcel, 'Range', Range)

BF_Value= OleGetProperty(BF_Range, 'Value')

Result=BF_Value

Next c

Return Result

*

I call these functions from a button's click event. "Open Spreadsheet" and "Get Tag". I select a filename, click on

Open Spreadsheet and the spreadsheet comes up. I click on "Get Tag" and an array is populated nicely with the data from

row 1 on the sheet. The PROBLEM is that if you alter the spreadsheet after it is open and delete the data from a cell,

Excel_Get_Col_Hdr fails. While debugging you can no longer see "" for "BF_Range" - just null.

I tried to reproduce the Error, could not.

Is is possible that you are trying to access a column ] 'Z', suc as AA or AB? The calc of the column name is not correct for ] 26 columns.

Or, if you close, open the workbook, then the common variable will not be oorrect any more, you will see behavior like you describe.

For something over Z you can always call a function to bring back the characters as in bellow.

compile function [/color]mc_char(CHARSTR Value) 
[/color]declare function char 
[/color] 
[/color]retval=[/color]'' 
[/color] 
 
[/color]if [/color]value gt [/color]26 [/color]then 
    [/color]vValue=int(value/[/color]26[/color]) 
    tValue=value - vValue*[/color]26 
    [/color]retval=[/color]char[/color]([/color]64 [/color]+ vValue):[/color]char[/color]([/color]64 [/color]+ tValue) 
    [/color]*if tValue eq 1 then debug 
[/color]end else 
    [/color]retval=[/color]char[/color]([/color]64 [/color]+ value) 
[/color]end 
[/color] 
return [/color]retval[/color][/color][/size]         
               

I use this, I had to use +0 otherwise the commands did not work.

oStartpos = OleGetProperty(oWorkSheet, "Cells", startrow+0, startcol+0)

oEndpos = OleGetProperty(oWorkSheet, "Cells", endrow+0, endcol+0) ;* row,column

oRange = OleGetProperty(oWorkSheet, "RANGE", oStartpos, oEndpos)

Hank - would you be kind enough to try the following:

1. Open the spreadsheet with OLE

2. Make sure your code works.

3. Go the spreadsheet and delete a cell

4. See if your code still works.

I tried using your method but it failed at number 4. If yours works, please let me have a little more detail as to how you put everything together….

View this thread on the Works forum...