Okay, here's more code. You will notice that I commented out one other table I had in a previous scan, so ignore that.
LOCAL lcExcelFileName AS Character
WITH ThisForm
* Dump the data from the EXCEL FIle into the WORK table.
SELECT WORK
lcExcelFileName = .cExcelFileName
APPEND FROM (lcExcelFileName) SHEET ALLTRIM(.cboTabNames.Value) TYPE XL5
* Remove the row that contains the Field Names.
SET ORDER TO
GO TOP
DELETE
PACK
SET ORDER TO ORDER_NUM
* Create table containing a list of Order Numbers, which we will use
* to cycle through the WORK table by Order Number. Why? So that we can
* go through all line items in the order.
SELECT DISTINCT Order_Num, COUNT(LINE_ITEM) AS LINE_ITEMS ;
FROM WORK ;
GROUP BY ORDER_NUM ;
INTO TABLE C:\KEITH\tmpOrders
SELECT tmpOrders
INDEX ON ORDER_NUM TAG ORDER_NUM
LOCAL lnColIndex AS Number
lnColIndex = 0
*SELECT tmpOrders
*SCAN
SELECT WORK
SCAN && FOR WORK.Order_Num = tmpOrders.Order_num
SELECT BatchN
lcOrderNo =Work.Order_Num
lcPart_Num=Work.Part_Num
*SELECT BATCHN
IF !SEEK(lcOrderNo) && 1st time in to the BATCHN table..
* If there is no record yet, make a new one.
APPEND BLANK
* Put in the data for the first line item.
*** 10/06/2009 Cecil Champenois. Fixing date format
*** Order_Date and Due_Date into format yy/mm/dd.
*lnColumnIndex=Work.Line_Item && This will always be 1.
REPLACE ;
BatchN.Line_Item WITH Work.Line_Item, ;
BatchN.Order_Num WITH Work.Order_Num, ;
BatchN.Order_Date WITH ;
SUBSTR(Work.Order_Date, 9, 2) + "/" + ;
LEFT(Work.Order_Date, 2) + "/" + ;
SUBSTR(Work.Order_Date, 4, 2), ;
BatchN.Due_Date WITH ;
SUBSTR(Work.Shop_Date, 9, 2) + "/" + ;
LEFT(Work.Shop_Date, 2) + "/" + ;
SUBSTR(Work.Shop_Date, 4, 2), ;
BatchN.SH_Name WITH Work.SH_Name, ;
BatchN.SH_Street WITH Work.SH_Street, ;
BatchN.SH_City WITH Work.SH_City, ;
BatchN.SH_State WITH Work.SH_State, ;
BatchN.SH_Phone WITH Work.SH_Phone, ;
BatchN.SO_Name WITH Work.SO_Name, ;
BatchN.SO_Street WITH Work.SO_Street, ;
BatchN.SO_City WITH Work.SO_City, ;
BatchN.SO_State WITH Work.SO_State, ;
BatchN.SO_Zip WITH Work.SO_Zip, ;
BatchN.SO_Phone WITH Work.SO_Phone, ;
BatchN.Quantity WITH Work.Quantity, ;
BatchN.Part_Num WITH Work.Part_Num, ;
BatchN.Desc WITH .GetWholeWords(Work.Desc,1), ;
BatchN.Map_Page WITH Work.Map_Page, ;
BatchN.Terms WITH Work.Terms, ;
BatchN.Ref_Num WITH SUBSTR(Work.Ref_Num, 10, 6)
lcDesc=ThisForm.GetWholeWords(Work.Desc, 2)
SET STEP ON
IF !EMPTY(lcDesc)
lnColIndex=lnColIndex + 1
cNo = ALLTRIM(STR(lnColIndex))
REPLACE ;
BatchN.Desc1 WITH lcDesc, ;
BatchN.PN1 WITH "", ;
BatchN.QTY1 WITH 0
lcDesc = ""
ENDIF
ELSE
