I think there is no need to use the Excel-Object library to pull out certain
cells from an Excel-Sheet - all this can easily be
done with standard DTS and Jet-Driver technology.
If you can identify the ranges and these ranges do have a header row you
can use an Excel connection.
Instead of selecting just the worksheet from the drop down in the source
page write a SELECT statement:
Let's say you have an excel sheet with the following data in Sheet ASheet
B3 Name C3 Number
B4 ABC C4 1
B5 XYZ C5 23
You can select the data from the sheet using the following SELECT statement:
SELECT * FROM [ASheet$B3:C5]
If you do not have a Header row you are better of using a standard data link
connection to the Excel Sheet.
Select the Microsoft.Jet.OLEDB.4.0 provider
Specify the name of the Excel-workbook as the data source.
Specify under "Extended Properties": Excel 8.0;HDR=NO;
Now use this connection as the source for your data.
Now lets take the same sample data, but this time without the header row
B4 ABC C4 1
B5 XYZ C5 23
Just use the SELECT-statement
SELECT * FROM [Asheet$B4:C5]
You can than map the columns to the appropriate columns in your target
I hope this helps - and by the way - not everyone has Excel installed on the
> I think to tell you the truth your easiest way is to use the Excel Object
> library and ADO in VB6
> Here is a quick example of retrieving cell in formation in VB
> Dim Excel_app As Excel.Application
> Dim Excel_Worksheet As Excel.Worksheet
> Dim Excel_WorkBook As Excel.Workbook
> Set Excel_app = New Excel.Application
> Set Excel_WorkBook =
> Set Excel_Worksheet = Excel_WorkBook.Worksheets(1)
> For Each c In Excel_Worksheet.Range("D6:G6").Cells
> MsgBox c.Value
> 'Clean Up
> Set Excel_Worksheet = Nothing
> Set Excel_WorkBook = Nothing
> Set Excel_app = Nothing
> Allan Mitchell
> > Hi all,
> > I'm looking for any directions on how to pull data from
> > specific cells in an Excel 2000 worksheet into SQL 2000
> > with DTS or DTS object in VB6. So far I was able to
> > determine that DTS package can import data from Excel if
> > the worksheet is formatted in a certain way (spreadsheet
> > columns have to correspond to the fields in SQL table, no
> > blank rows, etc.). My spreadsheets are not formatted that
> > way, so I have to go after specific cells/blocks of cells
> > and pull this data into SQL.
> > Does anybody have any idea how to do it or of it is at all
> > possible? I'd appreciate any help.
> > Thank you!
> > Andrew