Need to pieces of code or direction to help.

[hag]

1. How to import from a tab delimited file.
2. How to import directly from an xls file.

[Colin Haig]

Harvey

Code: Select all | Expand

bFile := {||'c:\awt\kyocera.xls'}

oExcel := TOleAuto():New("Excel.Application")
oExcel:WorkBooks:Open(eval(bFile))
oSheet := oExcel:Get("ActiveSheet")
oExcel:Visible := FALSE
nRows := oSheet:UsedRange:Rows:Count()
do while nRowInc <= nRows
   CursorWait()
   cCode   := oSheet:Cells(nRowInc,"A"):Value
   cDesc   := oSheet:Cells(nRowInc,"B"):Value
   cGroup  := 15
   nCost   := oSheet:Cells(nRowInc,"F"):Value
   nSell   := oSheet:Cells(nRowInc,"G"):Value
   cSpplr  := 'KYO01  '
   if (valtype(cGroup) = 'C')
       cGroup := str(cGroup,3)
       oKyocera:group := LEFT(ALLTRIM(cGroup) + space(3),3)
   endif
   if (valtype(cGroup) = 'N')
       cGroup := str(cGroup,3)
       oKyocera:group := LEFT(ALLTRIM(cGroup) + space(3),3)
   endif
   if (valtype(cCode) = 'C')
       oKyocera:code := cCode
   endif
   if (valtype(cCode) = 'N')
      oKyocera:code := LTRIM(str(cCode,15))
   endif
   if ! empty(cDesc)
      oKyocera:desc := cDesc
   endif
   if ! empty(nCost)
      oKyocera:cost := nCost
   endif
   if ! empty(nSell)
      oKyocera:sell := nSell
   endif
   oKyocera:append()
   nRowInc++
enddo
oExcel = nil
CursorArrow()
return(nil)
//---------------------------------------------------------------------------//

Hope this helps 

Colin


 

[hag]

Thanks for the info. Compiled and got following error.

Error description: Error 1519188/3 DISP_E_MEMBERNOTFOUND: CELLS
Args:
[ 1] = N 0
[ 2] = C A

Stack Calls
===========
Called from: => TOLEAUTO:CELLS(0)
Called from: excel.prg => MAIN(26)

What am I doing wrong.

Using Harbour

[Colin Haig]

Harvey

I am using xHarbour - and it works fine.

Regards

Colin

[anserkk]

Dear Mr.Harvey,

Mr.Colin's sample is working fine for me. Here is a complete code based on Mr.Colin's sample

Code: Select all | Expand

#include "Fivewin.ch"
//----------------------------------//
Function Main()
    
    Local oExcel,oWorkBook,cFileName:="D:\FwhExtra\FwhTests\xTest", ;
          dDate,nNumber,cDesc,nAmount,nRows,nRowInc

    oExcel   := CreateObject( "Excel.Application" )
    oExcel:WorkBooks:Open(cFileName)
    oSheet := oExcel:Get("ActiveSheet")
    oExcel:Visible := .F.
    nRows := oSheet:UsedRange:Rows:Count()
    nRowInc:=3
    Do while nRowInc <= nRows
       CursorWait()
       dDate   := oSheet:Cells(nRowInc,"A"):Value
       nNumber := oSheet:Cells(nRowInc,"B"):Value
       cDesc   := oSheet:Cells(nRowInc,"C"):Value
       nAmount := oSheet:Cells(nRowInc,"D"):Value
       
       MsgInfo(Dtoc(dDate)+CRLF+str(nNumber)+CRLF+cDesc+CRLF+str(nAmount))

       nRowInc++
    enddo
    oExcel:Quit()
    CursorArrow()
Return NIL
//-----------------------------------------------------------//

Regards

Anser

[hag]

Thanks for the help but I think I explained my needs incorrectly.
I'm looking for code to append a data base with data taken from an excel file. Presently I can do it with a coma delimited csv file but I'd like to do it from an xls file.

Thanks in advance.

[Colin Haig]

Harvey

The sample is appending data from cells into a database.

Please see a thread on the 20th March this year between Tim and Uwe - Uwe gives
some great samples of using excel.

( I dont know how to embed the topic number into the message - if someone could tell me I would appreciate it)

Cheers

Colin

[hag]

Thank you I'll do some searching