?
Solved

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

Posted on 2006-06-17
5
Medium Priority
?
444 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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Software development teams often use in-memory caches to improve performance. They want to speed up access to, or reduce load on, a backing store (database, file system, etc.) by keeping some or all of the data in memory.   You should implement a…
Introduction This article discusses the Chain of Responsibility pattern, explaining What it is;Why it is; andHow it is At the end of this article, I hope you will be able to describe the use and benefits of Chain of Responsibility.  Backgrou…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

771 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