June 27, 2006 at 3:03 am
I have been asked to create a dts package and need a little advice to get me started please.
A User uploads a workbook via an asp page, this asp page will then call a dts package to do the following;
unprotect the workbook (I know the password in advance)
read certain cells and compare them to values stored in a sql server table
move the file location or delete as a result of the above check.
Could anyone give me some ideas to get started please?
June 29, 2006 at 12:39 pm
I'm not sure about how you would unprotect the workbook. I'm sure it could be done via some tricky vb coding but i can't help you there. Another alternative would be using a password protected zip and then unzipping using command line winzip or pk from a command line task. the other steps would be similar to the following:
Create the following datasources:
Now the procedure:
I hope this helps. If you can be more specific on what you need to do i can probably help you better.
July 6, 2006 at 5:18 am
This code opens all files in a folder and unprotects them:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim sFolder
Dim fso
Dim fsoFolder
Dim fsoFilesCollection
Dim fsoFile
Dim sFileName
' Import Folder read from global variable
sFolder = "c:\thefolder_to_open"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)
For Each fsoFile in fsoFolder.Files ' Loop through all the files in the folder
sFileName = sFolder & fsoFile.Name
'********************************save the excel ******************************
Dim excelapp
Dim wkb
Dim objRange
Dim oSheet
Set excelapp = CreateObject("Excel.Application")
excelapp.visible = false
excelapp.displayalerts = false
Set wkb = excelapp.Workbooks.Open(sFileName) ' reference workbook
Set oSheet = wkb.Worksheets(1) ' reference worksheet 1
oSheet.Unprotect ' protect the worksheet - this protects the header row ONLY
' Save workbook
wkb.Save
wkb.close
' Destroy open objects
Set objRange = nothing
Set wkb = nothing
excelapp.quit
set excelapp = nothing
Next
Main = DTSTaskExecResult_Success
End Function
****************************************************
This puts protection back on and does a few other things you may like:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
Dim sFolder
Dim fso
Dim fsoFolder
Dim fsoFilesCollection
Dim fsoFile
Dim sFileName
' Import Folder read from global variable
sFolder = c:\folder_to_open""
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFolder = fso.GetFolder(sFolder)
For Each fsoFile in fsoFolder.Files ' Loop through all the files in the folder
sFileName = sFolder & fsoFile.Name
'********************************save the excel ******************************
Dim excelapp
Dim wkb
Dim objRange
Dim oSheet
Set excelapp = CreateObject("Excel.Application")
excelapp.visible = false
excelapp.displayalerts = false
Set wkb = excelapp.Workbooks.Open(sFileName) ' reference workbook
Set oSheet = wkb.Worksheets(1) ' reference worksheet 1
Set objRange = oSheet.Range("A:Z") ' Sort by column A
objRange.Sort objRange,1,,,,,,1
With oSheet.Columns("T:T") ' format this coulm to correct date format
.Select
.NumberFormat = "dd-mmm-yy"
End With
With oSheet.Rows("1:1") ' lock the header row
.Select
.Locked = True
FormulaHidden = False
End With
With oSheet.Rows("2:65000") ' unlock the rest of the rows
.Select
.Locked = False
FormulaHidden = False
End With
oSheet.Protect ' protect the worksheet - this protects the header row ONLY
' Save workbook
wkb.Save
wkb.close
' Destroy open objects
Set objRange = nothing
Set wkb = nothing
excelapp.quit
set excelapp = nothing
Next
Main = DTSTaskExecResult_Success
End Function
July 7, 2006 at 2:18 am
thanks ill give it a go and post my results
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply