[¤À¨É] ¥Îµ{¦¡¼gµ{¦¡ (VBProject / VBComponents)

Hsieh

§@ªÌ:leonchou
¤@­Ó²³æªº¨Ò¤l¡G¦b¬YXLSÀÉ¥[¤JWorkbook_Open¨Æ¥ó

Sub ggg()
Dim wb As Excel.Workbook
Set wb = GetObject("C:\test.xls")
With wb.VBProject.VBComponents("ThisWorkbook").CodeModule
¡@.InsertLines 1, "Private Sub Workbook_Open()"
¡@.InsertLines 2, "Msgbox ""This is a Test."""
¡@.InsertLines 3, "End Sub"
End With
wb.Close SaveChanges:=True
Set wb = Nothing
End Sub

VBComponent ¬O¤@­ÓVBA±M®×¤¤ªº¤¸¥ó¡A
¤¸¥ó¥i¥H¬Oªí³æ(UserForm)¡B¼Ò²Õ(Module)©Îª«¥óÃþ§O¡C
¦¹¨Ò§Y¬O¦b ThisWorkbook ¼Ò²Õ¼g¤Jµ{¦¡¡C

CodeModule ª«¥ó¥Nªí¤@­Ó¤¸¥ó¤ºªºµ{¦¡½X¡A
­n¼g¤Jµ{¦¡´N­n¥Î¨ì CodeModule¡C
*******************************
¥H¤Uµ{¦¡·|§R°£Book2¤¤©Ò¦³µ{¦¡ --

Sub ddd()
Dim vbc As Object
With Workbooks("book2.xls").VBProject
For Each vbc In .VBComponents
¡@Select Case vbc.Type
¡@Case vbext_rk_Project, vbext_wt_Browser, vbext_ct_MSForm 'µù
¡@¡@.VBComponents.Remove .Item(vbc.Name)

¡@Case Else
¡@¡@.VBComponents(vbc.Name).CodeModule.DeleteLines 1, _
¡@¡@.VBComponents(vbc.Name).CodeModule.CountOfLines

¡@End Select
Next
End With
End Sub

µù¡GVBComponent.Type
vbext_rk_Project ¼Ò²Õªº¶°¦X(Module) Type =1
vbext_wt_Browser ª«¥óÃþ§O(Class1) Type =2
vbext_ct_MSForm ¦Û­qªí³æ(UserForm) Type =3
**********************************
§R°£ Sheet1 ©Ò¦³ªº¥N½X¡G

Sub DelSheetCodes()
Dim cMdl As Object
Set cMdl = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
n = cMdl.CountOfLines
cMdl.DeleteLines 1, n
End Sub

³Ì«á¤G¦æ¥ç¥iª½±µ¼g¦¨
cMdl.DeleteLines 1, cMdl.CountOfLines
**********************************
¨Ò¦p§A¤w±q¥t¤@¬¡­¶Ã¯¶×¥XModule2ªºµ{¦¡½X©ó C:\module2.bas
¥H¤U±N module2.bas ¶×¤J¥Ø«eworkbook¡A
·|²£¥Í¤@­Ó Module2 ¼Ò²Õ¤Î¨äµ{¦¡½X --

Sub ImpCodes()
ActiveWorkbook.VBProject.VBComponents.Import "C:\module2.bas"
End Sub