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

[µo°Ý] ½Ð°Ý¦U¦ì«e½úÃö©ó¦h­Óexcel³]©wÄæ¦ì°ÝÃD

[µo°Ý] ½Ð°Ý¦U¦ì«e½úÃö©ó¦h­Óexcel³]©wÄæ¦ì°ÝÃD

¤£¦n·N«ä¡B½Ð°Ý«e½ú­Ì¤@¤U~
¤p§Ì¦¹vba¥\¯à¬°°w¹ï¦P¤@­Ó¸ê®Æ§¨¤Uªº©Ò¦³excel¤¤ªº(Sheet1)ªº²Ä9~14Äæ¦ì¶i¦æ³]©wÄæ¦ì¤j¤p¬°(13.5)¡A
¦ý¦]¥y¶°¤¤"Äæ¦ìÂà´«"¨Ï¥Îopen¤è¦¡¶i¦æ­×§ï¡B¬G¦pªG¤p§Ìªºexcel¦³¶W¹L50µ§¥H¤W«h»Ýªá¤W¤£¤Ö®É¶¡¡A
¯à§_½Ð°Ý«e½ú¡B¤p§Ìªº¤º®e¬O§_¦³¨ä¥¦¤è¦¡¥i¶i¦æ­×§ï©O¡H


µù¡GSheet1¬°©T©w³B²z¤À­¶

Âà´«testÀÉ.rar (19.19 KB)
·PÁÂ~

