This is the task:
*)-------------------------------------------------------------
*)Add a button to Parent form called 'Import Detail'.
*) Button will only enable if STATUS (txtStatus) box = 'Started'.
*) When Clicked popup box - user to confirm import of excel data to header record.
*) If 'Yes'- invoke file lookup with new Child dialog Window.
*) Lookup defaults to last used path based on program UserID.
*) Disk file path will be saved to the Program UTUSER user memo whenever function is envoked.
*) If new user - current path is Program system path to the Program folder.
*) When spreadsheet is selected - import each row.
*) Import from spreadsheet until blank line is found in Account# (Column A).
*) CCA - Validate Account# as valid current GL account (GLMAST).
*) CCB - If not Valid GL Account 'Error Message - Row will be skipped'.
*) CCD - Remove any dashes from importing Account#.
*) CCE - If column Account#(column A) evaluates as number convert to string.
*) CCF - [Transaction Description] (Column B) must not be blank and must evaluate to string.
*) CCG - Debit & Credit (Column C & D) must be numeric and one column must be >0 with no value >0 in both.
*) Check Code from CCA to CCG before inserting row into SQL dbo.GLITEM table via document automation.
*) Entries in Debit & Credit (Column C & D) must match. IF NOT EQUAL the VFP Parent Form will require additional line item or change to existing lines to achieve balance before transaction is posted.
*) Decimal precison is !> M2M currency decimal.
*) Display MsgBox with a Line count and Credit & Debit sum.
*)-------------------------------------------------------------
This is the basic direction I have been pointed at:
-GETFILE() file open dialog
-APPEND FROM file TYPE XLS
or alternatively OLE automation of Excel via
oExcel = CREATEOBEJCT("Excel.Application")
-SQLCONNECT, SQLEXEC, SQLDISCONNECT (SQL PAssthrough or short SPT)
*)This is some code snippets I have found so far from a member of this site:
> > SELECT MyImportedDataInAVFPCursor
> > SCAN
> > IF ValidateFirstField(nField1)
> > lnField1Value = nField1
> > ELSE
> > lnField1Value = 0
> > ENDIF
> > IF ValidateSecondField(cField2)
> > cField2Value = SUBSTR(cField2, 1, lnMaxSizeForField2)
> > ELSE
> > cField2Value = []
> > ENDIF
> > ...
> > lcSQL = [INSERT INTO MySQL Table (Fld1, fld2, ...) VALUES (lnField1Value, lnField2Value))
> > SQLEXEC(lnConnHndl, lcSql)
> > ENDSCAN
*) I also found this:
LOCAL lcXLBook AS STRING, lnSQLHand AS INTEGER, ;
lcSQLCmd AS STRING, lnSuccess AS INTEGER, ;
lcConnstr AS STRING
CLEAR
lcXLBook = [C:\SampleWorkbook.xlsx]
lcConnstr = [Driver=] + ;
[{Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};] + ;
[DBQ=] + lcXLBook
IF !FILE( lcXLBook )
? [Excel file not found]
RETURN .F.
ENDIF
*-- Attempt a connection to the .XLSX WorkBook.
*-- NOTE: If the specified workbook is not found,
*-- it will be created by this driver! You cannot rely on a
*-- connection failure - it will never fail. Ergo, success
*-- is not checked here. Used FILE() instead.
lnSQLHand = SQLSTRINGCONNECT( lcConnstr )
*-- Connect successful if we are here. Extract data...
lcSQLCmd = [Select * FROM "Sheet1$"]
lnSuccess = SQLEXEC( lnSQLHand, lcSQLCmd, [xlResults] )
? [SQL Cmd Success:], IIF( lnSuccess > 0, 'Good!', 'Failed' )
IF lnSuccess < 0
LOCAL ARRAY laErr[1]
AERROR( laErr )
? laErr(3)
SQLDISCONNECT( lnSQLHand )
RETURN .F.
ENDIF
*-- Show the results
SELECT xlResults
BROWSE NOWAIT
SQLDISCONNECT( lnSQLHand )
-----------------------------------------------------------------
Yes I am new to VFP, therefore the confusion.
Any help here please would be highly treasured.
I posted this request on Tek-Tips and they promptly removed it - sending a rather trite email, resulting in a 'Lot of Nothingness', exposing little but perhaps that of a trifle ego who proclaimed I was asking others to do my coding for me - which is not the case. I am hoping someone will mentor me through the required steps.
With great respect and thankfulness.
Cheers,
Blair
________________________________________________________________
verum est vires