looking a the script in dstx --draft idea objective: Biweekly/DAILY UPDATE THE EXCEL ON SHAREPOINT BY USING loop (run in dtsx>>JOB)

  • 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