µ{¦¡½X¡G(¦³ÂIªø..)

Sub ¼Ð®×¦WºÙÂà´«()
   
    Sheets("Âà´«­¶").[a2:a65535].ClearContents
    Worksheets("Âà´«­¶").Cells(1, 2) = ActiveWorkbook.Path
    fs = Dir(Worksheets("Âà´«­¶").Cells(1, 2) & "\*.*")
Do Until fs = ""
r = r + 1
Worksheets("Âà´«­¶").Cells(r + 1, 1) = fs
fs = Dir
Loop

End Sub

Sub ¼Ð®×¦WºÙÂà´«1() '§R°£¨ä¥¦«DexcelÀÉ®×

Dim LastRow As Long, r As Long

    '§PÂ_³Ì«á¨Ï¥Îªº¦C¡A±N³o­Ó¦C¼Æ«ü¬£µ¹ LastRow ÅܼÆ

    LastRow = Worksheets("Âà´«­¶").UsedRange.Rows.Count

    'LastRow ªº­pºâ¤è¦¡¡G§PÂ_¨Ï¥Î½d³ò¤¤ªº¦C¼Æ¡A¥[¤W¨Ï¥Î½d³ò¤¤²Ä¤@­Ó¦C¼Æ¡A¦A´î¥h 1

    LastRow = LastRow + Worksheets("Âà´«­¶").UsedRange.Row - 1

    Application.ScreenUpdating = False

    '°j°é¨Ï¥Î Step -1 ·|¥Ñ¤U¦Ó¤W¶i¦æ³B²z¡A·í§R°£¦C¤§«á·|±N©Ò¦³¤U¤èªº¦C©¹¤W²¾°Ê¡C
   

   
    '¦¹°Ï¶ô¬°§R°£"«Dxl*ÀÉ"
    For r = LastRow To 2 Step -1

       If Worksheets("Âà´«­¶").Cells(r, 1) Like "*xl*" Then
       Else
       Worksheets("Âà´«­¶").Rows(r).Delete
       End If
    Next r
   
  '¦¹°Ï¶ô¬°§R°£"Âà´«testÀÉ "¦r¦C
    For r = LastRow To 2 Step -1

       If Worksheets("Âà´«­¶").Cells(r, 1) Like "Âà´«testÀÉ*" Then Worksheets("Âà´«­¶").Rows(r).Delete
    Next r


End Sub
Sub Äæ¦ìÂà´«()

    Dim wb(1 To 2) As Workbook
      Application.DisplayAlerts = False '¬O¥Î¨Ó²¤¹L§R°£®É´£¿ôªº¹ï¸Ü¤è¶ô, ­YÁÙ­n½T»{, «h¥i¤£¥Î¦¹«ü¥O
      
    Set wb(1) = ThisWorkbook
    For i = 2 To wb(1).Worksheets("Âà´«­¶").[a65536].End(3).Row
    Set wb(2) = Workbooks.Open(Worksheets("Âà´«­¶").Cells(1, 2) & "\" & Worksheets("Âà´«­¶").Cells(i, 1))
   
   
   
     wb(2).Worksheets("Sheet1").Rows("9:14").Select
        Selection.RowHeight = 13.5
            
        
   

    wb(2).Close True

Next

Application.DisplayAlerts = True

    Set wb(1) = Nothing
    Set wb(2) = Nothing

End Sub

Sub ¸ê®ÆÁ`©I¥s()
    Call ¼Ð®×¦WºÙÂà´«
    Call ¼Ð®×¦WºÙÂà´«1
    Call Äæ¦ìÂà´«

End Sub

¥»©«³Ì«á¥Ñ luhpro ©ó 2014-9-20 04:50 ½s¿è
¤£¦n·N«ä¡B½Ð°Ý«e½ú­Ì¤@¤U~
¤p§Ì¦¹vba¥\¯à¬°°w¹ï¦P¤@­Ó¸ê®Æ§¨¤Uªº©Ò¦³excel¤¤ªº(Sheet1)ªº²Ä9~14Äæ¦ì¶i¦æ³] ...
ii31sakura µoªí©ó 2014-9-17 17:56

¥H¤U¬O­Ó¤Hªº²L¨£,§A¥i¥H°Ñ¦Ò¬Ý¬Ý:

1. °ò¥»¤W­n¹ïÀÉ®× "¤º®e" §@ÅܧóÀ³¸Ó³£¬O­n¥ý¶}Àɪº,
    °£«D§A¬O¥Î¦­´ÁDOS®É¥N§C¶¥¼g¤JºÏºÐ¸ê®Æªº¤è¦¡¨Óª½±µ¼g¤JºÏ­±,
    ¦ý³o¨Ã¤£·|³QWindows¨t²Î±µ¨ü,
    ¤]´X¥G·|³QÂk¤J¬O¯f¬rªº¦æ¬°.

2. µ½¥Î With...End With ¨Ó¨ú¥N»Ý "³Q­«½Æ¨Ï¥Î" ªº "ª«¥ó",
    µ½¥Î "ÅܼÆ" ¨Ó¨ú¥N»Ý "³Q­«½Æ¨Ï¥Î" ªº "­pºâµ²ªG",
    ¤×¨ä¬O For...Next ©Î Do...Loop ...µ¥°j°é¤¤¸g±`·|°Ñ¦Ò¨ìªºªF¦è,
    ³t«×¸û§Ö¤]¦³§Q©óµ{¦¡½Xªº¿ëÃÑ»P°£¿ù,
    ·íµM­Y¥u¨Ï¥Î¤@¦¸, ¨º´N§O¥Î¤F.

3. °£«D¥²»Ý­n¥Î¨ì(¨Ò¦p¨Ï¥ÎªÌ»Ý­n¬Ý¨ì, ©Î¬Y¨Ç»Ý­n°Ñ·Ó¨ì²{¥Îª«¥óªº±MÄÝ«ü¥O½X),
    §_«h Select ©Î Activate «ü¥O½Ð¾¨¶q¤Ö¥Î,
    ¨º·|®ö¶O¤@¨Ç«D¥²­nªº®É¶¡(¤×¨ä¬O¦b°j°é¤¤, ·|­«½Æ°õ¦æ«Ü¦h¦¸ªº±¡§Î).

4. µ{¦¡½X¥i¥H¾A·í¨Ï¥ÎÁY±Æ(¥ªºÝ¥[ªÅ®æ)»PªÅ¥Õ¦æ, ¼W¥[¿ëÃÑ»P°£¿ùªº«K§Q©Ê.

µ{¦¡­×§ï¦p¤U,¦]µL¸ê®Æ¥i¥H¥ý¦æ´ú¸Õµ²ªG,­Y¦³°ÝÃD¦A½Ð´£¥X:
  1. Sub ¼Ð®×¦WºÙÂà´«()
  2.   With Sheets("Âà´«­¶")
  3.     .[a2:a65535].ClearContents
  4.     .Cells(1, 2) = ActiveWorkbook.Path
  5.     fs = Dir(.Cells(1, 2) & "\*.*")
  6.     Do Until fs = ""
  7.       r = r + 1
  8.       .Cells(r + 1, 1) = fs
  9.       fs = Dir
  10.     Loop
  11.   End With
  12. End Sub

  13. Sub ¼Ð®×¦WºÙÂà´«1() '§R°£¨ä¥¦«DexcelÀÉ®×
  14.   Dim LastRow As Long, r As Long
  15.     '§PÂ_³Ì«á¨Ï¥Îªº¦C¡A±N³o­Ó¦C¼Æ«ü¬£µ¹ LastRow ÅܼÆ

  16.   With Worksheets("Âà´«­¶")
  17.     LastRow = .UsedRange.Rows.Count
  18.     'LastRow ªº­pºâ¤è¦¡¡G§PÂ_¨Ï¥Î½d³ò¤¤ªº¦C¼Æ¡A¥[¤W¨Ï¥Î½d³ò¤¤²Ä¤@­Ó¦C¼Æ¡A¦A´î¥h 1
  19.     LastRow = LastRow + .UsedRange.Row - 1
  20.     Application.ScreenUpdating = False
  21.     '°j°é¨Ï¥Î Step -1 ·|¥Ñ¤U¦Ó¤W¶i¦æ³B²z¡A·í§R°£¦C¤§«á·|±N©Ò¦³¤U¤èªº¦C©¹¤W²¾°Ê¡C
  22.     '¦¹°Ï¶ô¬°§R°£"«Dxl*ÀÉ"
  23.     For r = LastRow To 2 Step -1
  24.        If .Cells(r, 1) Like "*xl*" Then
  25.        Else
  26.          .Rows(r).Delete
  27.        End If
  28.     Next r
  29.   '¦¹°Ï¶ô¬°§R°£"Âà´«testÀÉ "¦r¦C
  30.     For r = LastRow To 2 Step -1
  31.       If .Cells(r, 1) Like "Âà´«testÀÉ*" Then .Rows(r).Delete
  32.     Next r
  33.   End With
  34. End Sub

  35. Sub Äæ¦ìÂà´«()
  36.   Dim lRows&
  37.   Dim wb As Workbook
  38.    
  39.   Application.DisplayAlerts = False '¬O¥Î¨Ó²¤¹L§R°£®É´£¿ôªº¹ï¸Ü¤è¶ô, ­YÁÙ­n½T»{, «h¥i¤£¥Î¦¹«ü¥O
  40.   With ThisWorkbook.Worksheets("Âà´«­¶")
  41.     lRows = .[a65536].End(3).Row
  42.     For i = 2 To lRows
  43.       Set wb = Workbooks.Open(.Cells(1, 2) & "\" & .Cells(i, 1))
  44.        wb.Worksheets("Sheet1").Rows("9:14").RowHeight = 13.5
  45.       wb.Close True
  46.     Next
  47.   End With
  48.   Application.DisplayAlerts = True
  49.   Set wb = Nothing
  50. End Sub

  51. Sub ¸ê®ÆÁ`©I¥s()
  52.     Call ¼Ð®×¦WºÙÂà´«
  53.     Call ¼Ð®×¦WºÙÂà´«1
  54.     Call Äæ¦ìÂà´«
  55. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# luhpro

·PÁÂluhpro«e½ú²Ó¤ß«ü¾É¡B³o­Ó¤è¦¡°õ¦æ°_¨Ó¸ò­ì¥»ªº°õ¦æ³t«×¬Û®t¦n´X­¿..¤×¨ä¬O¦b¦hÀÉ®×±¡ªp¤U®É¶¡®t¶Z§ó©úÅã¡A
·PÁ«e½úªºÀ°¦£«ü¾É~

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD