Getting Sheet Name from Excel

[Ace08]

Hi I'm just wondering is there a way to get the sheet name of an excel file without having to use excel objects? I dont have MS Excel installed on my PC.

I've found ptrex's script to read an excel file as dbf but the table name (Sheet Name) also needs to be defined currently i need to read 10 files with different sheet names.

expandcollapse popup

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001
Global $s_Filename=FileGetShortName("D:\Sample\638FE12D2614.xls")
Global $s_Tablename = "[Sheet1$]"
; Initialize COM error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
; Source XLS data
$objConnection = ObjCreate("ADODB.Connection")
$objRecordSet = ObjCreate("ADODB.Recordset")
$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source="&$s_Filename&";" & _
                "Extended Properties=""Excel 8.0;HDR=Yes;"";")
$objRecordSet.Open ("Select Count(*) FROM"& $s_Tablename & "Order by 1 Asc" , $objConnection, $adOpenStatic, $adLockOptimistic, $adCmdText)

Do
    ConsoleWrite ($objRecordSet.Fields(0).value+1 [email="&@CR"]&@CR[/email]) ; + 1 because it is 0 based
    $objRecordSet.MoveNext()
Until $objRecordSet.EOF()
$objConnection.Close
$objConnection = ""
$objRecordSet = ""

Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "       & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "       & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile     & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

[water]

Are those files xls or xlsx files? xlsx is compressed xml format so you might read them this way.

[Ace08]

just xls

[water]

Then I guess you are out of luck.

Just an idea: Use one of the online translation services to create a format you can process at your site. Example: http://www.coolutils.com/Online-Excel-Converter.php

[Ace08]

Whew thats what im afraid of. might as well look for other options thanks water for the help

[Ace08]

Have searched the forum and found out that same question was already asked. Below code was provided by ptrex.

expandcollapse popup

Const $adSchemaTables = 20
Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001 ;  =&H0001
Global $s_Filename=FileGetShortName("D:sampletest.xls")
; Initialize COM error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")
$objConnection = ObjCreate("ADODB.Connection")
$objRecordSet = ObjCreate("ADODB.Recordset")
$objConnection.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source="&$s_Filename&";" & _
                "Extended Properties=""Excel 8.0;HDR=Yes;"";")
ConsoleWrite(_adoListTables() & @LF)
ConsoleWrite( @LF)
Func _adoListTables()
    Local $oList = ''
    If IsObj($objConnection) = 0 Then Return SetError(1)
    $oRec = $objConnection.OpenSchema($adSchemaTables)
    While NOT $oRec.EOF
        If StringLen( $oRec("TABLE_TYPE").value) > 5 Then;; Skip the hidden internal tables
            $oRec.movenext
            ContinueLoop
        EndIf
        $oList = $oList & $oRec("TABLE_NAME").value & ' | '
        $oRec.movenext
    Wend
    If $oList <> '' Then
        Return '|' & StringTrimRight($oList,1)
    Else
        SetError(3, 0, 0)
        Return $oList
    EndIf
EndFunc    ;<===> _adoListTables()

$objConnection.Close
$objConnection = ""
$objRecordSet = ""
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"COM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"   & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "       & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "   & @TAB & $oMyError.scriptline   & @CRLF & _
             "err.source is: "       & @TAB & $oMyError.source       & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile     & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1)  ; to check for after this function returns
Endfunc

Thanks ptrex