Speed up excel import automation? 3

[EzLogic]

I have a small form that takes an excel file, opens the file, and traverse thru it and imports it.
VFP 2 / SP2
windows 2012 server 64 bit (my dev machine)
Office 2007 Excel.

It is going really slow. Is there a better/faster way to do this?

ActDate_Cell, Mastername_Cell, etc.. is the cell of the excel coming from a template file.
example: A, B, C, AA, etc..

the looping thru the workbook is slow..

TRY 
	llReturn = .f.
	loExcel = CREATEOBJECT("Excel.Application")
	loExcel.Workbooks.open(lcFile)
	oSheet = loExcel.ActiveSheet
	loExcel.visible = .t.
	llOK = .T.
	X = 1
*	SET STEP ON
	DO WHILE llOK

			y = TRANSFORM(x)
			m.ActDate	 	= NVL(loExcel.Range(ActDate_Cell+y).value,{})
			m.MDN			= NVL(UPPER(ALLTRIM(TRANSFORM(loExcel.Range(mdn_Cell+y).VALUE))),'')
			m.Master		= NVL(UPPER(ALLTRIM(TRANSFORM(loExcel.Range(master_Cell+y).VALUE))),'')
			m.MasterName	        = NVL(UPPER(ALLTRIM(TRANSFORM(loExcel.Range(mastername_Cell+y).VALUE))),'')
			m.sub			= NVL(UPPER(ALLTRIM(TRANSFORM(loExcel.Range(sub_Cell+y).value))),'')
			m.subname		= NVL(UPPER(ALLTRIM(TRANSFORM(loExcel.Range(subname_Cell+y).value))),'')
			m.Serial		= NVL(UPPER(ALLTRIM(TRANSFORM(loExcel.Range(Serial_Cell+y).VALUE))),'')			
			m.MRC			= NVL(VAL(TRANSFORM(loExcel.Range(mrc_Cell+y).value)),0)
			m.Rate			= NVL(VAL(TRANSFORM(loExcel.Range(Rate_Cell+y).value)),0)
			m.Amount		= NVL(VAL(TRANSFORM(loExcel.Range(amount_Cell+y).value)),0)

			IF TYPE("m.ActDate") <> 'D'
				m.ActDate = CTOD(SUBSTR(TRANSFORM(m.ActDate),1,8))
			ENDIF 

		
		IF m.serial = '.NULL.'
			m.serial = ''
		ENDIF  

		
		IF m.serial = '.NULL.'
			m.serial = ''
		ENDIF 

		IF NOT EMPTY(m.serial) AND !ISNULL(m.serial) AND m.serial <> '.NULL.'
				IF m.serial <> 'SIM'
					SELECT (lcCurCom)
					m.serial = SUBSTR(m.serial,1,20)
					APPEND BLANK 
					GATHER MEMVAR 
					thisform.grdCom.Refresh()
				ENDIF 
				X = X + 1
		ELSE
				EXIT
		ENDIF
	ENDDO
	loExcel.quit

CATCH TO loexp
	MESSAGEBOX("Error importing: " + loExp.Message + CHR(13) + ;
			   "Line No: " + TRANSFORM(loExp.LineNo),16,thisform.Caption)
	llreturn = .t. 
	IF TYPE('loExcel') = 'O'
		loExcel.Quit
	ENDIF 
	EXIT 
ENDTRY

Ez Logic


Ez Logic
Michigan

 

[GriffMG]

Agreed.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are not good for you. ​

 

[Mike Lewis]

On the question of alignment, I agree with both Ez and Griff. For me, it's worth the small extra effort involved. But I know many programmers think otherwise.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads