|
![](https://clubstatic.excelhome.net/image/common/ico_lz.png)
楼主 |
发表于 2009-4-1 16:02
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
5:EXCEL的COM对象的操作(演示在VB.NET中调用VBA过程);
EXCEL的COM对操作参见:
http://bingning.net/VB/SOURCE/programing/xlsclass.html
该页有许多VB.NET操作EXCEL的代码
在VB.NET中调用VBA过程(函数)可以加快程序的运行速度。
现演VB.NET创建VBA模块、过程,再调用后删除该过程.
Dim Excel As Microsoft.Office.Interop.Excel.Application
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Excel = New Microsoft.Office.Interop.Excel.Application
Excel.Workbooks.Add()
Excel.Visible = True
End Sub
Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
If Excel IsNot Nothing Then Excel.Quit()
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'开启VBA访问,并将VBA宏安全设置为高.
Dim Reg As Microsoft.Win32.RegistryKey = My.Computer.Registry.CurrentUser
'EXCEL 2003安全设置
Dim EReg As Microsoft.Win32.RegistryKey = Reg.CreateSubKey("Software\Microsoft\Office\11.0\Excel\Security")
EReg.SetValue("Level", "3", Microsoft.Win32.RegistryValueKind.DWord)
EReg.SetValue("DontTrustInstalledFiles", "0", Microsoft.Win32.RegistryValueKind.DWord)
EReg.SetValue("AccessVBOM", "1", Microsoft.Win32.RegistryValueKind.DWord)
EReg.Close()
'EXCEL 2007安全设置
EReg = Reg.CreateSubKey("Software\Microsoft\Office\12.0\Excel\Security")
EReg.SetValue("Level", "3", Microsoft.Win32.RegistryValueKind.DWord)
EReg.SetValue("DontTrustInstalledFiles", "0", Microsoft.Win32.RegistryValueKind.DWord)
EReg.SetValue("AccessVBOM", "1", Microsoft.Win32.RegistryValueKind.DWord)
EReg.Close()
Reg.Close() '关闭注册表
'在.NET开发环境中对系统的操作非常简单:
' 比如获得显示尺寸:
' My.Computer.Screen.Bounds
'播放声音文件:
' My.Computer.Audio.Play("c:\miues.wav", AudioPlayMode.Background)
'获得系统特殊文件夹:我的文档
' System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
Dim Vba As Microsoft.Vbe.Interop.VBComponent = Nothing
For xh As Int32 = 1 To Excel.VBE.ActiveVBProject.VBComponents.Count
If Excel.VBE.ActiveVBProject.VBComponents.Item(xh).Name = "NET" Then
Excel.VBE.ActiveVBProject.VBComponents.Remove(Excel.VBE.ActiveVBProject.VBComponents.Item(xh)) '删除该模块
Exit For
End If
Next
'添加 NET 模块。
Excel.VBE.ActiveVBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule).Name = "NET"
For xh As Int16 = 1 To Excel.VBE.ActiveVBProject.VBComponents.Count
If Excel.VBE.ActiveVBProject.VBComponents.Item(xh).Name = "NET" Then
Vba = Excel.VBE.ActiveVBProject.VBComponents.Item(xh)
End If
Next
Vba.CodeModule.AddFromString(My.Settings.VBA) '添加VBA代码,可以是多个过程或函数。
Vba.Activate()
Excel.Run("NET_VBA", 10, 10)'传递参数到该过程,最多30个,数组在VBA需声明为Variant类型。
'删除指定的过程。
If Vba.CodeModule.Find("NET_VBA", 0, 0, Vba.CodeModule.CountOfLines, 0, False, False, False) = True Then
Dim StarLins, EndLins As Int16
StarLins = Vba.CodeModule.ProcStartLine("NET_VBA", Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc)
EndLins = Vba.CodeModule.ProcCountLines("NET_VBA", Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc)
Vba.CodeModule.DeleteLines(StarLins, EndLins)
End If
End Sub
End Class
'VBA 的过程.
'Public Sub NET_VBA(ByVal X As Integer, ByVal Y As Integer)
' For xh = 1 To X
' For xy = 1 To Y
' Cells(xh, xy) = xh & "---" & xy
' Next
' Next
'End Sub
提供完整程序文件:
[ 本帖最后由 超音速自行车 于 2009-4-1 21:40 编辑 ] |
|