************************************************************************************************************ * Program: FPDUpdExcel.prg * Author: Tracy C Holzer * Date: 09/09/2002 * Purpose: Set through column store in lcRange and search for matching value in table * If matching value is found in table, store table's description field to worksheet column and update foxpro table ************************************************************************************************************ * MODIFICATIONS * 09/12/2002 TCHolzer Modified to prompt before replacing description on variables of different types * 09/13/2002 TCHOlzer Modified to store processed flag in typerecs table to differentiate records found * LPARAMETERS llcreate, lcversion CLEAR CLOSE TABLES WAIT WINDOW "Processing..." NOWAIT CLOSE TABLES SET SAFETY OFF *!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! *llcreate = .T. && overwrite table of action required only add to it, false by default unless .t. passed *lcversion="SC" && only process this state *!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! IF UPPER(TYPE('lcversion'))="L" lcVersion='SC' ENDIF IF llcreate CREATE TABLE action (cvarname c(15) NULL, ctype c(15) NULL, cstart c(3) NULL, clength c(3) NULL, cstate c(2) NULL, ; cinfo c(35) NULL, caction c(100) NULL, cdesc c(100) NULL, cfile c(20) NULL, ; cdbf c(15) NULL, lfound L, cexport c(15) NULL) ELSE IF FILE('action.dbf') USE action.dbf ALIAS action EXCLU IN 0 ELSE && no choice but to create it CREATE TABLE action (cvarname c(15) NULL, ctype c(15) NULL, cstart c(3) NULL, clength c(3) NULL, cstate c(2) NULL, ; cinfo c(35) NULL, caction c(100) NULL, cdesc c(100) NULL, cfile c(20) NULL, ; cdbf c(15) NULL, lfound L, cexport c(15) NULL) ENDIF ENDIF ************************************************************** SELE action INDEX ON ALLTRIM(caction) TAG caction INDEX ON ALLTRIM(cstate) TAG cstate INDEX ON TRANSFORM(VAL(SUBSTR(ctype,6,2)),"99")+ctype+TRANSFORM(cstart,"999") TAG typevar SET ORDER TO 0 GO BOTTOM LOCAL lans, lcXLSFile, llactionreq, lcRange, oExcel, llfound, lcvar, lcReplace, lncount, lnupdated, llblanksonly, llclose llactionreq=.T. && only process those that require an action to be done in column 7 llblanksonly=.F. && set to .t. to process only cells with no description llclose=.F. && set to .t. to process close matches i.e. mpr_bodily(num)=mpr_bodily() lnupdated=0 lncount=0 lcReplace="" lcvar="" llfound=.F. lans=6 lcXLSFile = 'c:\programnotes\excel\Type Record References.xls' lcRange = "a1:P1253" oExcel = CREATEOBJECT("excel.application") WITH oExcel .Workbooks.OPEN(lcXLSFile) WITH .ActiveWorkbook.ActiveSheet.RANGE(lcRange) FOR ix = 1 TO .ROWS.COUNT FOR jx = 1 TO .COLUMNS.COUNT IF jx<>2 && variable name column in excel spreadsheet LOOP ENDIF *--Check for valid variable in column of spreadsheet to search for in table IF ISNULL(.Cells(ix,jx).VALUE) .OR. EMPTY(.Cells(ix,jx).VALUE) && no variable to check against LOOP ENDIF *--Check the version (SC or NC) against the table currently being used - it must match to update from *--This needs to be 'SC' to get get data only pertaining to SC IF ISNULL(.Cells(ix,5).VALUE) .OR. ALLTRIM(UPPER(.Cells(ix,5).VALUE)) != lcversion LOOP ENDIF * llblanksonly should be .f. to process all records in spreadsheet IF llblanksonly && only process cells with no informtion in the description column IF !ISNULL(.Cells(ix,11).VALUE) .AND. !EMPTY(.Cells(ix,11).VALUE) && description cell already has a value LOOP ENDIF ENDIF IF llactionreq && only process those that require an action to be done in column 7 *--Check to see if there is any information in the excel ACTION column first IF ISNULL(.Cells(ix,7).VALUE) .OR. LEN(ALLTRIM(.Cells(ix,7).VALUE))=0 * No action information to check against LOOP ENDIF * data in column check it for any action required IF ALLTRIM(UPPER(.Cells(ix,7).VALUE))="NO CHANGE" LOOP ENDIF ENDIF lncount=lncount+1 WAIT WINDOW "Checking "+ALLTRIM(STR(lncount,6,0))+". "+UPPER(ALLTRIM(.Cells(ix,jx).VALUE))+"..." NOWAIT ** Find the action SELE action APPEND BLANK REPLACE action.cvarname WITH .Cells(ix,2).VALUE,; action.caction WITH .Cells(ix,7).VALUE,; action.ctype WITH .Cells(ix,1).VALUE,; action.cinfo WITH .Cells(ix,6).VALUE,; action.cdesc WITH .Cells(ix,11).VALUE,; action.cstart WITH ALLTRIM(STR(.Cells(ix,3).VALUE)),; action.clength WITH ALLTRIM(STR(.Cells(ix,4).VALUE)),; action.cstate WITH .Cells(ix,5).VALUE,; action.cfile WITH .Cells(ix,12).VALUE,; action.cdbf WITH .Cells(ix,13).VALUE,; action.cexport WITH .Cells(ix,14).VALUE ?UPPER(ALLTRIM(.Cells(ix,1).VALUE))+' '+UPPER(ALLTRIM(.Cells(ix,jx).VALUE)) ?UPPER(ALLTRIM(action.cvarname))+' '+' - '+ALLTRIM(.Cells(ix,7).VALUE) ?'*************************************************************************' SELE action lnupdatedt=RECCOUNT() ENDFOR && columns ENDFOR && rows ENDWITH .QUIT ENDWITH CLOSE TABLES ?"*******" ?"Checked "+ALLTRIM(STR(lncount,6,0))+ " and updated "+ALLTRIM(STR(lnupdated,6,0))+". Finished!" ?"*******" WAIT WINDOW "Checked "+ALLTRIM(STR(lncount,6,0))+ " and updated "+ALLTRIM(STR(lnupdated,6,0))+". Finished!" NOWAIT RETURN