Excel Object Error
Posted: Wed Jun 26, 2019 6:16 pm
Hi Everyone,
I would really appreciate help and guidance on a problem that I simply cannot solve.
Very inconsistent results happen when reading Excel cell values. Sometimes my code runs perfectly and other times (about once out of roughly 4 or 5 times) it errors on random rows and columns. The error always occurs on the line,
lcValue := oUsedSheet:Cells:Item( i,j ):Value()
Nevertheless the error always happens for different row and column values. (Including or excluding "do events" does not seem to matter).
Below is a code excerpt.
The error is always "Error BASE/1004 No exported method: VALUE".
The error message always shows this line's number.
When the error line's HMG code is then pasted into the debugger's "Evaluate", the cell's text is correctly displayed.
The debugger shows row (i) and column (j) values are completely valid but they randomly different each time.
I have spent many long hours going over and over this problem but I am out of ideas.
Questions:
1) How can my HMG code be improved? I must initially assume that the error is in my code.
2) Is there a creative workaround?
3) Is there any previously known issue of HMG accessing Excel I should know about?
Thank you so much in advance for your kind advice and ideas.
Red2
I would really appreciate help and guidance on a problem that I simply cannot solve.
Very inconsistent results happen when reading Excel cell values. Sometimes my code runs perfectly and other times (about once out of roughly 4 or 5 times) it errors on random rows and columns. The error always occurs on the line,
lcValue := oUsedSheet:Cells:Item( i,j ):Value()
Nevertheless the error always happens for different row and column values. (Including or excluding "do events" does not seem to matter).
Below is a code excerpt.
Code: Select all
oExcel := CreateObject( "Excel.Application" )
oExcel:Workbooks:Open( paFilePathAndName, Origin := xlWindows )
lnSheets := oExcel:Sheets:Count()
oExcel:Sheets(1):Select() // Assume sheet #1 is correct
oUsedSheet := oExcel:ActiveSheet:UsedRange()
pnRows := oUsedSheet:Rows:Count()
pnColumns := oUsedSheet:Columns:Count()
aXLSX_Array := array( pnRows, pnColumns
for i := 1 to pnRows // Each row
//do events
for j := 1 to pnColumns // Each column in row i
//do events
lcValue := oUsedSheet:Cells:Item( i,j ):Value() //// <<====== Error line
do case
case ( valtype( lcValue ) == "C" )
// lcValue := alltrim( lcValue )
case ( valtype( lcValue ) == "N" )
lcValue := alltrim( str( lcValue ) )
case ( lcValue == Nil )
lcValue := ""
case ( empty( lcValue )
lcValue := "N"
endcase
lcValue := alltrim( lcValue )
aXLSX_Array[ i, lnHit ] := lcValue
next // j
next // i
oExcel:ActiveWorkBook:Close()
oExcel:Application:Quit()
oUsedSheet := nil
oExcel := nilThe error message always shows this line's number.
When the error line's HMG code is then pasted into the debugger's "Evaluate", the cell's text is correctly displayed.
The debugger shows row (i) and column (j) values are completely valid but they randomly different each time.
I have spent many long hours going over and over this problem but I am out of ideas.
Questions:
1) How can my HMG code be improved? I must initially assume that the error is in my code.
2) Is there a creative workaround?
3) Is there any previously known issue of HMG accessing Excel I should know about?
Thank you so much in advance for your kind advice and ideas.
Red2