Export to Excel keeps appending rows to existing ones

• mmcardle

  SSCommitted

  Points: 1543

  More actions

  December 23, 2008 at 10:01 am

  #71121

  From time to time, I have had this problem and I've fixed it by scrapping the Excel spreadsheet and then creating a new one. I have a DTS job that just won't stop appending, even though it is set up the same way as other ones I have that work fine and even re-creating the Excel spreadsheet doesn't work either!!!!

  To explain, what I have is a DTS job that writes out rows of data from a query into an Excel worksheet. The DTS job drops the existing worksheet first, then creates it again and then writes the rows into it. This should ensure that the data is written once, but I have found that it is being appended to whatever is there.

  Going through the DTS job step-by-step, when I drop the worksheet and then open it to see what has happened, although the row headings have gone, the data is still in there, so that explains why the next lot is being appended, but I don't understand why the data is still there after the drop table command.. This doesn't happen on my other DTS/Excel jobs.

  I've tried clearing the data and running the DTS job; creating a new worksheet; creating a new spreadsheet.......none of these work. I'm baffled.

  Any wonderful ideas out there?

  Yours humbly,

  Mark

• Amit Gupta

  Old Hand

  Points: 321

  More actions

  December 24, 2008 at 3:57 am

  #917297

  Try it not sure

  Dim strErrMsg, f, LogFile, oFS

  Set oFS = CreateObject("Scripting.FileSystemObject")

  LogFile = folder & filename

  Const ForReading = 1, ForWriting = 2, ForAppending = 8

  On Error Resume Next

  If Not (oFS.FileExists(path\FileName)) Then

  oFS.CreateFILE(path\FileName)

  End If

  set f = oFS.OpenTextFile(path\FileName, ForAppending)

  WRITE UR CODE FOR EXCEL

  On Error Goto 0

  set f = Nothing

  Set oFS = Nothing