How can I read an excel worksheet using ADO/OLEDB when worksheet name is alway different

I am trying to read and import a daily excel report. I have no problem doing this when I know the worksheet name, but since it changes all the time I have to rename the worksheet in order to import it. I would like to make this an automatic process. Is there something I can put in the query string to always select the first worksheet with out having to know the worksheet name?
Who is Participating?
jimbobmcgeeConnect With a Mentor Commented:
It depends entirely on how you are reading the sheet.  If you are using the Excel COM object, you can refer to the first sheet by:

    szExcelFileName = "x:\pathto\the_workbook.xls"
    Set oExcel = CreateObject("Excel.Application")
    Set oBook = oExcel.Workbooks.Open(szExcelFileName)
    Set oSheet = oBook.Sheets(0)

If you're using ADO, as far as I know, you're out of luck.  You would have to use the Excel object (or a third-party library, like xlsgen) to rename the sheet, save it and then use ADO methods on it...

Im reading into your question, but it seems you using Excel to then import another excel spreadsheet.  If you know what directory the file is in and its always the latestest xls spreadsheet or the file in it, you could use the file directories Apis to read the directory and retrieve it.  Also you could then use them to rename it, move it, or do whatever you wanted before you even imported the spreadsheet.  I do have code on all this, but before I go digging them out of the mothballs.. want to make sure this is truely what want.

Let me know.. Thanks!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.