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.
Anne
aeshepherdAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Anne,

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
xlApp.Quit
Set xlApp = Nothing
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbbishopCommented:
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.
0
dbbishopCommented:
Sorry, posted to wrong question...
0
aeshepherdAuthor Commented:
Thank you very much for you help.
Anne
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.