Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
?
445 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Dependencies in Software Design In software development, the idea of dependencies (http://en.wikipedia.org/wiki/Coupling_%28computer_programming%29) is an issue of some importance. This article seeks to explain what dependencies are and where they …
Introduction Many of the most common information processing tasks require sorting data sets.  For example, you may want to find the largest or smallest value in a collection.  Or you may want to order the data set in numeric or alphabetical order. …
Loops Section Overview
Screencast - Getting to Know the Pipeline

596 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