Hi Ralph,
Thanks a lot for your alternative method. That’s a really good idea that I hadn’t thought of 🙂
Please find below my new code applied …
I note your proposal for the HTBasic 2021 test, I will send you an email very soon
Regards,
LTK
PS:
I confirm it’s not easy to paste HTBasic code in the forum.
I’m very curious to know how PatB managed to keep his indentation in his message !
CONFIGURE LONGFILENAMES ON
DLL UNLOAD ALL
CLEAR SCREEN
KEY LABELS OFF
!
DIM C$[200]
DIM Data$(1:200,1:259)[20]
DIM Disque1$[80],Disque2$[80],Disque3$[80]
!
Nb1=259*20 ! Nb of values / Lines
Nb2=259-1 ! Nb of separator
Nb3=2 ! Line Terminator [CR]
Nb=Nb1+Nb2+Nb3
!
ALLOCATE Lines$(200)[Nb]
!
LONG Hconvexcel! Excel conversation handle
LONG Retval ! Return value for the DLL functions
!
Pc=1 ! Computer choice
!
!————————————————
! Computers configuration
!————————————————
SELECT Pc
CASE 1 ! HTBasic 10.1
Disque1$=”C:\Program Files (x86)\HTBwin 10.1\DLL Toolkit\Samples\HTBDde”! 22/01/2009 13:32
!Disque1$=”C:\Program Files (x86)\HTBwin 10.1\DLL Toolkit\Samples\HTBDde” ! 14/11/2020 08:44
Disque2$=”C:\Program Files (x86)\Microsoft Office\root\Office16″ ! EXCEL 2010
Disque3$=”C:\HP_progs\Progs”
CASE 2 ! HTBasic 10.0
Disque1$=”C:\Program Files (x86)\HTBwin10\DLL Toolkit\Samples\HTBDde” ! 22/01/2009 13:32
Disque2$=”C:\Program Files (x86)\Microsoft Office\Office14″ ! EXCEL 2010
Disque3$=”C:\HP_progs\Progs”
END SELECT
!
!————————————————
! HTBDDE dll
!————————————————
MASS STORAGE IS Disque1$ ! Go to DDE Directory
!
DLL LOAD “HTBDDE”
DLL GET “LONG HTBDDE:Ddeconnect”
DLL GET “SHORT HTBDDE:Ddeterminate”
DLL GET “LONG HTBDDE:Ddetimeout”
DLL GET “SHORT HTBDDE:Ddeexecute”
DLL GET “SHORT HTBDDE:Ddepoke”
DLL GET “CHARPTR HTBDDE:Dderequest”
DLL GET “SHORT HTBDDE:Runexe”
!
MASS STORAGE IS Disque3$ ! Return to my Directory
!
!——————————————-
! Connect to the EXCEL System topic
!——————————————-
Hconvexcel=0
Hconvexcel=FNDdeconnect(“Excel”,”System”)
!
IF Hconvexcel=0 THEN
!
! If we couldn’t connect, lauch EXCEL and try again
!
C$=Disque2$&”\Excel.exe”
C$=TRIM$(C$)
!
Retval=0
Retval=FNRunexe(C$,1)
!
PAUSE ! Sometime Very long !!! For the moment I prefer to WAIT and [CONTINUE] manually
! ! when EXCEL is open, I will work on it with Taskmanager …
!
Hconvexcel=0
WHILE Hconvexcel=0
Hconvexcel=FNDdeconnect(“Excel”,”System”)
WAIT .1
END WHILE
!
ELSE
!
GOTO Exit
!
END IF
!
!——————————————-
! OPEN selected workbook in DDE Directory
!——————————————-
Fich$=”sample.xlsx”
!
C$=Disque1$&”\”&Fich$
C$=”[OPEN(“””&C$&”””)]”
C$=TRIM$(C$)
!
Retval=0
Retval=FNDdeexecute(Hconvexcel,C$)
WAIT 1
!
!——————————————-
! PURGE csv File if existing …
!——————————————-
Fich$=Fich$[1,POS(Fich$,”.xlsx”)-1]&”.csv”
Fich$=Disque1$&”\”&Fich$
!
ON ERROR GOSUB Err
PURGE Fich$
OFF ERROR
WAIT 1
!
!——————————————-
! SAVE AS csv
!——————————————-
C$=”[Save.As(“””&Fich$&”””,23)]” ! 23 is the file type for csv
C$=TRIM$(C$)
!
Retval=0
Retval=FNDdeexecute(Hconvexcel,C$)
WAIT 1
!
!——————————————-
! CLOSE EXCEL Activ File
!——————————————-
Retval=0
Retval=FNDdeexecute(Hconvexcel,”[File.Close(0)]”)
WAIT 1
!
!——————————————-
! READ csv File with HTBasic
!——————————————-
ASSIGN @Samples TO Fich$;FORMAT ON
FOR Row=1 TO 200
ENTER @Samples;Lines$(Row)
NEXT Row
ASSIGN @Samples TO *
!
Total=0
!
FOR Row=1 TO 200
!
GOSUB Split ! get comma delimited data from Lines
!
FOR Column=1 TO 259
Total=Total+1
PRINT Total,Row,Column,Data$(Row,Column)
NEXT Column
!
NEXT Row
!
PRINT “Total =”;Total
GOTO Exit
!
!
!———————————————
Err: ! ERROR management
!———————————————
ERROR RETURN
!
!
!———————————————
Split: ! Extract delimited values from string
!———————————————
IF POS(Lines$(Row),”,”) THEN C$=”,”
IF POS(Lines$(Row),”;”) THEN C$=”;”
!
J$=””
Column=1
FOR I=1 TO LEN(Lines$(Row))
IF Lines$(Row)[I,I]<>C$ THEN
J$=J$&Lines$(Row)[I,I]
ELSE
Position=I
Data$(Row,Column)=J$
J$=””
Column=Column+1
END IF
NEXT I
Data$(Row,Column)=Lines$(Row)[Position+1,LEN(Lines$(Row))]
RETURN
!
!
!——————————————–
! Terminate the connection with EXCEL
!——————————————–
Exit: IF Hconvexcel=1 THEN
Retval=0
Retval=FNDdeterminate(Hconvexcel)! Terminate EXCEL DDE conversation
END IF
!
DLL UNLOAD “HTBDDE” ! Unload DDE dll
KEY LABELS ON
!
END
