• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 591
  • Last Modified:

How do I reference individual Excel cells using VBScript when using DTS with Excel source and SQLServer destination

I have been given the task of creating a DTS package (SQLServer 2000) which has as a source an Excel workbook with several worksheets, and SQLServer tables as a destination.  The Excel worksheet is not straightforward in that a certain column or columns contain different types of data, depending upon the row.  The worksheet has been created to look like a form that a user can fill out, which is why different rows contain different types of data in a particular column.  There are checkboxes in some cells, text in others, and dates in others.  A straightforward column mapping obviously will not work, and cells need to be referenced differently.  Would you please help me out with this and let me know if this is possible and the best way to go about this?  I am new to DTS and am trying to read and research as much as possible to figure out the best way to handle this project.

Thank you for your help.
  • 2
1 Solution
Patrick MatthewsCommented:

Something like this can get you started...

Dim xlApp, xlWb, xlWs

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open("path to workbook")
Set xlWs = xlWb.Worksheets("name of sheet")

With xlWs
    MsgBox .Range("a1")            'example using A1 address
    MsgBox .Cells(1, 1)            'example using Cells property
    MsgBox .Range("NamedRange")      'example using named range
End With

xlWb.Close False
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
I am on vacation and will not be returning to work until 12/2. Our company pushed a software update that has left me unable to VPN into our system, so all I have is normal internet access.

I am using late bindingg and can send the full method when I return to work next Tuesday.
Sorry, posted to wrong question...
aeshepherdAuthor Commented:
Thank you very much for you help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now