|
Home > Archive > MS SQL Server DTS > September 2005 > How do I determine the sheet names in an Excel Spreadsheet?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How do I determine the sheet names in an Excel Spreadsheet?
|
|
| Snake 2005-09-22, 11:24 am |
| I would like to be able to read an Excel file and loop through all the sheet
names until I find the one I want. I suspect there is a way to do this but I
can't find it!
thanks,
Michael
| |
|
| We import data from several Excel files, and in some cases the sheet name
changes without notice. So I wanted to eliminate the need for knowing the
sheet name ahead of time. Here is the code which seems to do what I want!
This ActiveX code preceeds the datapump which sucks data from the Excel
file's sheet and places it in a Sql server table. Global variables provide
the Excel file name and sheet number. The activex control then sets the
connection's DataSource and the data pump's SourceObjectName. There is no
error checking but I may add it if I can figure out how.
If you have any suggestioins, please share it.
Michael
'*******************
********************
********************
***********
' Visual Basic ActiveX Script
' This sample gets global variables
' ExcelFile string path\filename.ext of the Excel file
' SheetNum int - sheet number (1 or greater) to find
' SheetName string - The returned name of the sheet using the SheetNum
value.
' Then the connection is updated to ExcelFile and the datapump.datasource is
updated to SheetName
'*******************
********************
********************
*************
Function Main()
Dim pkg, eFile, tsk
Dim Excel_Application
Dim Excel_WorkBook
Dim Excel_WorkSheet
Dim SheetNum
Dim oConn
set pkg = DTSGlobalVariables.Parent
eFile = DTSGlobalVariables("ExcelFile").Value
SheetNum = DTSGlobalVariables("SheetNum").Value
' Create Excel object and set up the Excel File to Import
Set Excel_Application = CreateObject("Excel.Application")
' Open Excel Workbook
Set Excel_WorkBook =
Excel_Application.Workbooks. Open(DTSGlobalVariab
les("ExcelFile").Value)
' Get Worksheet and its name and place in global variable
Set Excel_WorkSheet = Excel_WorkBook. Worksheets(SheetNum)
DTSGlobalVariables("SheetName").Value = Excel_WorkSheet.name & "$"
' Set the Connection
Set oConn = DTSGlobalVariables.Parent.Connections("Microsoft Excel 97-2000")
oConn.DataSource = eFile
'Set Data Pump SourceObjectname to the worksheet name
set tsk = pkg.Tasks(" DTSTask_DTSDataPumpT
ask_1").CustomTask
tsk.SourceObjectName = Excel_WorkSheet.name & "$"
Excel_WorkBook.Close
Main = DTSTaskExecResult_Su
ccess
End Function
"Snake" wrote:
> I would like to be able to read an Excel file and loop through all the sheet
> names until I find the one I want. I suspect there is a way to do this but I
> can't find it!
>
> thanks,
>
> Michael
|
|
|
|
|