Reading from excel without knowing the sheet name? 2

[Tommyhat]

More on the ongoing saga/battle against Excel.

I'm reading chargeback reports that come from all over the place and the sheet names are always different. is there a simpler way than just asking for user input as to the sheet name?

CONN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & filepath & ";" & _
"Extended Properties=""Excel 8.0;HDR=no"""
RS.Open "SELECT * FROM [Sheet1$]", CONN
Needs to change--------------^

 

[Swi]

You could check what the sheet name is.

' Add a reference to Microsoft ADO Ext. x.x for DLL and Security
Private Function GetExcelSheetName(ByVal sFile As String, sNumber As Integer) As String
Dim adoCat As New ADOX.Catalog
adoCat.ActiveConnection = "Provider=MSDASQL.1;Persist Security Info=False;" _
& "Data Source=Excel Files;Initial Catalog=" & sFile
GetExcelSheetName = adoCat.Tables(sNumber).Name
End Function

Private Function ReadExcel(ByVal sFile As String, ByVal sSheetName As String) As DAO.Recordset
On Error GoTo fix_err
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Set Db = OpenDatabase(sFile, False, True, "Excel 8.0; HDR=YES; IMEX=1;")
Set rs = Db.OpenRecordset("SELECT * FROM [" & sSheetName & "]")
rs.MoveFirst
Set ReadExcel = rs
Set rs = Nothing
Exit Function
fix_err:
Debug.Print Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
End Function

Call the functions like so:

ExcelSheetName = GetExcelSheetName("Excel File Name", SheetNumber)

Set rs = ReadExcel("Excel File Name", Replace(ExcelSheetName, "'", ""))

Swi

 

[Tommyhat]

Swi, you've saved my hide once again!
Thanks dood.

 

[Swi]

Glad to help.

Swi

 

[tsinvr]

Thanks Swi - I just gave you another star. I was looking up something completely different but I stumbled on this post and it was something I have been looking for an answer on for a long time.

Thanks.

Ted