如何用au3将VBA代码写入到excel 中
要用au3创建电子表格,当点击表格中的某个单元格时,单元格颜色自动发生变化 以做标记发现VBA可以做到Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Interior.ColorIndex = 22 Then
Target.Interior.ColorIndex = 0
Else
Target.Interior.ColorIndex = 22
End If
End Sub双击单元格变颜色
要是每张表格都手动输入的话,也是相当的麻烦
有没有办法在au3创建excel的时候就把VBA代码写进去呢? Private Sub CommandButton1_Click()
Dim Wbk As Workbook
Set Wbk = Workbooks.Add
Dim w1 As String
'构造代码
w1 = "Private Sub Workbook_Open()"
w1 = w1 & vbCrLf & "MsgBox ""我是被克隆出来的,呵呵!"""
w1 = w1 & vbCrLf & "End Sub"
Dim VBC
For Each VBC In Wbk.VBProject.VBComponents
If VBC.Name = Wbk.CodeName Then
'删除所有代码
VBC.CodeModule.DeleteLines 1, VBC.CodeModule.CountOfLines
'插入新代码
VBC.CodeModule.InsertLines 1, w1
Exit For '跳出循环
End If
Next
'保存工作簿
'Wbk.SaveAs "c:\obs\obs1.xls"
Set Wbk = Nothing
End Sub
这是网上一段代码
转不成功 求指点
#include <Excel.au3>
Local $oExcel = _ExcelBookNew()
;~' Place code in a string.
$strCode = _
'Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)' & @CRLF & _
'If Target.Interior.ColorIndex = 22 Then' & @CRLF & _
'Target.Interior.ColorIndex = 0' & @CRLF & _
'Else' & @CRLF & _
'Target.Interior.ColorIndex = 22' & @CRLF & _
'End If' & @CRLF & _
'End Sub'
Dim $VBC
For $VBC In $oExcel.VBProject.VBComponents
If $VBC.Name = $oExcel.CodeName Then
$VBC.CodeModule.DeleteLines( 1, $VBC.CodeModule.CountOfLines)
;'插入新代码
$VBC.CodeModule.InsertLines(1, $strCode)
ExitLoop; '跳出循环
EndIf
Next
精神上支持你一下 先要修改一个注册表值,也就是在excel选项里有一个“信任对vb模块的访问”前面打个勾,通过监控注册表可以知道修改了哪里,然后才可以向vb模块写入代码 我来学习一下,也正在学EXCEL
页:
[1]