[µo°Ý] §R°£«ü©w¸ê®Æ§¨¤¤©Ò¦³¥¨¶°

PJChen

½Ð±Ð°ª¤â:
VBA TEST 6_§R°£©Ò¦³¥¨¶°.zip (281.2 KB)

¤U¸ü¦¸¼Æ: 14

2012-6-8 16:37

§Ú­n¦bCancel VBA.xlsmªºE6Àx¦s®æ¤¤«ü©w¤@­Ó¸ê®Æ§¨¦WºÙ¬°"§R°£©Ò¦³¥¨¶°",
1) ¥u­n¬O©ñ¦b³o­Ó¸ê®Æ§¨¤¤ªº©Ò¦³ÀÉ®×,¤@«ß§R°£©Ò¦³¥¨¶°(¤£½×EXCELÀɮפ¤¦³¦h¤Ö¥¨¶°)
2) ¸ê®Æ§¨¤¤ªºEXCELÀÉ®×Ãþ«¬¦³.xls/.xlsm

³o¼Ëªº­n¨D¬O§_¥i¼g¦¨¤@­Ó¥¨¶°?

Hsieh

¦^´_ 1# PJChen
°Ñ¦Ò¥Îµ{¦¡¼gµ{¦¡ (VBProject / VBComponents)

1. Sub Try()
   
2. fd = ThisWorkbook.Path & "\" & Sheets(1).[E6] & "\"
   
3. fs = Dir(fd & "*.xlsm")
   
4. Do Until fs = ""
   
5. With Workbooks.Open(fd & fs)
   
6. For Each vbc In .VBProject.VBComponents
   
7.   Select Case vbc.Type
   
8.   Case vbext_rk_Project, vbext_wt_Browser, vbext_ct_MSForm 'µù
   
9.     .VBProject.VBComponents.Remove .Item(vbc.Name)
   
10. 
    
11.   Case Else
    
12.     .VBProject.VBComponents(vbc.Name).CodeModule.DeleteLines 1, _
    
13.     .VBProject.VBComponents(vbc.Name).CodeModule.CountOfLines
    
14. 
    
15.   End Select
    
16. Next
    
17. .Close 1
    
18. End With
    
19. fs = Dir
    
20. Loop
    
21. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 1# PJChen
¥t¦s¬° 2007µL¥¨¶°®æ¦¡ªº¬¡­¶Ã¯ "xlsx"

1. Sub Ex()
   
2.     Dim xlPath As String, xlFile As String      'String: ¸ê®Æ«¬ºA
   
3.     Application.DisplayAlerts = False           '°±¤î¨t²Îªº´£¥Ü
   
4.     xlPath = ThisWorkbook.Path & "\"            'ThisWorkbook.Path: µ{¦¡½X¬¡­¶Ã¯©Ò¦sÀɪº¸ô®|;  §A¥i­×§ï¬°«ü©wªº¸ô®|
   
5.     xlFile = Dir(xlPath & "*.xlsm")             '«ü©wªº¸ô®|¤¤ ´M§ä°ÆÀɦW"xlsm"ªºÀÉ®×
   
6.     Do While xlFile <> ""                        '³B¸Ì §ä¨ì"xlsm"ªºÀÉ®×
   
7.         With Workbooks.Open(xlPath & xlFile)    '¶}±Ò³oÀÉ®×
   
8.             .SaveAs Filename:=Mid(xlPath & xlFile, 1, Len(xlPath & xlFile) - 1) & "x", FileFormat:=51
   
9.                                                 '³oÀÉ®×¥t¦s¬° 2007µL¥¨¶°®æ¦¡ªº¬¡­¶Ã¯ "xlsx"
   
10.             .Close                              'Ãö³¬ÀÉ®×
    
11.         End With
    
12.         xlFile = Dir                            '«ü©wªº¸ô®|¤¤Ä~Äò´M§ä°ÆÀɦW"xlsm"ªºÀÉ®×
    
13.     Loop
    
14.     If Dir(xlPath & "*.xlsm") <> "" Then Kill xlPath & "*.xlsm"
    
15.                                                 '§R°£«ü©wªº¸ô®|¤¤©Ò¦³°ÆÀɦW"xlsm"ªºÀÉ®×
    
16.     Application.DisplayAlerts = True            '«ì´_¨t²Îªº´£¥Ü
    
17. End Sub

½Æ»s¥N½X

OFFICE±M®a

¬Ý¨ì³o°ÝÃD·Q°_¦h¦~«eZZª©¥S¦bOFFICEºë­^
¤]°Ý¹L¦P¼Ë°ÝÃD.¨º®É«áStartUp­è¥X²{¨S¦h¤[

¤£¹L­è­n¶iOFFICEºë­^...¤´¶i¤£¥h...OFFICEºë­^¦³¦n´X¤Ñ¶i¤£¥h¤F
¤W¦¸OFFICEºë­^¶i¤£¥h¬O2004¦~ªº®É­Ô¤F.µ²ªG¬O¥D¾÷Ãa±¼.©Ò¦³¤å³¹°h¦^1­Ó¤ë«e

PJChen

¦^´_ 2# Hsieh
ª©¤j,
¦bµ{¦¡°õ¦æ¹Lµ{¤¤,¥¦·|¤@ª½¥X²{¹ï¸Ü®Ø°Ý¬O§_§ó·s³sµ²?§Ú·Q¨ì¤§«e¤j¤jµ¹ªº¤G¥y¸Ü(ÂŦâ¦r),´N±N¥¦¥[¦b³o­Óµ{¦¡¤¤,¦ý°õ¦æ®É°±¦bWith Workbooks.Open(fd & fs),¬O§_«e­z¤G¥y¸Ü©ñ¿ù¦ì¸m¤F?
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False

1. Sub Try()
   
2. fd = ThisWorkbook.Path & "\" & Sheets(1).[E6] & "\"
   
3. fs = Dir(fd & "*.xlsm")
   
4. fs = Dir(fd & "*.xls")
   
5. Do Until fs = ""
   
6. Application.DisplayAlerts = False
   
7. Application.AskToUpdateLinks = False
   
8. With Workbooks.Open(fd & fs)
   
9. For Each vbc In .VBProject.VBComponents
   
10.   Select Case vbc.Type
    
11.   Case vbext_rk_Project, vbext_wt_Browser, vbext_ct_MSForm 'µù
    
12.     .VBProject.VBComponents.Remove .Item(vbc.Name)
    
13. 
    
14.   Case Else
    
15.     .VBProject.VBComponents(vbc.Name).CodeModule.DeleteLines 1, _
    
16.     .VBProject.VBComponents(vbc.Name).CodeModule.CountOfLines
    
17. 
    
18.   End Select
    
19. Next
    
20. .Close 1
    
21. End With
    
22. fs = Dir
    
23. Loop
    
24. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 5# PJChen


    ¶}±ÒÀɮפ£§ó·s³sµ²

1. Sub Try()
   
2. fd = ThisWorkbook.Path & "\" & Sheets(1).[E6] & "\"
   
3. fs = Dir(fd & "*.xlsm")
   
4. fs = Dir(fd & "*.xls")
   
5. Do Until fs = ""
   
6. Application.DisplayAlerts = False
   
7. With Workbooks.Open(fd & fs, False)  ' ¶}±ÒÀɮפ£§ó·s³sµ²
   
8. 'With Workbooks.Open(fd & fs, True)  ' ¶}±ÒÀɮקó·s³sµ²
   
9. 
   
10. For Each vbc In .VBProject.VBComponents
    
11.   Select Case vbc.Type
    
12.   Case vbext_rk_Project, vbext_wt_Browser, vbext_ct_MSForm 'µù
    
13.     .VBProject.VBComponents.Remove .Item(vbc.Name)
    
14. 
    
15.   Case Else
    
16.     .VBProject.VBComponents(vbc.Name).CodeModule.DeleteLines 1, _
    
17.     .VBProject.VBComponents(vbc.Name).CodeModule.CountOfLines
    
18. 
    
19.   End Select
    
20. Next
    
21. .Close 1
    
22. End With
    
23. fs = Dir
    
24. Loop
    
25. End Sub

½Æ»s¥N½X

PJChen

¦^´_ 6# Hsieh
½Ð°Ý¤j¤j,
­Y¬O«Ý§R°£¥¨¶°ªºÀɮפ¤¦³¨Ç¬O§¹¥þ¨S¦³¥¨¶°ªº(µ{¦¡·|°±¤U¨Ó),§Ú­nÅý"§R°£¥¨¶°"ªºµ{¦¡©¿²¤¥¦,Ä~Äò°õ¦æ«ü¥O§R°£¨ä¥¦¦³¥¨¶°ªºÀÉ®×,
§ÚÀ³¸Ó¥[¤J¤°»ò»y¨¥(¥[¦b¤°»ò¦a¤è)?¤~¯à¹F¦¨§Ú·Q­nªº?

PJChen

¦^´_ 6# Hsieh

¦A¸É¥R°ÝÃD,µ{¦¡°õ¦æ®É¬O§_·|¦]¬°Â²Åé¦r¦ÓµLªk°õ¦æ,¹³¥H¤UªºÀɦW,¥¦´N¥X²{¤F³o¼Ëªº°T®§:

¤U¸ü (34.74 KB)

2012-6-12 08:45

6¤ë11¤é¥X车ªí.xls

Hsieh

¦^´_ 8# PJChen
1. ¨ÃµL§A©Ò»¡·íµLµ{¦¡½XÀÉ®×°õ¦æ®É·|°±¤îªº²{¶H
2. ­n¥¿½TÅã¥ÜÀɦW¡A¥u¯à­É¥ÎÀx¦s®æÅã¥Ü

1. Sub Try()
   
2. Dim fs
   
3. Application.DisplayAlerts = False
   
4. fd = ThisWorkbook.Path & "\" & Sheets(1).[E6] 'Àɮץؿý
   
5. Set fos = CreateObject("Scripting.FileSystemObject")
   
6. Set fdn = fos.getfolder(fd)
   
7. Set fc = fdn.Files 'Àɮץؿý¤¤©Ò¦³ÀÉ®×
   
8. For Each fs In fc
   
9. [A1] = fs.Name '­É¥ÎÀx¦s®æÅã¥Ü¥¿½TÀɦW
   
10. With Workbooks.Open(fd & "\" & [A1].Text, 0) ' ¶}±ÒÀɮפ£§ó·s³sµ²
    
11. For Each vbc In .VBProject.VBComponents
    
12.   Select Case vbc.Type
    
13.   Case 100
    
14.     .VBProject.VBComponents(vbc.Name).CodeModule.DeleteLines 1, _
    
15.     .VBProject.VBComponents(vbc.Name).CodeModule.CountOfLines '§R°£©Ò¦³µ{¦¡½X
    
16.   Case Else
    
17.     .VBProject.VBComponents.Remove .VBProject.VBComponents.Item(vbc.Name) '²¾°£¼Ò²Õ¡Bªí³æ¡BÃþ§O¼Ò²Õ
    
18.   End Select
    
19. Next
    
20. .Close 1
    
21. End With
    
22. Next
    
23. End Sub

½Æ»s¥N½X

PJChen

¦^´_ 9# Hsieh
¤]³\§Ú»~¥H¬°Â²Åé¦rÀɦW¬°µL¥¨¶°«K·|°±¤U¨Ó,²{¦b´ú¸Õ¥¼µo²{¦³°ÝÃD¤F,¤£¹L·Q½Ð±Ð,¬°¦ó±NÀɦWÅã¥Ü¦b[A1]Àx¦s®æ,¬°¦ó¥¦´N¥i¥H±µ¨ü²Åé¦r¤F?