Page 1 of 1

Excel Object Error

Posted: Wed Jun 26, 2019 6:16 pm
by Red2
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.

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"
		lcValue := alltrim( lcValue )
		aXLSX_Array[ i, lnHit ] := lcValue
	next // j
next // i

oUsedSheet := nil
oExcel := nil
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.

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.


Re: Excel Object Error

Posted: Wed Jun 26, 2019 7:32 pm
by KDJ

Try without parentheses at the end:

Code: Select all

		lcValue := oUsedSheet:Cells:Item( i,j ):Value//()  //// <<======  Error line

Re: Excel Object Error

Posted: Wed Jun 26, 2019 7:41 pm
by jairpinho
hello try this
lcValue := oUsedSheet:Cells( i,j ):Value or
lcValue := oUsedSheet:Range("A"+i ):Value

Re: Excel Object Error

Posted: Wed Jun 26, 2019 7:56 pm
by Red2
Thank you KDJ,

I appreciate your quick response. I tried that line both "with" and "without" the ending "()". I ran each about 12 to 15 times.

Unfortunately, both results were quite similar. Of this small sample I did not see a clear difference between "with" and "without".

I am still quite new to HMG. I assume that there may be either 1) a work-around or 2) a better way to code accessing Excel files.

Question: Do you have any further guidance? I would very much like to hear any thoughts that you have.

Thanks again for your kind help!


Re: Excel Object Error

Posted: Wed Jun 26, 2019 8:23 pm
by Red2
Hi jairpinho,

Thank you so much for your expert guidance. With your suggested change ---
from: oUsedSheet:Cells:Item( i,j ):Value()
to: oUsedSheet:Cells( i,j ):Value()
it successfully ran 50 iterations. My previous line's code never succeeded more than 6 or 8 iterations.

I think that you have solved what I could not and am extremely grateful for your kind help.

There will be more testing since there are other features to add. I will let you know if there is anything further to report but it is looking good right now.

Thank you once again!


Re: Excel Object Error

Posted: Wed Jun 26, 2019 9:52 pm
by Red2
Hi All,

One additional "for whatever it is worth" observation. I took the now successful HMG code and, with the necessary syntactical changes, ran (virtually) the same code in Visual FoxPro 9 (Sp 2).

The results rather surprised me.
1) Running the VFP code would only evaluate the line in question, lcValue := oUsedSheet:Cells( i,j ):Value(), to a NULL (NIL in HMG).
2) If I manually stepped through the code 1 line at a time inside VFP's debugger it correctly produced the Excel cell's value.
3) However inside the VFP debugger if I then let it "run" (like HMG's debugger's Animate) then that line only produced NULLs (NILs).

I don't know quite what to make of this except that in this instance HMG was more successful than VFP. That's not taking anything away from VFP. Unfortunately VFP's development ended a long time ago. So, kudos here go to HMG.