Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Albert itkin
  Where is Albert itkin?
 brooklyn
 New York - United States
 Albert itkin
 To: Whoosh Diaz
  Where is Whoosh Diaz?
 
 Guam
 Whoosh Diaz
 Tags
Subject: RE: Excel VFP7 data transfer Emergency -- PLEAS...
Thread ID: 33934 Message ID: 33983 # Views: 1 # Ratings: 0
Version: Visual FoxPro 7 Category: Microsoft Office Automation
Date: Friday, December 05, 2003 4:38:58 PM         
   


> Hi there,
>
> I have 2 problems that are quite similar to each other. I'd appreciate any help you can give.
>
> Basically these 2 problems work together as a check and match to balance accounting entries for my company.
>
> Problem 1.
>
> I have a worksheet with 6 columns that gets bigger daily as entries are added onto it. I need to capture all six columns, and append it to a database, but only lines that have the first column = date today. The format of the first column is MMDDYY (data type is "general").
>
> All other columns are also, of type "General", and my database (dbf) has numeric fields. Will the general data type populate my numeric fields?
>
>
> Problem 2
>
> I have another worksheet that is emailed to me daily. On this one, I need to capture the lines after the header "Account #". Similar to the first problem , I need to append each column into a field, and only stop, when the first column becomes empty.
>
>
>
> Any assistance will be greatly appreciated by yours truly. I am a newbie to VFP, so please assume that I know very little of it.
>
>
> Thanks,
>
> Kim
May be this fragment of program will help you. It reads 2 parts of spreadsheet. First it finds particular rows (starting with "MatchRef"
text) until empty row selects row and saves as dbf file (Note lines marked *** and ^^^ for correct field formats)

1.
oExcel=createobject("excel.application")
WorkBook=oExcel.Workbooks.Open(infile)
StartRow=oExcel.Cells.Find("MatchRef",,,,,2).Row
IF StartRow>1
oExcel.Columns("A:AZ").Select
oExcel.Selection.Columns.AutoFit ***
LOCAL i
RateColumn=oExcel.Cells.Rows(StartRow).Find("rate").Column
oExcel.Columns(RateColumn).NumberFormat='0.00000' ^^^
i=1
DO WHILE NOT Empty(oExcel.Cells[StartRow+i,1].text)
i=i+1
ENDDO
LastRow=StartRow+i-1
oExcel.Rows(TRANSFORM(StartRow)+':'+TRANSFORM(LastRow)).Select
DbfFileName=FORCEEXT(infile,'dbf')
IF FILE(DbfFileName)
DELETE FILE (DbfFileName)
endif
oExcel.ActiveWorkbook.SaveAs(DbfFileName,8) &&&-4158
oExcel.ActiveWorkbook.Close(.f.)
USE (DbfFileName) ALIAS NewPace IN 0 EXCLUSIVE
SELECT NewPace


Here its read spreadsheet until first empty row and doing some manipulation to data
SCAN
WorkBook=oExcel.Workbooks.Open(infile)
BankPortField=oExcel.Cells[1,1].text
BankName=STRTRAN(BankPortField,'_portfolio')
MatchColRef=oExcel.Cells.Rows(1).Find("MatchToMatchRef").Column
CurrRow=2
SET DELETED off
DO WHILE !EMPTY(oExcel.Cells[CurrRow,2].text)
PaceCusipText=ALLTRIM(oExcel.Cells[CurrRow,MatchColRef].text)
DO case
CASE EMPTY(PaceCusipText)
CASE LEN(PaceCusipText)>10 OR 'match' $ LOWER(PaceCusipText) &&&Do not match
SELECT (BankName)
DELETE FOR TRIM(bcusip)==TRIM(BankCusip) &AdditCond &&&AND EMPTY(cusip)
CASE LEN(PaceCusipText)<3 AND VAL(PaceCusipText)>0 &&&Reference
IF USED('NewPace')
SELECT NewPace
LOCATE FOR TRANSFORM(MatchRef)=PaceCusipText
IF FOUND()
PaceCusip=Cusip
SELECT (BankName)
RECALL FOR TRIM(bcusip)==TRIM(BankCusip) &AdditCond
REPLACE cusip WITH PaceCusip,mstatus with "m" FOR TRIM(bcusip)==TRIM(BankCusip) &AdditCond
ELSE
MESSAGEBOX('WRONG CUSIP REFERENCE?')
susp
ENDIF
ELSE
MESSAGEBOX('Check format of match file')
SUSPEND
ENDIF
OTHERWISE &&&CUSIP
PaceCusip=PaceCusipText
SELECT pace
LOCATE FOR TRIM(cusip)==TRIM(PaceCusip)
IF !FOUND()
MESSAGEBOX('Wrong cusip '+PaceCusip +' In Match file')
else
SELECT (BankName)
RECALL FOR TRIM(bcusip)==TRIM(BankCusip) &AdditCond
REPLACE cusip WITH PaceCusip,mstatus with "m" FOR TRIM(bcusip)==TRIM(BankCusip) &AdditCond
ENDIF
ENDCASE
CurrRow=CurrRow+1
ENDDO

May be this wil help don't try to undestand logic just syntasis.


ENTIRE THREAD

Excel VFP7 data transfer Emergency -- PLEASE HELP! Posted by Whoosh Diaz @ 12/5/2003 6:58:30 AM
RE: Excel VFP7 data transfer Emergency -- PLEAS... Posted by Karben Selim Mejia @ 12/5/2003 4:19:52 PM
RE: Excel VFP7 data transfer Emergency -- PLEAS... Posted by Albert itkin @ 12/5/2003 4:38:58 PM
RE: Excel VFP7 data transfer Emergency -- PLEAS... Posted by Whoosh Diaz @ 12/6/2003 2:02:41 AM
RE: Excel VFP7 data transfer Emergency -- PLEAS... Posted by Albert itkin @ 12/8/2003 5:57:06 PM