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

Posted on 2008-11-19
Last Modified: 2013-11-30
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.
Question by:aeshepherd
    LVL 92

    Accepted Solution


    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
    LVL 15

    Expert Comment

    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.
    LVL 15

    Expert Comment

    Sorry, posted to wrong question...

    Author Closing Comment

    Thank you very much for you help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now