Excel VBA automatically adds a certificate

---restore content starts---

Before I talk about this topic, let me explain why I want to add a digital certificate signature, what it does, and then explain how. First explain the location where the certificate was added, as shown in:

1. Click the Office button in the upper-left corner and select the Excel option in the lower-right corner.

Pop-up the interface shown below, select common items, and the Ribbon displays the Developer tab (D) option box selected.

You can then see the "Development Tools" menu item in the item, which is not displayed by default.

Then click the Visual Basic toolbar, as shown below

When opened, the following interface is displayed, that is, the IDE that writes macro code, the previous VB6.0 development interface

Select the Tools menu in the menu item, select the submenu item digital signature

The following dialog box appears, with the first copy signed without a certificate, and the second copy contains the certificate signature.

After you have signed the certificate, you can set the following options to set the macro access for the Trust Center

The following interface is displayed:

Good, the relevant knowledge has been introduced, and now go to how to automatically implement the certificate signature through code, rather than manually choose a certificate, so it is too case, like the QA test, manual know what step by step, but to automate to test, it will be more trouble. At that time several colleagues together to think a good solution, finally found a more reliable. Let's start with a step-by-step introduction.

Create a new Excel file, then modify the suffix name to zip before extracting it

After extracting it, you will see the following folder:

Open them up and see what the heck is:

This is the contents of the _rels folder. rels file:

The contents of the App.xml and core.xml files under this Docprops folder:

Contents of this core.xml file:

The subdirectories and files under the XL folder

The following is the contents of the sheet1.xml below the Worksheets folder:

Well, through the above analysis, I don't know if you can find out that the storage of Excel is stored by a system of XML files or other files to store information about it. If this is the case, it can be manipulated.

These files go to work indirectly with Excel. To verify that this is the case, then find an Excel file that contains the macro code, modify the suffix name, and then unzip it to see what the file is inside it:

There are two more files under the XL folder Vbaproject.bin and Vbaprojectsignature.bin, see this name is VBA project signed with VBA project

Open the _rels folder and see if there are any new things in it:

Well, the previous conjecture is correct, the Excel2007 after the storage used in a different way than before, with the binary, which is certainly not good expansion

Later is how to operate these files to achieve the desired operation, and later selected a more mature epplus, it can be very good to operate the relevant operations in Excel, well,

This time first to this, the stomach starved to death, the next time to specifically talk about the implementation of code.

Excel VBA automatically adds a certificate