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

VBA§ì¨úºô¯¸¸ê®Æ

VBA§ì¨úºô¯¸¸ê®Æ

¦U¦ì¤j®a¦n¡A§Ú³Ìªñ¦b½m²ßVBA
§Ú·Q­n¦b«ö¤U Button ¥i¥H¦³¥H¤Uµ{¦¡ªº¥\¯à
µ{¦¡¥\¯à¬OOKªº¡A¦ý¬O§ì¨ú®É¶¡«Ü¤[¦³®ÉÁÙ·|µL¦^À³
ÁÙ¦³¦AÅܧó§ì¨ú¤§¶g´Á®É¡A·|¦³§ì¨ì¶g´Áªº¸ê®Æªº°ÝÃD(¦ý¦h«ö´X¦¸·|´N·|¥¿±`)
·Q°Ý°Ý§Ú­n±q­þÃäµÛ¤âÀu¤Æµ{¦¡

  1. Private Sub CommandButton2_Click()
  2.     Range("A9:J168").Select
  3.     ActiveWindow.SmallScroll Down:=-174
  4.     Selection.ClearContents
  5.     Selection.Clear
  6.     Range("A8").Select
  7.     '²M°£Â¸ê®Æ'
  8.    
  9. With ActiveSheet.QueryTables.Add(Connection:= _
  10. "URL;http://www.cnyes.com/twstock/ps_historyprice.aspx?code=" & Cells(1, 3) & "&ctl00$ContentPlaceHolder1$startText=" & Cells(2, 3) & "&ctl00$ContentPlaceHolder1$endText=" & Cells(3, 3) _
  11. , Destination:=Range("$A$10"))
  12. .Name = "16_24"
  13. .FieldNames = True
  14. .RowNumbers = False
  15. .FillAdjacentFormulas = False
  16. .PreserveFormatting = False
  17. .RefreshOnFileOpen = False
  18. .BackgroundQuery = False
  19. .RefreshStyle = xlOverwriteCells
  20. .SavePassword = False
  21. .SaveData = True
  22. .AdjustColumnWidth = False
  23. .RefreshPeriod = 0
  24. .WebSelectionType = xlSpecifiedTables
  25. .WebFormatting = xlWebFormattingNone
  26. .WebTables = "2"
  27. .WebPreFormattedTextToColumns = True
  28. .WebConsecutiveDelimitersAsOne = True
  29. .WebSingleBlockTextImport = False
  30. .WebDisableDateRecognition = False
  31. .WebDisableRedirections = False
  32. .Refresh BackgroundQuery:=False
  33. End With
  34. '§ì¨ú¹d¦ëºô¸ê®Æ'
  35.     Range("A10:J50").Select
  36.     ActiveWindow.SmallScroll Down:=-51
  37.     Selection.AutoFilter
  38.     ActiveWorkbook.Worksheets("¤u§@ªí2").AutoFilter.Sort.SortFields.Clear
  39.     ActiveWorkbook.Worksheets("¤u§@ªí2").AutoFilter.Sort.SortFields.Add Key:=Range( _
  40.         "A10:A31"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  41.         xlSortNormal
  42.     With ActiveWorkbook.Worksheets("¤u§@ªí2").AutoFilter.Sort
  43.         .Header = xlYes
  44.         .MatchCase = False
  45.         .Orientation = xlTopToBottom
  46.         .SortMethod = xlPinYin
  47.         .Apply
  48.     End With
  49.     '±Æ§Ç'
  50.     Selection.AutoFilter
  51.     ActiveWindow.SmallScroll Down:=-3
  52.     Selection.ColumnWidth = 12
  53.     Range("A8").Select
  54.     'ÅܧóÄæ¼e'
  55. End Sub
½Æ»s¥N½X

        ÀR«ä¦Û¦b : ¦n¨Æ­n´£±o°_¡A¬O«D­n©ñ±o¤U¡A¦¨´N§O¤H§Y¬O¦¨´N¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD