Excel Object Error

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

Post Reply
Red2
Posts: 271
Joined: Sat May 18, 2019 2:11 pm
DBs Used: Visual FoxPro, FoxPro
Location: United States of America

Excel Object Error

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

oExcel:ActiveWorkBook:Close()
oExcel:Application:Quit()
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.

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
KDJ
Posts: 243
Joined: Mon Sep 05, 2016 3:04 am
Location: Poland

Re: Excel Object Error

Post by KDJ »

Red2

Try without parentheses at the end:

Code: Select all

		lcValue := oUsedSheet:Cells:Item( i,j ):Value//()  //// <<======  Error line
User avatar
jairpinho
Posts: 420
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Contact:

Re: Excel Object Error

Post by jairpinho »

hello try this
lcValue := oUsedSheet:Cells( i,j ):Value or
lcValue := oUsedSheet:Range("A"+i ):Value
Jair Pinho
HMG ALTA REVOLUÇÃO xBASE
HMG xBASE REVOLUTION HIGH
http://www.hmgforum.com.br
Red2
Posts: 271
Joined: Sat May 18, 2019 2:11 pm
DBs Used: Visual FoxPro, FoxPro
Location: United States of America

Re: Excel Object Error

Post 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!

Red2
Red2
Posts: 271
Joined: Sat May 18, 2019 2:11 pm
DBs Used: Visual FoxPro, FoxPro
Location: United States of America

Re: Excel Object Error

Post 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!

Red2
Red2
Posts: 271
Joined: Sat May 18, 2019 2:11 pm
DBs Used: Visual FoxPro, FoxPro
Location: United States of America

Re: Excel Object Error

Post 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.

Red2
Post Reply