Disable Save functionality in Excel with VBA through C#

[mutanic]

Assalamualaikum & salam sejahtera warga CG, sorry aku repost soalan aku dari StackOverflow.com kat sini, sorry sekali lagi sebab pakai omputih ayaq sejuk sbb copy paste ja ni...

Minta tolong warga CG yang boleh tolong perbetui kat mana kurang sat :

Disable Save functionality in Excel with VBA through C#

Ni code aku, nak nampak elok sket korang buka link di StackOverflow.com tu...

I got one function where I need to protect the Excel file uploaded in server where it can't be edited or even saved. To achieve this, I programmatically secure the file and added some of VBA code limit what user can do with it. The VBA code function suppose to have 2 functionality where it can hide the Ribbon and prevent Save/SaveAs function. You may refer into my code where I got 3 version of VBA code inside but the most desired is the no.3 of VBA code inside it. This is my function for that purpose where I will pass the file location in string to this function. I wish someone can assist me on how to allow the program to save the secured Excel file (using VBA code no.3). From what I understand, I can't save it due to the VBA code on Save(not SaveAs) part won't allow this line wbkExcel.Save() to proced.

You may test the VBA code in your Excel file see if it's working or not (tested working for me so far...)
Thanks in advance everyone...

protected void ExcelEncryptor(string strExcelFile)
{
    Microsoft.Office.Interop.Excel.Application wAppExcel = new Microsoft.Office.Interop.Excel.Application();
    wAppExcel.Interactive = false;
    wAppExcel.Visible = true;
    wAppExcel.DisplayAlerts = false;
    wAppExcel.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable;
    Microsoft.Office.Interop.Excel.Workbook wbkExcel = wAppExcel.Workbooks.Open(strExcelFile.ToString(),System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value);

    string strVBCode = string.Empty;
    //To hide Ribbon only
    /*strVBCode = "Private Sub Workbook_Open()\r\n" +
                "     msgbox \"This document is protected!\"\r\n" +
                "     application.ExecuteExcel4Macro \"show.toolbar(\"\"Ribbon\"\",False)\"\r\n" +
                "End Sub";*/
    //To hide Ribbon + Disable SaveAs (F12 key) but still can Save (Ctrl+S key)
    /*strVBCode = "Private Sub Workbook_Open()\r\n" +
              "     msgbox \"This document is protected!\"\r\n" +
              "     application.ExecuteExcel4Macro \"show.toolbar(\"\"Ribbon\"\",False)\"\r\n" +
              "End Sub\r\n" +
              "Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)\r\n" +
              "     If (SaveAsUI = True) Then\r\n" + 
              "         msgbox \"You are not allowed to save this document!\"\r\n" + 
              "         Cancel = True\r\n" + 
              "     End If\r\n" + 
              "End Sub";*/
    //To hide Ribbon + Disable SaveAs (F12 key) and cannot Save (Ctrl+S key)
    strVBCode = "Option Explicit\r\n" +
                "Dim SaveByCode As Boolean\r\n" +
                "Const msg As String = \"You are not allowed to save this document!\"\r\n" +
                "Const ttl As String = \"This document is protected!\"\r\n" +
                "Private Sub Workbook_Open()\r\n" +
                "     MsgBox msg, vbExclamation, ttl\r\n" +
                "     application.ExecuteExcel4Macro \"show.toolbar(\"\"Ribbon\"\",False)\"\r\n" +
                "End Sub\r\n" + 
                "Private Sub Workook_BeforeClose(Cancel As Boolean)\r\n" +
                "   If Me.Saved = False And SaveByCode = False Then\r\n" +
                "       MsgBox msg, vbExclamation, ttl\r\n" +
                "       Cancel = True\r\n" +
                "   End If\r\n" +
                "End Sub\r\n" +
                "Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)\r\n" +
                "   Application.EnableEvents = False\r\n" +
                "   If SaveByCode = True Then\r\n" +
                "       SaveThisFile\r\n" +
                "   Else\r\n" +
                "       MsgBox msg, vbExclamation, ttl\r\n" +
                "       Cancel = True\r\n" +
                "   End If\r\n" +
                "   Application.EnableEvents = True\r\n" +
                "End Sub\r\n" +
                "Sub SaveThisFile()\r\n" +
                "   SaveByCode = True\r\n" +
                "   ThisWorkbook.Save\r\n" + 
                "End Sub";
    Microsoft.Vbe.Interop.VBProject vbMacro = wbkExcel.VBProject;
    Microsoft.Vbe.Interop.VBComponent vbCode = vbMacro.VBComponents.Item("ThisWorkBook");
    Microsoft.Vbe.Interop.CodeModule vbModule = vbCode.CodeModule;
    vbModule.AddFromString(strVBCode.ToString());

    wbkExcel.Protect("Pa$$w0rd!", true, false);
    foreach (Microsoft.Office.Interop.Excel.Worksheet wstExcel in wAppExcel.Worksheets)
    {
        wstExcel.Protect("Pa$$w0rd!", true, true, true, true, false, false, false, false, false, false, false, false, true, true, false);
    **
    wbkExcel.Save();
    wbkExcel.Close(System.Reflection.Missing.Value,System.Reflection.Missing.Value,System.Reflection.Missing.Value);
    Marshal.ReleaseComObject(wbkExcel);
    Marshal.ReleaseComObject(wAppExcel);
    GC.Collect();
**

 

[mutanic]

Errr....BUMP seround.... calling for help, any VB/C# coder please...

 

[evit]

Errr....BUMP seround.... calling for help, any VB/C# coder please...

Ni lebih untuk orang2 pro je ni, kalo macro yg simple boleh gak tolong. Saya gatal tangan duduk copy code bro dalam VB pastu x reti nak delete... huhu.

Apa2 pun good luck TT, semoga ada yg dapat membantu

 

[mutanic]

Ni lebih untuk orang2 pro je ni, kalo macro yg simple boleh gak tolong. Saya gatal tangan duduk copy code bro dalam VB pastu x reti nak delete... huhu.

Apa2 pun good luck TT, semoga ada yg dapat membantu

tu Macro code yg simple la tu...nko guna dlm VBA ja, buka Excel pastu press Alt+F11 untuk masukkan code...code tu mmg untuk halang user dari re-save file...yang x reti nak delete maksudnya menda? delete menda?

 

[mutanic]

Problem SOLVED!!!

Syukur Alhamdulillah aku dah berjaya, so aku just share jawapan aku kat StackOverflow.com di sini, maaf sbb omputih ayaq sejuk, main copy paste ja ni...

After a lot of try & error of codes, I finally manage to achieve what I need, the Excel file will be protected+cannot press F12 key+cannot press Ctrl+S key+cannot press Ctrl+P key anymore, the updated VBA code also can prevent user from opening Visual Basic editor if they wish to disable the code but in my case I leave it accessible for now. Refer my updated VBA code below :

    //Excel file will be password protected+cannot press F12 key+cannot press Ctrl+S key+cannot press Ctrl+P key anymore
    strVBCode = "Private Sub Workbook_Open()\r\n" +
              "     msgbox \"This document is protected!\"\r\n" +
              "     application.ExecuteExcel4Macro \"show.toolbar(\"\"Ribbon\"\",false)\"\r\n" +
              "     ThisWorkbook.Saved = True\r\n" + 
              "     Application.OnKey \"^s\", \"\"\r\n" + 
              "     Application.OnKey \"^p\", \"\"\r\n" + 
              //Enable this line if you wish to block Visual Basic Editor
              //"     Application.OnKey \"%{F11**\", \"\"\r\n" + 
              "End Sub\r\n" +
              "Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)\r\n" +
              "     If (SaveAsUI = true) Then\r\n" +
              "         msgbox \"You are not allowed to save this document!\"\r\n" +
              "         Cancel = True\r\n" +
              "     Else\r\n" + 
              "         ThisWorkbook.Saved = True\r\n" + 
              "         Application.OnKey \"^s\", \"\"\r\n" + 
              "         Application.OnKey \"^p\", \"\"\r\n" + 
              //Enable this line if you wish to block Visual Basic Editor
              //"     Application.OnKey \"%{F11**\", \"\"\r\n" + 
              "     End If\r\n" +
              "End Sub";

What I notice from my previous code, command(VBA code) Cancel=True in Private Sub Workbook_BeforeSave will prevent command(C#) wbkExcel.Save() from running because the VBA code will return false for C# code in save process. I hope someone can help and correct me if I'm wrong here. Hope this one will help the others too. Thanks StackOverflow.com.

 

[jomBiz]

..rasa nye bagi excel add-in. pon bole.

tapi kenapa tak bagi user save excel file,
sedangkan file ni disuruh gune..