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

52028

¦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¤Æµ{¦¡

¤U¸ü (20.1 KB)

2015-4-30 05:04

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