READ XLSx with ADO (without Excel)

[AUGE_OHR]

hi,

i found Sample c:\MiniGUI\SAMPLES\Advanced\ReadXLS_2\ReadExcel.PRG

it work fine but i have a Problem with "unknown" XLSx how to get 1st Sheet Name
default it is "Sheet1" but if User have use different Name ...

Code: Select all

   objRS1 := CreateObject( 'ADODB.Recordset' )
   strRange := mySheet + '$'            // ALL
   //   strRange := mySheet + "$" + my1stCell + ":" + myLastCell
   BEGIN SEQUENCE
      objRS1:Open( "Select count(*) from [" + strRange + "]", objExcel, adOpenStatic )
      DO WHILE .NOT. objRS1:EOF()
         nMaxLen := MAX( nMaxLen, objRS1:Fields( 0 ) :Value() )
         objRS1:MoveNext()
      ENDDO
      objRS1:Close()
   RECOVER USING oError
      ERRORBLOCK( bError )
      MsgStop( "Operation: " + oError:Operation + " - Description: " + oError:Description, "Error" )
      RETURN
   END SEQUENCE
   ERRORBLOCK( bError ) 

using ActiveX i can "Set" Sheet Name using

Code: Select all

   oExcel:Application:Worksheets(1):name := ID_USER

[bpd2000]

hi,

i found Sample c:\MiniGUI\SAMPLES\Advanced\ReadXLS_2\ReadExcel.PRG

it work fine but i have a Problem with "unknown" XLSx how to get 1st Sheet Name
default it is "Sheet1" but if User have use different Name ...

Working Sample attached

Code: Select all

//bpd2000 31-05-2020

#include "hmg.ch"

#ifndef __XHARBOUR__
   #xcommand TRY  => BEGIN SEQUENCE WITH {|oErr| Break( oErr )}
   #xcommand CATCH [<!oErr!>] => RECOVER [USING <oErr>] <-oErr->
#endif

FUNCTION MAIN()

  GetExcelsheetname() 

Return NIL


//This function reads an Excel sheet name without using MS-Office
Function GetExcelsheetname() 

LOCAL objExcel, rsSchema, oError
LOCAL myXlsFile  := "Demofile.xls"


objExcel := TOleauto():New('ADODB.Connection')
objExcel:ConnectionString ='Provider=Microsoft.ACE.OLEDB.12.0;' + ;
                     'Data Source=' + myXlsFile + ';' + ;
                     'Extended Properties="Excel 12.0 Xml;' + "" +'IMEX=1' + '";' 
TRY
   objExcel:Open()
CATCH oError
   MsgStop("Operation: " + oError:Operation + " - Description: " + oError:Description, "Error")
   RETURN arrData
END

rsSchema := objExcel:OpenSchema(20)
rsSchema:MoveFirst()

Do While ! rsSchema:EOF()
    msginfo((rsSchema:Fields("table_name"):VALUE))
    rsSchema:MoveNext()
enddo
rsSchema:Close()
objExcel:Close()

Return nil