[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 576
  • 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.

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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