- 帖子
- 8
- 主題
- 3
- 精華
- 0
- 積分
- 56
- 點名
- 0
- 作業系統
- windows 7
- 軟體版本
- office2010
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2014-9-14
- 最後登錄
- 2016-1-8
|
VBA抓取網站資料
各位大家好，我最近在練習VBA
我想要在按下 Button 可以有以下程式的功能
程式功能是OK的，但是抓取時間很久有時還會無回應
還有再變更抓取之週期時，會有抓到舊週期的資料的問題(但多按幾次會就會正常)
想問問我要從哪邊著手優化程式
- Private Sub CommandButton2_Click()
- Range("A9:J168").Select
- ActiveWindow.SmallScroll Down:=-174
- Selection.ClearContents
- Selection.Clear
- Range("A8").Select
- '清除舊資料'
-
- With ActiveSheet.QueryTables.Add(Connection:= _
- "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) _
- , Destination:=Range("$A$10"))
- .Name = "16_24"
- .FieldNames = True
- .RowNumbers = False
- .FillAdjacentFormulas = False
- .PreserveFormatting = False
- .RefreshOnFileOpen = False
- .BackgroundQuery = False
- .RefreshStyle = xlOverwriteCells
- .SavePassword = False
- .SaveData = True
- .AdjustColumnWidth = False
- .RefreshPeriod = 0
- .WebSelectionType = xlSpecifiedTables
- .WebFormatting = xlWebFormattingNone
- .WebTables = "2"
- .WebPreFormattedTextToColumns = True
- .WebConsecutiveDelimitersAsOne = True
- .WebSingleBlockTextImport = False
- .WebDisableDateRecognition = False
- .WebDisableRedirections = False
- .Refresh BackgroundQuery:=False
- End With
- '抓取鉅亨網資料'
- Range("A10:J50").Select
- ActiveWindow.SmallScroll Down:=-51
- Selection.AutoFilter
- ActiveWorkbook.Worksheets("工作表2").AutoFilter.Sort.SortFields.Clear
- ActiveWorkbook.Worksheets("工作表2").AutoFilter.Sort.SortFields.Add Key:=Range( _
- "A10:A31"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
- xlSortNormal
- With ActiveWorkbook.Worksheets("工作表2").AutoFilter.Sort
- .Header = xlYes
- .MatchCase = False
- .Orientation = xlTopToBottom
- .SortMethod = xlPinYin
- .Apply
- End With
- '排序'
- Selection.AutoFilter
- ActiveWindow.SmallScroll Down:=-3
- Selection.ColumnWidth = 12
- Range("A8").Select
- '變更欄寬'
- End Sub
|
|
發表於 2015-4-30 05:10
| 
