Sign in to follow this  
Followers 0
Ace08

Getting Sheet Name from Excel

6 posts in this topic

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.

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

Work smarter not harder.My First Posted Script: DataBase

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

just xls


Work smarter not harder.My First Posted Script: DataBase

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


Work smarter not harder.My First Posted Script: DataBase

Share this post


Link to post
Share on other sites

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

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


Work smarter not harder.My First Posted Script: DataBase

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0