Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Update dbf
Message
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Miscellaneous
Thread ID:
00778040
Message ID:
00778055
Views:
14
Hi Sergio,
The program below updates a spreadsheet and updates a table based on values in an excel spreadsheet. Maybe it can help you get started:
************************************************************************************************************
*       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
.·*´¨)
.·`TCH
(..·*

010000110101001101101000011000010111001001110000010011110111001001000010011101010111001101110100
"When the debate is lost, slander becomes the tool of the loser." - Socrates
Vita contingit, Vive cum eo. (Life Happens, Live With it.)
"Life is not measured by the number of breaths we take, but by the moments that take our breath away." -- author unknown
"De omnibus dubitandum"
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform