Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

looking a the script in dstx --draft idea objective: Biweekly/DAILY UPDATE THE EXCEL ON SHAREPOINT BY USING loop (run in dtsx>>JOB) Expand / Collapse
Posted Tuesday, January 7, 2014 12:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 15, 2016 2:44 PM
Points: 4, Visits: 353
Hi all,

Is possible to update a table (that has a sql connection) from excel file that has pasword protection in the table to avoid someelse can modify the content. that table is located in a hide sheet where the table has a level protection.
The file is locate in sharepoint and need to be update daily.
the code will be put in a script from SSIS.

Objective: Biweekly/DAILY UPDATE THE EXCEL ON SHAREPOINT (run in dtsx>>JOB)
connects to the SharePoint site, loads the desired library where the file is stored 'last file upload'

Looking to try using sql script task-

*enable to let visible to run the excel file
*DETECT LAST MODIFIED DATE EXCEL FILE (LOOKING) ONSHAREPOINT not same than networkfolder "properties of the file"
*change name (OK)
*disable macro (ok)
*update file (OK) that have the follow steps:>>

- open file (ok)

- un hide sheet 'DemographicMaster'
The Visible property determines whether the object is visible:
Worksheets("DemographicMaster").Visible = xlSheetVisible

- unlock PASSWORD level on the sheet
The Unprotect method of the Worksheet class can be used. (password storage in thescript)

- refresh the specific sheet with Threading.Thread.Sleep(30000)
- put back PASSWORD level on the sheet
The Protect method of the Worksheet class can used.
- hide back Sheet ‘DemographicMaster' (LOOKING)
Worksheets("DemographicMaster").Visible = xlSheetVeryHidden ' or xlSheetHidden

*upload file (OK) TO SHAREPOINT

code base:
Public Sub Main()
' code used to update excel file(s) without human intervention
Dts.TaskResult = ScriptResults.Success
Dim excel As New Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook

' Disable Excel UI elements
excel.Visible = False 'true allow me see the process. display excel
excel.DisplayAlerts = False 'stop the message >>this action will cancel a pending refresh data

excel.AskToUpdateLinks = False
excel.AlertBeforeOverwriting = False
excel.EnableEvents = False 'prevent Events codes (such as Vb code in the excel file) from be

firing or triggering.
''''excel.DisplayStatusBar = False 'human eye
''''excel.ScreenUpdating = False ' Prevents screen refreshing as your code executes. This

improve greatly the speed of your code. Human eyes
''''excel.Interactive = False
''''excel.UserControl = False

''open the file

wb = excel.Workbooks.Open("\\Wn7x64-XXX\crs$\OutPut\XXXXX.xlsm")

''un hide sheet 'DMaster' (LOOKING)
''unlock PASSWORD level on the sheet(LOOKING)
'' refresh the specific sheet --this will be change by the specific sheet
''Lock back PASSWORD level on the sheet (LOOKING)
'' hide back Sheet 'DMaster' (LOOKING)

End Sub


Post #1528643
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse