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

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

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


§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)

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

TOP

·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ªºÄæ¦ì

TOP

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

TOP

@@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¼Ú

TOP

¦^´_ 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. '¥i¿ï¾Ü¦h­ÓÀÉ®×
  4. Source = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx),*.xls;*.xlsx", _
  5.                                     MultiSelect:=True)
  6. If TypeName(Source) = "Boolean" Then If Source = False Then Exit Sub

  7. For Each f In Source
  8. '¶}±ÒÀÉ®×/¬¡­¶Ã¯
  9. With Workbooks.Open(f)
  10.     '¹ï©Ò¦³¤u§@ªí
  11.     For i = 1 To ActiveWorkbook.Sheets.Count
  12.         '½Æ»s¤u§@ªí¨ì¥»¬¡­¶Ã¯
  13.         .Sheets(i).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
  14.         '¥»¬¡­¶Ã¯¤¤¸Ó¤u§@ªí
  15.         With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
  16.            '¨ÌE,FÄæ±Æ§Ç
  17.            .[A1].CurrentRegion.Sort Key1:=.[E:E], Order1:=xlAscending, _
  18.                                     Key2:=.[F:F], Order2:=xlAscending, _
  19.                                     Header:=xlYes
  20.            '§R°£¤£­nªºÄæ
  21.            .Range("A:C,E:I,K:L,Q:Q").Delete Shift:=xlToLeft
  22.         End With
  23.     Next i
  24.     'Ãö³¬ÀÉ®×
  25.     .Close
  26. End With
  27. Next f
  28. End Sub
½Æ»s¥N½X

TOP

·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¦ì)

TOP

¦^´_ 7# billchenfantasy
  1. Private Sub CommandButton1_Click()
  2. Dim Source, f
  3. Dim rng As Range

  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. For Each f In Source
  9. '¶}±ÒÀÉ®×/¬¡­¶Ã¯
  10. With Workbooks.Open(f)
  11.     '¹ï©Ò¦³¤u§@ªí
  12.     For i = 1 To ActiveWorkbook.Sheets.Count
  13.         '½Æ»s¤u§@ªí¨ì¥»¬¡­¶Ã¯
  14.         .Sheets(i).Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
  15.         '¥»¬¡­¶Ã¯¤¤¸Ó¤u§@ªí
  16.         With ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
  17.            '¨Ìland_no_m, land_no_cÄæ±Æ§Ç
  18.             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
  19.             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
  20.             
  21.             '§ä¥X¤£²Å¦XªºÄæ
  22.             For j = 1 To .[A1].CurrentRegion.Columns.Count
  23.                 If IsError(Application.Match(.Cells(1, j).Value, Array("section", "SC", "LANDUSE", "PUBNO", "OPTION", "METHOD", "MUPLAN", "DUPLAN", "ORG_FID"), 0)) Then
  24.                     If rng Is Nothing Then Set rng = .Columns(j) Else Set rng = Union(rng, .Columns(j))
  25.                 End If
  26.             Next j
  27.             '§R°£
  28.             .Range(rng.Address).Delete shift:=xlToLeft
  29.             Set rng = Nothing
  30.         End With
  31.     Next i
  32.     'Ãö³¬ÀÉ®×
  33.     .Close
  34. End With
  35. Next f
  36. End Sub
½Æ»s¥N½X

TOP

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

TOP

¹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

TOP

        ÀR«ä¦Û¦b : ¯àµ½¥Î®É¶¡ªº¤H¡A¥²¯à´x´¤¦Û¤v§V¤Oªº¤è¦V¡C
ªð¦^¦Cªí ¤W¤@¥DÃD