January 7, 2014 at 12:49 pm
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"
*Copy LAST UPDATE EXCEL FILE (LOOKING) TO LOCAL SERVER 'UNC PATH'
*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
commad
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
wb.RefreshAll()
Threading.Thread.Sleep(30000)
''Lock back PASSWORD level on the sheet (LOOKING)
'' hide back Sheet 'DMaster' (LOOKING)
wb.Save()
wb.Close()
excel.Quit()
Runtime.InteropServices.Marshal.ReleaseComObject(excel)
End Sub
--------------------
regards,
Cris
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply