Excel Object Error

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

Post Reply
Red2
Posts: 27
Joined: Sat May 18, 2019 2:11 pm
DBs Used: DBF, FoxPro, Visual FoxPro
Location: United States (East Coast)
Has thanked: 5 times
Been thanked: 1 time

Excel Object Error

Post by Red2 » 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.

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: 223
Joined: Mon Sep 05, 2016 3:04 am
Location: Poland
Been thanked: 262 times

Post by KDJ » Wed Jun 26, 2019 7:32 pm

Red2

Try without parentheses at the end:

Code: Select all

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

User avatar
jairpinho
Posts: 340
Joined: Mon Jul 18, 2011 5:36 pm
Location: Rio Grande do Sul - Brasil
Has thanked: 3 times
Been thanked: 11 times
Contact:

Post by jairpinho » Wed Jun 26, 2019 7:41 pm

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: 27
Joined: Sat May 18, 2019 2:11 pm
DBs Used: DBF, FoxPro, Visual FoxPro
Location: United States (East Coast)
Has thanked: 5 times
Been thanked: 1 time

Post by Red2 » Wed Jun 26, 2019 7:56 pm

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: 27
Joined: Sat May 18, 2019 2:11 pm
DBs Used: DBF, FoxPro, Visual FoxPro
Location: United States (East Coast)
Has thanked: 5 times
Been thanked: 1 time

Post by Red2 » Wed Jun 26, 2019 8:23 pm

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: 27
Joined: Sat May 18, 2019 2:11 pm
DBs Used: DBF, FoxPro, Visual FoxPro
Location: United States (East Coast)
Has thanked: 5 times
Been thanked: 1 time

Post by Red2 » Wed Jun 26, 2019 9:52 pm

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