Solved

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

Posted on 2006-06-17
5
439 Views
Last Modified: 2013-11-12
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?
0
Comment
Question by:onesmartguy
5 Comments
 
LVL 16

Accepted Solution

by:
jimbobmcgee earned 500 total points
ID: 16931501
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:

    'VBSCRIPT
    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...

J.
0
 
LVL 5

Expert Comment

by:TheVeee
ID: 17200559
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!!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Before You Read The Article Please make sure you understand these two concepts: Variable Scope (http://www.php.net/manual/en/language.variables.scope.php) and Property Visibility (http://www.php.net/manual/en/language.oop5.visibility.php).  And to …
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
This video discusses moving either the default database or any database to a new volume.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now