ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

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

§Ú­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¦¨¤@­Ó¥¨¶°?

VBA TEST 6_§R°£¸ê®Æ§¨¤¤¥¨¶°.zip (93.08 KB)

¦^´_ 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.   Case Else
  11.     .VBProject.VBComponents(vbc.Name).CodeModule.DeleteLines 1, _
  12.     .VBProject.VBComponents(vbc.Name).CodeModule.CountOfLines

  13.   End Select
  14. Next
  15. .Close 1
  16. End With
  17. fs = Dir
  18. Loop
  19. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-6-9 12:11 ½s¿è

¦^´_ 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

TOP

¬Ý¨ì³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
¤£®¢¤U°Ý,¤£Â_¾Ç²ß,¤~·|¶i¨B

TOP

¦^´_ 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.   Case Else
  14.     .VBProject.VBComponents(vbc.Name).CodeModule.DeleteLines 1, _
  15.     .VBProject.VBComponents(vbc.Name).CodeModule.CountOfLines

  16.   End Select
  17. Next
  18. .Close 1
  19. End With
  20. fs = Dir
  21. Loop
  22. End Sub
½Æ»s¥N½X

TOP

¦^´_ 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. 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.   Case Else
  14.     .VBProject.VBComponents(vbc.Name).CodeModule.DeleteLines 1, _
  15.     .VBProject.VBComponents(vbc.Name).CodeModule.CountOfLines

  16.   End Select
  17. Next
  18. .Close 1
  19. End With
  20. fs = Dir
  21. Loop
  22. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

¦^´_ 6# Hsieh

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

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-6-12 16:36 ½s¿è

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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?

TOP

        ÀR«ä¦Û¦b : ÁÀ¨¥¹³¤@¦·²±¶}ªºÂAªá¡A¥~ªí¬üÄR¡A¥Í©Rµu¼È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD