Overwriting data in an excel spreadsheet

Question

Post reply

Overwriting data in an excel spreadsheet

David Bobko

SSC Enthusiast

Points: 126
More actions
June 20, 2006 at 3:26 pm

#68716

has anyone gotten past the obstacle of not being able to drop and recreate the table (worksheet) in an excel spreadsheet successfully?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Harold Buckner SSCarpal Tunnel Points: 4024 More actions · Answer 1

We just use a sql task to drop table [table_name] and then another sql task to create table [table_name]

DROP TABLE NEW_TABLE

CREATE TABLE `New_Table` (

`pmt_no` Long ,

`amount` Double ,

`issue_dt` VarChar (12) ,

`pmt_vendor_name` VarChar (40) )

No Problems here.

MattieNH SSCertifiable Points: 7463 More actions · Answer 2

I don't know if this is what you're asking (table being a multi-purpose word), but if the problem is recreating the spreadsheet file each time, I did this, which I got from somewhere on this site, plus the DTS site http://www.sqldts.com/

ActiveX Tasks

Delete the Spreadsheet

Option Explicit

Function Main()

Dim oFSO

Dim sSourceFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

sSourceFile = DTSGlobalVariables("OutputFile").Value

' Check if file exists to prevent error

If oFSO.FileExists(sSourceFile) Then

oFSO.DeleteFile sSourceFile

End If

' Clean Up

Set oFSO = Nothing

Main = DTSTaskExecResult_Success

End Function

Copy Empty File to Destination

Option Explicit

Function Main()

Dim oFSO

Dim sSourceFile

Dim sDestinationFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

sSourceFile = DTSGlobalVariables("SourceFile").Value

sDestinationFile = DTSGlobalVariables("OutputFile").Value

oFSO.CopyFile sSourceFile, sDestinationFile

' Clean Up

Set oFSO = Nothing

Main = DTSTaskExecResult_Success

End Function

This requires an empty file to copy in.

I set the file names up as global variables.

Mattie

David Bobko SSC Enthusiast Points: 126 More actions · Answer 3

the drop table recreate table statements succeed and the data is no longer there, however for some reason the file size does not shrink, and eventually I get an error saying that I exceeded the max number of rows.

I got around this by, deleting the file and copying a initial copy of the file into its place each time I have to run my dts package.

PurpleLady SSCertifiable Points: 7698 More actions · Answer 4

We have quite a few DTS pacakages that function similarly. In each, we delete the spreadsheet using xp_cmdshell within an execute SQL task, and recreate it using the Visual Basic ActiveX Script task in DTS. Then we populate the spreadsheet, etc... Generally speaking, the process works very well.

David Bobko SSC Enthusiast Points: 126 More actions · Answer 5

Can you post an example of your active x script to recreate the spreadsheet?

Thanks,

David

PurpleLady SSCertifiable Points: 7698 More actions · Answer 6

Here goes... THe names have been changed to protect the guilty

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

Dim appExcel

Dim newBook

Dim xSheet5

Dim xSheet6

Dim oPackage

Dim oConn

Set appExcel = CreateObject("Excel.Application")

Set newBook = appExcel.Workbooks.Add

Set oSheet = newBook.Worksheets(1)

newBook.Sheets.Add

newBook.Sheets(1).Name = "Productivity YTD"

newBook.Sheets(2).Name = "EQ Weekly"

newBook.Sheets(3).Name = "EQ QTR"

newBook.Sheets(4).Name = "EQ YTD"

Set xSheet5 = newBook.Worksheets.add

xSheet5.Name = "Productivity QTR"

Set xSheet6 = newBook.Worksheets.add

xSheet6.Name = "Productivity Weekly"

Set oSheet=newBook.Sheets(1)

'oSheet.Range ("A1").Value = "Western Southern Productivity Rating Report"

Title (oSheet)

Set oSheet=newBook.Sheets(2)

'oSheet.Range ("A1").Value = "Western Southern Productivity Rating Report 2"

Title (oSheet)

Set oSheet=newBook.Sheets(3)

'oSheet.Range ("A1").Value = "Western Southern Qualifier Rating Report"

Title (oSheet)

Set oSheet=newBook.Sheets(4)

'oSheet.Range ("A1").Value = "Western Southern Qualifier Rating Report 2"

Title (oSheet)

Set oSheet=newBook.Sheets(5)

'oSheet.Range ("A1").Value = "Western Southern Productivity Rating Report 3"

Title (oSheet)

Set oSheet=newBook.Sheets(6)

'oSheet.Range ("A1").Value = "Western Southern Qualifier Rating Report 3"

Title (oSheet)

' Specify the name of the new ExcelFile to be created

DTSGlobalVariables("FileName").Value = "\\server\...\WestSouthWeekly.xls"

With newBook

.SaveAs DTSGlobalVariables("FileName").Value

.save

End With

appExcel.quit

'Destroy all objects

set appExcel = nothing

set newBook = nothing

set oSheet = nothing

Main = DTSTaskExecResult_Success

End Function

Sub Title(oSheet)

oSheet.Range("A1").Value = "Division Code"

oSheet.Range("B1").Value = "District"

oSheet.Range("C1").Value = "Name"

oSheet.Range("D1").Value = "Do Not Proceed"

oSheet.Range("E1").Value = "Proceed"

oSheet.Range("F1").Value = "Total"

oSheet.Range("G1").Value = "% Passed"

oSheet.Range("H1").Value = "SDate"

oSheet.Range("I1").Value = "EDate"

End Sub

Karen Dunning Old Hand Points: 356 More actions · Answer 7

As far as the Drop Table, Create Table SQL Tasks, You have to make sure that the Insert/Names in Excel is reading your table correctly. Then you can not do anything to the table after creation by SQL. If you change or manipulate any of the data in the excel sheet ON THAT SHEET, it will not recreate properly. To manipulate the data, you'll have to link to it from another sheet and do your calculations, formatting, manipulations on that sheet.

Kindest Regards,

Just a bozo on this bus