Read excel when sheet name is unknow

[maniootek]

I have this part of code:

expandcollapse popup

Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = 0x0001
Global $s_Filename=FileGetShortName("C:TmpTest.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 &@CR) ; + 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

I would like to run this script but with the excel file which I don't know the default sheet name (without variable $s_Tablename). Any idea?

Edited January 13, 2016 by maniootek

[BrewManNH]

Look at the Excel UDF and look inside the function _Excel_SheetList, you will see how to get the names of the sheets.

[water]

Do you need to use ADO to read the Excel sheet?

[mLipok]

ADOX ?
 

[water]

I mean: If Excel is installed it is much simpler to read the first sheet.

[mLipok]

@maniotek

You should look here:

https://social.msdn.microsoft.com/Forums/en-US/739a3e93-fff1-4883-bff2-d7db2ea23ea6/ado-excel-how-to-use-ado-retrieve-excel-worksheet-name-and-some-information?forum=isvvba

or here:

http://www.motobit.com/tips/detpg_listdb/

 

 

[maniootek]

‌@water

All I want achieve is to read cells data from excel file when I don't know the sheet name without opening that file as a window.

‌@BrewManNH

as far as I am concerned, Excel UDF is based on different type of connection and it must open excel file as a window to read data

‌@mLipok

unfortunatelly I couldn't find solution for my problem with articles you sent me

[water]

If you have Excel installed then you can easily access the sheet by number using hte Excel UDF.

So my question is: Do you have Excel installed?

[maniootek]

I do have installed excel but I don't want to be addicted to have it.

[water]

Why not use it as long as it is available. You can still look for another solution if Excel is not installed any longer.
Reading all cells of the first Sheet would look like

#include <Excel.au3>
$oExcel = _Excel_Open()
$oWorkbook = _Excel_BookOpen($oExcel, "path to the Excel file goes here")
$aCells = _Excel_RangeRead($oWorkbook)

 

Edited January 15, 2016 by water

[maniootek]

but it opens excel window which I can not accept (script must working on the background and do not disturb user working on station).

Here's a nice article about this method but almost anytime you must to know the sheet name

https://support.microsoft.com/en-us/kb/278973

Edited January 15, 2016 by maniootek

[water]

OK. This does everything in the background:

#include <Excel.au3>
#include <Array.au3>
$oExcel = _Excel_Open(False) ; Open Excel invisible
$oWorkbook = _Excel_BookOpen($oExcel, "C:\temp\test.xlsx", True, False) ; Open Workbook invisible and read-only
$aCells = _Excel_RangeRead($oWorkbook)
_Excel_Close($oExcel, False)
_ArrayDisplay($aCells)

 

[maniootek]

Thank you @water

[water]

This means: Problem solved

[mLipok]

expandcollapse popup

#include <FileConstants.au3>
#include <MsgBoxConstants.au3>


Global Const $ADO_adSchemaTables = 20

_Example()
Func _Example()
    Local $oConnection = ObjCreate('ADODB.Connection')
    $oConnection.Open(_ADO_XLS_ConnectionString())

    Local $oTablesSchema = $oConnection.OpenSchema($ADO_adSchemaTables)
    While Not $oTablesSchema.EOF
        ConsoleWrite("! " & $oTablesSchema.Fields("TABLE_NAME").Value & @CRLF)
        $oTablesSchema.MoveNext
    WEnd
EndFunc

Func _ADO_XLS_ConnectionString($sXLS_FileFullPath = Default, $sProvider = Default, $sXLS_Version = Default, $HDR = Default, $IMEX = 0)
    If $sXLS_FileFullPath = Default Then
        $sXLS_FileFullPath = FileOpenDialog('Select XLS File', @ScriptDir, 'XLS file (*.xls)', $FD_FILEMUSTEXIST + $FD_PATHMUSTEXIST)
    EndIf

    If $sProvider = Default Then
        $sProvider = 'Microsoft.Jet.OLEDB.4.0'
    EndIf

    If $sXLS_Version = Default Then
        $sXLS_Version = 'Excel 8.0'
    EndIf

    If $HDR = Default Or $HDR = True Or $HDR = 'yes' Then
        $HDR = 'yes'
    Else
        $HDR = 'no'
    EndIf

    Local $sXLS_ConnectionString = _
            'Provider=' & $sProvider & ';' & _
            'Data Source="' & $sXLS_FileFullPath & '";' & _
            'Extended Properties="' & $sXLS_Version & ';HDR=' & $HDR & ';IMEX=' & $IMEX & '";'

    Return $sXLS_ConnectionString
EndFunc   ;==>_ADO_XLS_ConnectionString