How to add/Delete lines of code to a "ThisWorkbook" of excel file using vb.net?

Question

As per my application I want to write some Lines code in "ThisWorkbook" of Excel file using vb.net,before that we need to check the file for existance of code.

Please let me know any code or links for reference..

thank you...

Answer 1

The only thing I can find that would do this is ThisWorkbook.VBProject.VBComponents.Count which counts the number of Modules in your VB solution, which is Sheets + ThisWorkbook + anything additional. I can't find anything that would let you do a diff of the code.

Are you trying to do a security check of some sort? If random code was being inserted into your workbooks, wouldn't a black hat delete this coded in check? Why not just use signatures and digitally sign it?

Answer 2

It's possible but it's also very likely that the user's macro security settings will prevent this from working initially.

To adjust the security settings (all examples for Excel 2003):

(from a workbook): Tools > Macro > Security > Trusted Publishers

You now need to check the box which says "Trust access to Visual Basic project"

To read the code:

(from the VBA editor): Tools > References and add "Microsoft Visual Basic For Applications Extensibility 5.3" (the actual file is VBE6EXT.OLB)

To work out which VBProject is which, use the FileName property:

For Each vbpItem In Application.VBE.VBProjects
   If (vbpItem.FileName = "C:\foo.xls") Then
      Set vbpProject = vbpItem
   End If
Next vbpItem

Once you have the project, you can refer to the module by name:

vbpProject.VBComponents("ThisWorkbook")

and you can check how many lines there are like this:

If (vbpProject.VBComponents("ThisWorkbook").CodeModule.CountOfLines <> 147) Then

With the CodeModule object, you can read back specific lines (via the Lines property) and change lines (with the ReplaceLine method)