Very Computer

Thank you!

Andrew

Andrew,

If your Excel data was indeed in "blocks", you could still use DTS to
import those blocks if you made them "Named Ranges" in Excel. (Where you
see a list of the "tables" available from an Excel file in DTS, those with
the $ like Sheet$ are worksheets, those without are named ranges.)

I have never tried using a single-cell named range with ADO/OLE DB (which
is what DTS is using underneath), so I'm not sure what results you'd see if
you tried to retrieve individual cell values this way.

If you do need to pick up individual worksheet cells from various
locations, you might need to automate Excel in an ActiveX Script Task and
use the Excel object model to retrieve those values. However you normally
don't want to automate Office applications in an unattended server
environment.

Let us know what additional information we can offer.

-Doug

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Doug,
Thank you for reply. For my Excel import package, in DTS,
under a list of tables, all I see is the listing of
worksheets (for example:'January 02$'). The problem with
these worksheets is that they are created, formatted and
maintained by users who use them for all kinds of
reporting. I 'm looking for a way to pull some data from
those worksheets into SQL server with DTS package. I was
hoping that either the DTS designer or a package created
in VB6 would be able to access specific cells or groups of
cells (I would liek to leave the spreadsheets the way they
are) and pull the data from those cells. However, I don't
see such functionality built into neither the DTS designer
(SQL Enterprise Manager) nor the DTS object model (VB6).

Thanks,
Andrew

Quote:>-----Original Message-----

>Andrew,

>If your Excel data was indeed in "blocks", you could
still use DTS to
>import those blocks if you made them "Named Ranges" in
Excel. (Where you
>see a list of the "tables" available from an Excel file
in DTS, those with
>the $ like Sheet$ are worksheets, those without are named
ranges.)

>I have never tried using a single-cell named range with
ADO/OLE DB (which
>is what DTS is using underneath), so I'm not sure what

Quote:>you tried to retrieve individual cell values this way.

>If you do need to pick up individual worksheet cells from
various
>locations, you might need to automate Excel in an ActiveX
Script Task and
>use the Excel object model to retrieve those values.

Quote:>don't want to automate Office applications in an
unattended server
>environment.

>Let us know what additional information we can offer.

>-Doug

>This posting is provided "AS IS" with no warranties, and
confers no rights.

>Are you secure? For information about the Microsoft

Quote:>Protection Program and to order your FREE Security Tool
Kit, please visit
>http://www.microsoft.com/security.

>.

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 =
Excel_app.Workbooks.Open("f:\xl\testfiles\cellfinder.xls")

Set Excel_Worksheet = Excel_WorkBook.Worksheets(1)

For Each c In Excel_Worksheet.Range("D6:G6").Cells
    MsgBox c.Value
Next

'Clean Up

Excel_WorkBook.Close
Set Excel_Worksheet = Nothing
Set Excel_WorkBook = Nothing
Set Excel_app = Nothing

--

Allan Mitchell
www.allisonmitchell.com

Quote:> 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

I agree with Allan that your best bet is probably Excel Automation if you
need to access cells that are all over the place in the source worksheets.

Although you can place this code in an ActiveX Script Task in DTS, that's
not really taking advantage of DTS other than as a convenient context to
run VB code in SQL Server. If your source cells are designated as Named
Ranges, as I mentioned, then you could use some DTS capabilities a little
more (since those ranges can be datasources).

By the way, I would add the line "Excel_app.Quit" to Allan's cleanup code
in the VB sample, to explicitly close Excel.

Best wishes,

-Doug

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

I have asked Darren if he would mind hosting some examples of using things
like Excel and Word Automation that I have written.

I hopefully should get something to press in the next day or so and then it
should go up when Darren has the time.

One of them will use named ranges in Excel and creating them in an ActiveX
script at the start of a package.  We then change the source query to
reference this data

This would be useful if you have a group of salesmen using the same XL
template but it starts in the middle of the page and you have to go and get
the data from there.

--

Allan Mitchell
www.allisonmitchell.com

Quote:> Andrew,

> I agree with Allan that your best bet is probably Excel Automation if you
> need to access cells that are all over the place in the source worksheets.

> Although you can place this code in an ActiveX Script Task in DTS, that's
> not really taking advantage of DTS other than as a convenient context to
> run VB code in SQL Server. If your source cells are designated as Named
> Ranges, as I mentioned, then you could use some DTS capabilities a little
> more (since those ranges can be datasources).

> By the way, I would add the line "Excel_app.Quit" to Allan's cleanup code
> in the VB sample, to explicitly close Excel.

> Best wishes,

> -Doug

> This posting is provided "AS IS" with no warranties, and confers no
rights.

> Are you secure? For information about the Microsoft Strategic Technology
> Protection Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.

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:
For example:
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
table...

I hope this helps - and by the way - not everyone has Excel installed on the
server...

sven