[µo°Ý] ±Æ§Ç«á§R°£¯S©wÄd¦ì

billchenfantasy

¤U¸ü (68.14 KB)

2013-1-22 15:53

§e¤W¸ê®Æ­¶­±¡A½Ð°Ý¦pªG§Ú­n¥ý¥Hland_no_m©Mland_no_c¨âÄd¬°·Ç°µ±Æ§Ç¤§«á«O¯d¥H¤U³o¤E­ÓÄæ¦ì¨ä¥L§R°£¸Ó«ç»ò¼¶¼gVBA©O~·PÁÂ
(section) (SC) (LANDUSE)  (PUBNO)        (OPTION)        (METHOD)        (MUPLAN)        (DUPLAN)        (ORG_FID)

¤U¸ü (20.85 KB)

2013-1-22 16:05

stillfish00

¦^´_ 1# billchenfantasy
¨Ì§A±Ô­zªº»Ý¨DÀ³¸Ó¥Î¤@¯ëExcel¾Þ§@´N¥i¹F¦¨:
¥ý±N¤£¥²­nªºÄæ§R°£ , ¦A¨ì  ¸ê®Æ>±Æ§Ç(excel 2010)  ³]©w§Y¥i

billchenfantasy

·PÁ±zªº¦^µª¨ä¹ê¬O¦]¬°Àɮ׫ܦh¸ê®Æªø«×½d³ò¤£¤@©w¡A·Q¶}©l¾Ç²ßVBA©Ò­P¡A§Ú¦Û¤v¨ä¹ê¦³¥ý¥Î¿ý»s¥¨¶°ªº¤è¦¡¸Õ¹L¦p¤U¨Ã³]¬°¨â­Ó«ö¶s¡A·s¤â¾Ç²ß½Ð±z¥]§t
Private Sub CommandButton1_Click()
Dim uFile$
ChDrive "¸ê®ÆºÏºÐ"
ChDir "¸ê®Æ¸ô®|"
Source = Application.GetOpenFilename
With Workbooks.Open(Source)
   For i = 1 To ActiveWorkbook.Sheets.Count
    .Sheets(i).Copy after:=ThisWorkbook.Worksheets(Sheets.Count)
   Next i
  .Close
End With
End Sub
¥H¤W¬O±N¸ê®Æ¶×¤J¨Ã¶}·sªº¤u§@ªí(¦P®É¦WºÙ¬°¸Ó¸ê®ÆÀÉ)½Ð°Ý­n¦p¦ó³]¸m¦Û°Ê©R¦W¬°¦P¤@­Ó¤u§@ªí¦WºÙ?
----------------------------------------------------------------------------------------
Private Sub CommandButton2_Click()
Cells.Select
    Cells.EntireColumn.AutoFit
    Application.WindowState = xlMaximized
    ActiveWorkbook.Worksheets("¤u§@ªí¦WºÙ").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("¤u§@ªí¦WºÙ").Sort.SortFields.Add Key:=Range( _
        "land_no_mªº½d³ò"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    ActiveWorkbook.Worksheets("¤u§@ªí¦WºÙ").Sort.SortFields.Add Key:=Range( _
        "land_no_cªº½d³ò"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("¤u§@ªí¦WºÙ").Sort
        .SetRange Range("¸ê®Æ¥þ½d³ò")----->­n«ç»ò³]­pµL½×¸ê®Æ¦hªø³£¥i¥H¿ï°_©O?¬O(.Range("A1").CurrentRegion)¶Ü
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Application.Width = 831
    Application.Height = 810
    Columns("A:C").Select
    Range("C1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("B:F").Select
    Selection.Delete Shift:=xlToLeft
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
    Range("J1").Select
End Sub
-------------------------------------------------------------
¨ä¹ê§Ú¬O·Q«ü©w§R°£¤W­z¯S©w¼ÐÃDªºÄæ¦ì

stillfish00

¦^´_ 3# billchenfantasy
>> Private Sub CommandButton1_Click()
>> ¥H¤W¬O±N¸ê®Æ¶×¤J¨Ã¶}·sªº¤u§@ªí(¦P®É¦WºÙ¬°¸Ó¸ê®ÆÀÉ)½Ð°Ý­n¦p¦ó³]¸m¦Û°Ê©R¦W¬°¦P¤@­Ó¤u§@ªí¦WºÙ?

¤£©ú¥Õ§Aªº·N«ä , §A½Æ»s¤u§@ªí¹L¨Ó®É¦W¦rÀ³¸Ó¬O©M­ì¥»¤u§@ªí¤@¼Ëªº

>>Private Sub CommandButton2_Click()

¸Õ¸Õ¬Ý
Private Sub CommandButton2_Click()
With Sheets("¤u§@ªí¦WºÙ")
    .[A1].CurrentRegion.Sort Key1:=.[E:E], Order1:=xlAscending, _
                             Key2:=.[F:F], Order2:=xlAscending, _
                             Header:=xlYes
    .Range("A:C,E:I,K:L,Q:Q").Delete Shift:=xlToLeft
End With
End Sub

billchenfantasy

@@Private Sub CommandButton2_Click()
ªºOK¤F¹ï©ó±zªº¸Ñµª·PÁ¸U¤À¡A§Ú¨Ó¦n¦n¬ã¨s¤@¤U
Private Sub CommandButton1_Click()ªº·N«ä¬O»¡
"½Æ»s¤u§@ªí¹L¨Ó®É¦W¦rÀ³¸Ó¬O©M­ì¥»¤u§@ªí¤@¼Ëªº"¨S¿ù
¦]¬°§ÚªºÀɮצ³«Ü¦h¡A¦Ó¥BÁöµM¤º®e®æ¦¡¤@¼Ë¦ýÀɦW¤£¤@¼Ë¡A§Úªº·Qªk¬O­n«ç»ò¼Ë
§â¤£¦PÀɦWªºÀɮ׶׶i¨Ó¤§«á³£¥i¥H°õ¦æPrivate Sub CommandButton2_Click()¡A
¦]¬°«ü©wªºWith Sheets("¤u§@ªí¦WºÙ")¤u§@ªí¦WºÙ·|ÅÜ
µ¥³o­Ó³£¤F¸Ñ¤F±µ¤U¨Ó¦A·Q¤U¤@¨B«ç»ò§å¦¸
¦A¦¸ÁÂÁ§A¼Ú

stillfish00

¦^´_ 5# billchenfantasy
¦P¤@¬¡­¶Ã¯¬O¤£¯à¦³¦h­Ó¬Û¦P¦WºÙªº¤u§@ªí

§A­nªº§å¦¸³B²z¬O³o¼Ë¶Ü? (¥i¿ï¦h­ÓÀÉ®×)

1. Private Sub CommandButton1_Click()
   
2. Dim Source, f
   
3. 
   
4. '¥i¿ï¾Ü¦h­ÓÀÉ®×
   
5. Source = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx),*.xls;*.xlsx", _
   
6.                                     MultiSelect:=True)
   
7. If TypeName(Source) = "Boolean" Then If Source = False Then Exit Sub
   
8. 
   
9. For Each f In Source
   
10. '¶}±ÒÀÉ®×/¬¡­¶Ã¯
    
11. With Workbooks.Open(f)
    
12.     '¹ï©Ò¦³¤u§@ªí
    
13.     For i = 1 To ActiveWorkbook.Sheets.Count
    
14.         '½Æ»s¤u§@ªí¨ì¥»¬¡­¶Ã¯
    
15.         .Sheets(i).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
16.         '¥»¬¡­¶Ã¯¤¤¸Ó¤u§@ªí
    
17.         With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
18.            '¨ÌE,FÄæ±Æ§Ç
    
19.            .[A1].CurrentRegion.Sort Key1:=.[E:E], Order1:=xlAscending, _
    
20.                                     Key2:=.[F:F], Order2:=xlAscending, _
    
21.                                     Header:=xlYes
    
22.            '§R°£¤£­nªºÄæ
    
23.            .Range("A:C,E:I,K:L,Q:Q").Delete Shift:=xlToLeft
    
24.         End With
    
25.     Next i
    
26.     'Ãö³¬ÀÉ®×
    
27.     .Close
    
28. End With
    
29. Next f
    
30. End Sub

½Æ»s¥N½X

billchenfantasy

·PÁÂstillfish00 ³o¹ï§Ú³oµæ³¾¨Ó»¡¡A¬Ý°_¨Ó²ª½¬OÅÜÅ]³N¡A·P®¦¡A¬O±zªº¶×¤J¤è¦¡¤ñ¸û¦X²z¡A
.Range("A:C,E:I,K,Q").Delete Shift:=xlToLeft¬Oª½±µ¿ï©wÄæ¦ì
³Ì«á¤@­Ó°ÝÃD¬O©Ò¿×"§R°£¯S©wÄd¦ì"¥i¥H¬O«ü©w«O¯d(section) (SC) (LANDUSE) (PUBNO) (OPTION) (METHOD) (MUPLAN) (DUPLAN) (ORG_FID)¼ÐÃDªº¤@¾ã­ÓÄæ¦ì¡A¨ä¥LµL½×¬Æ»ò¼ÐÃDªºÄæ¦ì³£§R±¼ªº¼gªk¶Ü(¥Î¥H½T«O§å¦¸®É¬Y¤@­ÓÀɪº¨Ò¥~¦h¤@±øµL¶·«O¯dªºÄd¦ì)

stillfish00

¦^´_ 7# billchenfantasy

1. Private Sub CommandButton1_Click()
   
2. Dim Source, f
   
3. Dim rng As Range
   
4. 
   
5. '¥i¿ï¾Ü¦h­ÓÀÉ®×
   
6. Source = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx),*.xls;*.xlsx", _
   
7.                                     MultiSelect:=True)
   
8. If TypeName(Source) = "Boolean" Then If Source = False Then Exit Sub
   
9. 
   
10. For Each f In Source
    
11. '¶}±ÒÀÉ®×/¬¡­¶Ã¯
    
12. With Workbooks.Open(f)
    
13.     '¹ï©Ò¦³¤u§@ªí
    
14.     For i = 1 To ActiveWorkbook.Sheets.Count
    
15.         '½Æ»s¤u§@ªí¨ì¥»¬¡­¶Ã¯
    
16.         .Sheets(i).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
17.         '¥»¬¡­¶Ã¯¤¤¸Ó¤u§@ªí
    
18.         With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
19.            '¨Ìland_no_m, land_no_cÄæ±Æ§Ç
    
20.             If Not IsError(Application.Match("land_no_m", .Rows(1), 0)) Then .[A1].CurrentRegion.Sort Key1:=.Columns(Application.Match("land_no_m", .Rows(1), 0)), Order1:=xlAscending, Header:=xlYes
    
21.             If Not IsError(Application.Match("land_no_c", .Rows(1), 0)) Then .[A1].CurrentRegion.Sort Key1:=.Columns(Application.Match("land_no_c", .Rows(1), 0)), Order1:=xlAscending, Header:=xlYes
    
22.             
    
23.             '§ä¥X¤£²Å¦XªºÄæ
    
24.             For j = 1 To .[A1].CurrentRegion.Columns.Count
    
25.                 If IsError(Application.Match(.Cells(1, j).Value, Array("section", "SC", "LANDUSE", "PUBNO", "OPTION", "METHOD", "MUPLAN", "DUPLAN", "ORG_FID"), 0)) Then
    
26.                     If rng Is Nothing Then Set rng = .Columns(j) Else Set rng = Union(rng, .Columns(j))
    
27.                 End If
    
28.             Next j
    
29.             '§R°£
    
30.             .Range(rng.Address).Delete shift:=xlToLeft
    
31.             Set rng = Nothing
    
32.         End With
    
33.     Next i
    
34.     'Ãö³¬ÀÉ®×
    
35.     .Close
    
36. End With
    
37. Next f
    
38. End Sub

½Æ»s¥N½X

billchenfantasy

·P¿E~µù¸Ñ¸Ô²Ó~·Pı¤W¤F¤@½Ò@@ÁÂÁ±zªº¸Ñµª

billchenfantasy

¹J¨ì°ÝÃD¤F@@¦pªG»Ý­n¥Hland_no_m¬°KEY1»Pland_no_c¬°KEY2¥h°µ±Æ§Ç¥H¤U±Æ§Çªºµ{§Ç¸Ó¦p¦ó­×§ï£z

If Not IsError(Application.Match("land_no_m", .Rows(1), 0)) Then .[A1].CurrentRegion.Sort Key1:=.Columns(Application.Match("land_no_m", .Rows(1), 0)), Order1:=xlAscending, Header:=xlYes

  If Not IsError(Application.Match("land_no_c", .Rows(1), 0)) Then .[A1].CurrentRegion.Sort Key1:=.Columns(Application.Match("land_no_c", .Rows(1), 0)), Order1:=xlAscending, Header:=xlYes