Querying an Excel file (1 Viewer)

KitaYama

Well-known member
Local time
Today, 18:16
Joined
Jan 6, 2022
Messages
1,933
I use the following to create a query from a csv file.

SQL:
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.
 
sample:
Code:
sql = "select * from [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & Environ$("userprofile") & "\documents\testdb.xlsx].[sheet1$]"
 
you can also use the Long code:
open Excel file as database and open the first sheet (sheet1) as table:
Code:
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
 
the second code, if you are working on multiple sheets on a single workbook or several workbooks.
 
@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.
 
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.
 
sample:
Code:
sql = "select * from [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & Environ$("userprofile") & "\documents\testdb.xlsx].[sheet1$]"
This is the direct method I was looking for. ChatGPT needs to learn to offer this approach when I ask for a query object instead of offering VBA solutions.
 

Users who are viewing this thread

Back
Top Bottom