none
How to remove password from excel workbook using C#.net in SSIS script task RRS feed

  • Question

  • Hi,

    I have a password protected excel workbook which needs to be loaded into a table. I understand from forums that Excel source will not read password protected files. So before using the excel file in Excel source I thought of removing password using a script task.

    I have written below code inside a script task which ran successfully but password is not removed from Excel file. After saving also it is again asking for password.

    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
    
    string Password = Dts.Variables["Password"].Value.ToString(); 
    string filename = Dts.Variables["File"].Value.ToString(); 
                    
    xlApp.DisplayAlerts = false;
    xlApp.Visible = false;
    
    Excel.Workbook wbk = xlApp.Workbooks.Open(filename, 0, false, 5, Password, "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
    
    wbk.Unprotect("");
    wbk.Save();
    
    xlApp.Quit();
    
    Marshal.ReleaseComObject(wbk);
    Marshal.ReleaseComObject(xlApp);

    Can anyone help me in figuring out the error. Please move to appropriate category if this question is not relevant to C#.

    Thanks.

    • Moved by CoolDadTx Monday, November 13, 2017 3:58 PM Office related
    Monday, November 13, 2017 6:49 AM

All replies

  • Hi Poornimadevi,

    There are two different password for protecting workbook.

    One is the password protects the workbook's sheets from being added, moved or deleted. You could use below code to remove it.

    wbk.Unprotect(Password);

    Another is the password protects the workbook from being opened. You could use below code to remove it.

    wbk.Password = "";

    Best Regards,

    Terry



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, November 14, 2017 10:02 AM