Querying an Excel file (1 Viewer)

[KitaYama]

I use the following to create a query from a csv file.

SELECT
    ..... AS .....,
    ..... AS .....,
    ..... AS .....
FROM
    [TEXT;DATABASE=D:\test\;HDR=Yes].somefile.csv;

How about if the file is Excel?
Is it possible to query a table in a specific worksheet in an Excel file?

If not, any other way that allows me access the data in a worksheet?
For now I prefer not to have a linked table to the excel file. (It would be my last option)

thanks.

 

[arnelgp]

sample:

sql = "select * from [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & Environ$("userprofile") & "\documents\testdb.xlsx].[sheet1$]"

 

[arnelgp]

you can also use the Long code:
open Excel file as database and open the first sheet (sheet1) as table:

Private Sub test2()
    Dim dbAny As DAO.Database
    Dim rs As DAO.Recordset
    Dim strFileNameAndPath As String
    Dim strExcelConnection As String

    strFileNameAndPath = Environ$("userprofile") & "\documents\testdb.xlsx"
    strExcelConnection = "Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;"
    
    Set dbAny = DBEngine.OpenDatabase( _
        Name:=strFileNameAndPath, _
        Options:=False, _
        ReadOnly:=True, _
        Connect:=strExcelConnection)
                
    'open the first Sheet (sheet1)
    Set rs = dbAny.OpenRecordset(dbAny.TableDefs(0).Name, dbOpenSnapshot)
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            Debug.Print !fNAME, !LNAME
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    dbAny.Close
    Set dbAny = Nothing
End Sub

 

[KitaYama]

@arnelgp once again you saved me.

For now, the first method suits me just fine.
Million thanks.

 

[arnelgp]

the second code, if you are working on multiple sheets on a single workbook or several workbooks.

 

[KitaYama]

@arnelgp
One more question if you don't mind.
The excel files that I receive have only one worksheet and the worksheet name is random.

Is there any way to change your #2 code to read the first sheet, no matter the sheet name?

If there's no way, I can change the sheet name to something specific (manually or with code") and do my job.

Thanks again.

 

[arnelgp]

code #2, open the recordset of whatever the name of the first sheet (remember, Tabledef(0).Name?)

to change the name of the sheet, that is different thing, since it requires Excel automation.