I recently have a client who is running a Win 7 Machine with Office 2010 Starter.
I am having trouble opening an excel file in Excel 2010 Starter and reading it via an xBasic script (below). Not sure how OLE works and what I can do to fix this issue...
Here is the code I am using...
I am having trouble opening an excel file in Excel 2010 Starter and reading it via an xBasic script (below). Not sure how OLE works and what I can do to fix this issue...
Here is the code I am using...
Code:
DIM SHARED vFileName as c Dim xlApp as p dim myCell as c = "" DIM vBookNum as c = "" DIM vDateRec as d = {} DIM vShipNo as c = "" DIM vRollNum as c = "" DIM vSize as n DIM vWeight as n DIM vTons as n DIM vShipDate as d = {} DIM vLoadNum as c = "" DIM vNumOfRoll as n = 0 DIM vCount as n DIM tRoll as p DIM tBook as p DIM tRec as p DIM vComments as c = "" DIM vShipName as c = "" ' DIM SHARED xShipNo as C = "" DIM SHARED xLoadNo as C = "" DIM SHARED xD_Rec as D = {} DIM SHARED xD_ship as D = {} DIM SHARED varC_result as C = "" xD_Rec = {} ok_button_label = "&OK" cancel_button_label = "&Cancel" varC_result = ui_dlg_box("Enter Information for Excel File Selected",<<%dlg% {region} Select the File to Import to Alpha:| [%p=ui_get_file("Select the Excel File that Contains the Rolls","Excel files (*.xls)",vFileName,"X");I=$a5_smart_file%.75vFileName]; Shipper Number:| [.40xShipNo]; Load Number:| [.3xLoadNo]; Change Date Recieved if Necessay:| [%DATE%.40xD_Rec!xD_Rec_*]; Enter Shipping Date:| [%DATE%.40xD_ship!xD_ship_*]; {endregion}; {line=1,0}; {region} <*15=ok_button_label!OK> <15=cancel_button_label!CANCEL> {endregion}; %dlg%,<<%code% if left(a_dlg_button,7) = "xD_Rec_" then if a_dlg_button = "xD_Rec_killfocus" then xD_Rec = ctod(dtoc(xD_Rec)) end if a_dlg_button = "" end if if left(a_dlg_button,8) = "xD_ship_" then if a_dlg_button = "xD_ship_killfocus" then xD_ship = ctod(dtoc(xD_ship)) end if a_dlg_button = "" end if %code%) tRoll = table.open("rolls") tBook = table.open("bookings") tRec = table.open("receiving") vBookNum = "" vDateRec = {} vShipNo = "" vRollNum = "" vSize = 0 vWeight = 0 vTons = 0 vShipDate={} vLoadNum = "" vComments = "" vShipName = "" 'Prompt for DateRec, DateShipped, Load Numb, and shipNo (vShipNo) xlApp = ole.create("Excel.Application") 'xlApp.Workbooks.Open(a5.Get_Path()+chr(92)+"bol.xls") xlApp.Workbooks.Open(vFileName) 'debug(1) xlApp.Visible = .T. 'comment this line out if you don't want to see Excel myCell = "F3" 'Booking Number on Excel sheet xlApp.Sheets(1).Range(myCell).Select() vBookNum = xlApp.activecell.value() vBookNum = alltrim(vBookNum) myCell = "F4" 'Shipping Comments on Excel sheet xlApp.Sheets(1).Range(myCell).Select() vComments = xlApp.activecell.value() vComments= alltrim(vComments) myCell = "B1" 'Shipping Comments on Excel sheet xlApp.Sheets(1).Range(myCell).Select() vShipName = xlApp.activecell.value() vShipName = alltrim(vShipName) if vBookNum = "" then ' DIM SHARED xBookNum as C DIM SHARED varC_result as C ok_button_label = "&OK" cancel_button_label = "&Cancel" varC_result = ui_dlg_box("No Booking Number Found on Excel Sheet. Please enter a booking number below!",<<%dlg% {region} Enter the Booking Number:| [.40xBookNum]; {endregion}; {line=1,0}; {region} <*15=ok_button_label!OK> <15=cancel_button_label!CANCEL> {endregion}; %dlg%) vBookNum = alltrim(xBookNum) end if 'ui_msg_box("Booking",vBookNum) 'vCount = e(vCount) the cell that the rolls begin 'Enter Booking # into Bookings table if lookup("bookings","book_num = alltrim('"+vBookNum+"')","book_id") <= 0 then tBook.enter_begin() tBook.book_num = vBookNum tBook.enter_end(.t.) tBook.close() end if DIM vBookID as n vBookID = lookup("bookings","book_num = alltrim('"+vBookNum+"')","book_id") 'Enter Receiving Header for Rolls into Receiving table tRec.enter_begin() tRec.book_id = vBookID tRec.shipper_num = xShipNo tRec.date_rec = xD_Rec tRec.comments = vComments tRec.shipper_name = vShipName trec.book_num = vBookNum tRec.icsloadid = vLoadNum tRec.enter_end(.t.) tRec.Close() DIM vRecID as n vRecID = lookup("receiving","shipper_num = alltrim('"+xShipNo+"') .and. book_id = val('"+vBookID+"')","rec_id") 'vCount = position for excel to start reading vCount = 9 vRollnum = "" while ("======" !$ alltrim(vRollNum)) ' for i = 1 to vNumOfRoll myCell = "B" + vCount 'ui_msg_box("",myCell) xlApp.Sheets(1).Range(myCell).Select() vRollNum = xlApp.activecell.value() myCell = "D" + vCount 'WEight xlApp.Sheets(1).Range(myCell).Select() vWeight = xlApp.activecell.value() myCell = "E" + vCount 'Tons xlApp.Sheets(1).Range(myCell).Select() vTons = xlApp.activecell.value() myCell = "F" + vCount 'Width(Size( xlApp.Sheets(1).Range(myCell).Select() vSize = xlApp.activecell.value() tRoll.enter_begin() tRoll.rec_id = vRecID ' = xShipNo tRoll.Roll_num = vRollNum tRoll.Size = vSize tRoll.Weight = vWeight tRoll.Inventory = .f. tRoll.Date_rec = xD_Rec tRoll.Date_ship = xD_ship tRoll.Book_num = vBookNum tRoll.Icsloadid = vLoadNum tRoll.book_id = vBookID tRoll.Shipper_no = alltrim(xShipNo) ' tRoll.Tons = vTons tRoll.enter_end() vCount = vCount + 1 'Defines vRollNum to determine if while statment will continue...ends if vRollNum contains "=======" myCell = "B" + vCount 'ui_msg_box("",myCell) xlApp.Sheets(1).Range(myCell).Select() vRollNum = xlApp.activecell.value() end while tRoll.close() 'xlApp.ActiveCell.FormulaR1C1 = dtoc(tbl.pick_up_date) 'xlapp.DisplayAlerts = .F. 'newname = a5.Get_Path()+chr(92)+"BOL"+chr(92)+"BOL-" + alltrim(tbl.Cust_po_num) + "_" + alltrim(dtoc(tbl.Pick_up_date,"6-")) 'xlApp.ActiveWorkbook.SaveAs(newname,-4143) '-4143 is a constant for Excel latest version, dbf4 is 11 xlApp.Workbooks.Close() 'xlapp.DisplayAlerts = .T. 'xlApp.Quit()
Comment