---

Build macro for batch manipulate Excel Files

Now I have to add a security mark for many many excel files. I use the following the following VBA codes to achieve this.

1. Run Excel 2010, save the new blank file as "Excel Macro-Enabled Workbook", named "AddMark.xlsm" (via [File -> Save as]);

2. Open "Developer" ribbon: [File -> Options -> Customize ribbon], check "Developer" in right panel, press "OK" to close the dialog, you can see "Developer" ribbon now;

3. In "developer" ribbon, click "Visual Basic" to open the VBA IDE (or use shortcut "Alt-F11");

4. In visual basic IDE, add "Microsoft Scripting Run-time" and "Microsoft Scriptlet Library" in [Tools -> References] of main menu;

5. Add the following codes to [VBAProject -> Microsoft Excel Objects -> This Workbook]:

   Option Explicit Sub InsertMark2Excels() Dim fso As New FileSystemObject Dim folder As Object Set folder = fso.GetFolder(ActiveWorkbook.Path) LoopFolder folder Call WriteLog("Creation Date: " & Now) Call WriteLog("Log over.") End Sub Sub LoopFolder(FF As Scripting.folder) Dim F As Scripting.File Dim SubF As Scripting.folder Dim fso As New FileSystemObject For Each F In FF.Files Dim fext As String fext = fso.GetExtensionName(F) If fext = "xls" Or fext = "xlsx" Then InsertMark F.Path End If Next F For Each SubF In FF.SubFolders LoopFolder SubF Next SubF End Sub Sub InsertMark(target As String) Workbooks.Open target ActiveWorkbook.Sheets(1).Activate ActiveWorkbook.Sheets(1).Rows("1:1").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1").Select ActiveCell.FormulaR1C1 = "保密" ActiveWorkbook.Save ActiveWorkbook.Close Call WriteLog("Insert mark to " & target) End Sub Sub WriteLog(log As String) Open ActiveWorkbook.Path & "\insertmark.log" For Append As #1 Print #1, log Close #1 End Sub

6. Add shortcut for macro "InsertMark2Excels": in ribbon Developer -> Macro, then click "Options", assign Ctrl-d to this macro;

Now you can copy AddMark.xlsm to another host, open this file, press Ctrl-d to run the macro.

Open Office Macro in LibreOffice

1. Open xlsm file with LibreOffice Calc, or simply thunar filename.xlsm;

2. Tools -> Macros -> Organize Macros -> LibreOffice Basic, select [AddMark.xlsm -> VBA Project -> Document Objects -> ThisWorkbook] in "Macro from", then select a macro in "Existing macros", then click "Edit" button.

---

---