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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy