Solved

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

Posted on 2006-06-17
5
442 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Fluent Interface Design Pattern You can use the Fluent Interface (http://en.wikipedia.org/wiki/Fluent_interface) design pattern to make your PHP code easier to read and maintain.  "Fluent Interface" is an object-oriented design pattern that r…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

726 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