ADORDD

[AUGE_OHR]

hi,

under c:\hmg.3.4.4\HARBOUR\addons\rddado\ i found ADORDD Source

Question : is it still valid

does someone have try ADORDD with *.XLSx

[danielmaximiliano]

In C:\Harbour\extras\rddado , adordd.prg, STATIC FUNCTION ADO_OPEN( nWA, aOpenInfo ), line 292

Code: Select all

  CASE Lower( Right( aOpenInfo[ UR_OI_NAME ], 4 ) ) == ".xls"
         aWAData[ WA_CONNECTION ]:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + aOpenInfo[ UR_OI_NAME ] + ";Extended Properties='Excel 8.0;HDR=YES';Persist Security Info=False" )

connection with .xls files is generated, a syntax addition would be needed to see if it is likely to use this extra harbour library

[AUGE_OHR]

hi,

i saw that file but before use it i want to ask if it is still valid ... not like PGRDD

now Demo use existing Data but i try to create a XLSx and write Data from DBF using ADO.
i try CREATE TABLE but this seem not to work with Excel

[AUGE_OHR]

hi,

i found out i need to create a Table in Catalog before fill it.

Code: Select all

   oConnect := CreateObject( "ADODB.Connection" )
   oCatalog := CreateObject( "ADOX.Catalog" )
   oTable   := CreateObject( "ADOX.Table" )
   FOR ii := 1 TO iMax
      cField := aStructure[ ii ] [ DBS_NAME ]
      cType  := aStructure[ ii ] [ DBS_TYPE ]

      oColumn  := CreateObject( "ADOX.Column")
      oColumn:Name := cField

      DO CASE
         CASE cType = "C" ; oColumn:Type := adVarWChar
         CASE cType = "M" ; oColumn:Type := adLongVarWChar
         CASE cType = "N" ; oColumn:Type := adDouble
         CASE cType = "D" ; oColumn:Type := adDate
         CASE cType = "L" ; oColumn:Type := adBoolean
      ENDCASE

      oTable:Columns:Append(oColumn)
   NEXT
   oCatalog:Tables:Append(oTable)

than i try use INSERT INTO and i got a *.XLSx which Excel can open

CREXLSX.ZIP

(1.45 KiB) Downloaded 145 times

but i can´t open same *.XLSx with ADO again ...

AdoXLSx.jpg (27.74 KiB) Viewed 3249 times

so something still not working

[AUGE_OHR]

hi,

i have try ADO Recordset instead of INSERT INTO

Code: Select all

   objRS := CreateObject( "ADODB.Recordset" )
   objRS:Open( "Select * from [Sheet$]", oConnect, adOpenKeyset, adLockOptimistic )
   DO WHILE !EOF()
      aField := {}
      aValue := {}
      ii := 1
      FOR ii := 1 TO iMax
         cField := aStructure[ ii ] [ DBS_NAME ]
         cType  := aStructure[ ii ] [ DBS_TYPE ]
         nPosi  := FIELDPOS( cField )
         xValue := FIELDGET( nPosi )

         AADD(aField,cField)
         AADD(aValue,xValue)
      NEXT
      objRS:AddNew(aField,aValue)
      objRS:Update()
      SKIP
   ENDDO
   objRS:Close()
   objRS := NIL

XLSx is create and Excel can open it ... but same when i try to read it with ADO

---

while both seems to work i guess Problem is before ... how to create Table ... hm
who have a Idea

p.s. it work also with 64 Bit

[AUGE_OHR]

hi,

got it

instead of

Code: Select all

      cSql := "INSERT INTO [Sheet1$] "+ cSelect + "VALUES ( "

use

Code: Select all

      cSql := "INSERT INTO [Sheet1] "+ cSelect + "VALUES ( "

it was the "$" which was wrong ( have use it when read XLSx )

[AUGE_OHR]

hi,

here working Sample

CREXLSX2.ZIP

(11.85 KiB) Downloaded 170 times

Source include both Version : INSERT INTO or Recordset

Recordset work with "Update", not with "UpdateBatch" yet
INSERT INTO have Problem with DATE Format

Code: Select all

   xValue := DTOC( xValue )
   xValue := DTOS( xValue ) + "000000"
   xValue := HB_STOT( DTOS( xValue ) + "000000" )

none of them seems to work.
if someone find right Syntax please post it

[bpd2000]

Hi Jimmy
Error while I check your code
missing, but unknown function(s): SP_ADBFSTRUCT(),
ONDUMMY(), SETCURSORWAIT(), CALCPOS()

[AUGE_OHR]

Error while I check your code
missing, but unknown function(s): SP_ADBFSTRUCT(),
ONDUMMY(), SETCURSORWAIT(), CALCPOS()

SP_ADBFSTRUCT() -> DBFSTRUCT()
ONDUMMY() is only for debug, delete it

Code: Select all

PROCEDURE SetCursorWait( cForm, lOn )
LOCAL nHwnd
   DEFAULT lOn TO .F.
   nHwnd := GetformHandle( cForm )
   IF lOn = .T.
      SetWindowCursor( nHwnd, IDC_WAIT )
   ELSE
      SetWindowCursor( nHwnd, IDC_ARROW )
   ENDIF
RETURN

Code: Select all

FUNCTION CalcPos( nValue, iMax )
LOCAL nRet   := 0
LOCAL nEvery
LOCAL xScale

   IF iMax < 100
      nEvery := 100 / iMax
      nRet := nValue * nEvery
   ELSE
      nEvery := INT( iMax / 100 )
      xScale := iMax * nEvery                                         // Scale to 100%
      nRet := iMax / xScale * nValue
   ENDIF
RETURN ROUND( nRet, 0 )

[bpd2000]

Hi
Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.
So convert date to serial and store to excel
check code :

Code: Select all

#include "hmg.ch"

Function main()
 
   SET DATE BRITI
   SET CENT ON

msginfo( serial2dt(39448),"Date serial 44180 to date"+space(10))
msginfo( dt2serial(date()),"Today's Date Serial"+space(10))

return


Function serial2dt(xdt)
   Local x_dateorigin, x_datenew
   SET DATE BRITI
   SET CENT ON
   x_dateorigin := ctod("01/01/1900")
   x_datenew :=( x_dateorigin + xdt ) - 2 
Return dtoc( x_datenew )
   

Function dt2serial(xdt)
   Local x_dateorigin, x_datenew
   SET DATE BRITI
   SET CENT ON
   x_dateorigin := ctod("01/01/1900")
   x_datenew := (    xdt -x_dateorigin ) + 2 
Return  x_datenew