How to add / remove lines of code to "ThisWorkbook" of excel file using vb.net?
It is possible, but it is very likely that the user's macro security settings will prevent this from getting started.
To configure security settings (all examples are for Excel 2003):
(from the tutorial): Tools> Macro> Security> Trusted Publishers
Now you need to check the box "Trust access to Visual Basic project"
To read the code:
(from VBA editor): Tools> References and add "Microsoft Visual Basic for Application Extensibility 5.3" (actual file is VBE6EXT.OLB)
To determine which one it VBProject
belongs to, use the property FileName
:
For Each vbpItem In Application.VBE.VBProjects
If (vbpItem.FileName = "C:\foo.xls") Then
Set vbpProject = vbpItem
End If
Next vbpItem
Once you have a project, you can refer to the module by name:
vbpProject.VBComponents("ThisWorkbook")
and you can check how many lines there are:
If (vbpProject.VBComponents("ThisWorkbook").CodeModule.CountOfLines <> 147) Then
With an object, CodeModule
you can read specific lines (via a property Lines
) and modify lines (via a method ReplaceLine
)
a source to share
The only thing I can find is to do this: ThisWorkbook.VBProject.VBComponents.Count which counts the number of modules in your VB solution which is a sheet + ThisWorkbook + something extra. I cannot find anything that would allow you to follow the code difference.
Are you trying to perform a security check? If random code were inserted into your books, wouldn't the black hat remove that encoding under control? Why not just use signatures and digital signatures?
a source to share