Page 1 of 2
ADORDD
Posted: Wed Jul 29, 2020 4:50 am
by 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
Re: ADORDD
Posted: Thu Jul 30, 2020 3:58 am
by 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
Re: ADORDD
Posted: Thu Jul 30, 2020 7:53 am
by 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
Re: ADORDD
Posted: Thu Jul 30, 2020 7:11 pm
by 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
but i can´t open same *.XLSx with ADO again ...
- AdoXLSx.jpg (27.74 KiB) Viewed 2955 times
so something still not working
Re: ADORDD
Posted: Fri Jul 31, 2020 6:48 am
by 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
Re: ADORDD
Posted: Fri Jul 31, 2020 11:48 pm
by 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 )
Re: ADORDD
Posted: Sat Aug 01, 2020 2:33 am
by AUGE_OHR
hi,
here working Sample
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
Re: ADORDD
Posted: Sat Aug 01, 2020 5:22 am
by bpd2000
Hi Jimmy
Error while I check your code
missing, but unknown function(s): SP_ADBFSTRUCT(),
ONDUMMY(), SETCURSORWAIT(), CALCPOS()
Re: ADORDD
Posted: Sat Aug 01, 2020 5:34 am
by AUGE_OHR
bpd2000 wrote: ↑Sat Aug 01, 2020 5:22 am
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 )
Re: ADORDD
Posted: Sat Aug 01, 2020 9:15 am
by 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