Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    9

    Red face Unanswered: Problem Extracting DTS to Excel

    HI,

    I'm creating an Scheduled DTS to extract Sales data into Excel and Email the Excel sheet and I almost have it complete Apart of one small problem. (The bit I though would be easy).

    The DTS is Emailing, scheduling and executing ok.

    I have had to write a little bit of VBscript to cleardown the contents of the Excel fle before I begin loading the data each time, but when the data is loaded into Excel it does not start at the first Cell but maybe 700 lines down the worksheet

    I think it is my VBscript that is the problem but I'm not sure. Has anyone else had problems like this?

    Example

    For Each Excel_WorkSheet in Excel_WorkBook.WorkSheets
    If Excel_WorkSheet.Name = CStr(sSheetName) Then
    Excel_WorkBook.Worksheets(sSheetName).Range
    ("A2:IV65536").ClearContents
    Excel_WorkBook.Save
    bFound = True
    Exit For
    End if
    Next

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    i haven't looked into excel object, but can't you issue something like Excel_WorkBook.Home before you do Excel_WorkBook.Save?

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Have you tried deleting the range rather than clearing the contents ? Why do you need to do this ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •