如何添加/删除代码行到“ThisWorkbook”的excel文件使用vb.net？ [英] How to add/Delete lines of code to a "ThisWorkbook" of excel file using vb.net?

问题描述

根据我的应用程序，我想使用vb.net在Excel文件的ThisWorkbook中编写一些Lines代码，之后我们需要检查文件是否存在代码。

/ p>

解决方案

这是可能的，但是用户的宏安全设置也很可能会阻止它最初的工作。

调整安全性设置（Excel 2003的所有示例）：

（工作簿）：工具>宏>安全性>受信任的发布者

您现在需要选中信任访问Visual Basic项目的框。

要阅读代码：

（从VBA编辑器）：工具>引用并添加Microsoft Visual Basic应用程序可扩展性5.3（实际文件是VBE6EXT.OLB）

要计算出哪个 VBProject 是哪个，使用 FileName property：

 对于每个vbpItem在Application.VBE.VBProjects 
如果（vbpItem.FileName =C： \foo.xls）然后
设置vbpProject = vbpItem 
结束如果
下一个vbpItem 
  

一旦你有项目，你可以通过名称来引用这个模块：

vbpProject.VBComponents（ThisWorkbook ）

，你可以查看这样的行数：

If（vbpProject.VBComponents（ThisWorkbook）。CodeModule.CountOfLines<> 147）然后

使用 CodeModule 对象，您可以读回特定行通过 Lines 属性）和更改行（使用 ReplaceLine 方法）

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...

解决方案

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)

这篇关于如何添加/删除代码行到“ThisWorkbook”的excel文件使用vb.net？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！