January 9, 2007 at 6:53 am
Hi
i want to change name of excel sheet. I have dts pacakage which convert ab.xls file into a table and after that i delete this file. New user store the same file with same name. My dts pacakage fail when they save sheet with different names. How i can rename excel sheet...before import it to sql server.
Thanks
January 9, 2007 at 8:36 am
Hi,
You could do this via a script task though I think you might need excel installed on the server. Check this out.
http://www.microsoft.com/technet/scriptcenter/resources/officetips/mar05/tips0308.mspx
Regards
Daniel
January 10, 2007 at 3:25 am
i've found this example lot of time ago....and i've used with success...
using active x:
' Copy File
Option Explicit
Function Main()
Dim oFSO
Dim sSourceFile
Dim sDestinationFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sSourceFile = "C:\SourceFile.txt"
sDestinationFile = "C:\DestinationFile.txt"
oFSO.CopyFile sSourceFile, sDestinationFile
' Clean Up
Set oFSO = Nothing
Main = DTSTaskExecResult_Success
End Function
to delete a file (checking before for its existence...):
Function Main()
Dim oFSO
Dim sSourceFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sSourceFile = "C:\SourceFile.txt"
' 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
merging the 2 solutions you can obtain whatever you want (may be...!)
ciao!
January 10, 2007 at 4:34 am
Thanks a lot for reply
i am trying to do something like that but my script is not working i am working on sql 2000
Function Main()
Dim objExcel
Dim objwb
Dim odjws
Dim sFilename
Dim sSheetName
sFilename = DTSGlobalVariables("gv_ExcelFileLocation").Value
sSheetName = DTSGlobalVariables("gv_SheetToRename").Value
Set objExcel = CreateObject("Excel.Application")
Set objwb = objExcel.Workbooks.Open(sFilename)
Set objws=objExcel.Worksheets("gv_SheeetToRename")
if objws.Name = CStr(sSheetName) Then
objws.Name = "PurchaseOrderRequest"
objwb.Save
End If
Set objws=Nothing
objwb.Close
Set objwb = Nothing
Excel.Application.Quit
Set Excel.Application = Nothing
Main = DTSTaskExecResult_Success
End Function
I set values for global variables also.. I am a new user so not sure where i am wrong....& i want to rename sheet not file ..
Can anyone correct it..
Thanks
January 10, 2007 at 6:45 am
i don't know if it works but... i'm confident of it!
try this, instead of oFSO.CopyFile use oFSO.CopySheet and see if it works, so in the excel file you'll have two identical sheet, the delete one of those using:
' (DeleteExcelSheet)
Option Explicit
Function Main()
Dim Excel_Application
Dim Excel_WorkBook
Dim Excel_WorkSheet
Dim iSheetCounter
Dim bFound
Dim sFilename
Dim sSheetName
sFilename = DTSGlobalVariables("gv_ExcelFileLocation").Value
sSheetName = DTSGlobalVariables("gv_SheetToDelete").Value
Set Excel_Application = CreateObject("Excel.Application")
' Open the workbook specified
Set Excel_WorkBook = Excel_Application.Workbooks.Open(sFilename)
bFound = False
' Find out how many sheets are in the workbook as there must
' be at least 1 visible sheet so if we are about to delete the
' only valid sheet then abort
iSheetCounter = Excel_WorkBook.WorkSheets.Count
If iSheetCounter > 1 then
' Now we need to make sure that the sheet to
' be deleted exists in the sheets available
For Each Excel_WorkSheet in Excel_WorkBook.WorkSheets
' Find the WorkSheet specified
If Excel_WorkSheet.Name = CStr(sSheetName) Then
Excel_WorkBook.Worksheets(sSheetName).Delete
Excel_WorkBook.Save
bFound = True
Exit For
End if
Next
Else
Msgbox "There is only one sheet. Cannot delete it."
End if
If bFound = True then
Msgbox "Outcome = Sheet Deleted"
Else
MsgBox "Outcome = No Sheet Was deleted"
End if
'Clean Up our Excel Objects
Set Excel_WorkSheet = Nothing
Excel_WorkBook.Close
Set Excel_WorkBook = Nothing
Excel_Application.Quit
Set Excel_Application = Nothing
Main = DTSTaskExecResult_Success
End Function
good luck!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply