December 23, 2008 at 10:01 am
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
December 24, 2008 at 3:57 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply