主題: [其他] [求助]Excel VBA的問題求解.. [打印本頁]
發表人:
XLOVER 時間: 2012-6-1 06:05 PM 主題: [求助]Excel VBA的問題求解..
以下是一段VBA程式，
是要把股票代號2330的資料從網頁把它讀到Excel裡。
不知道程式要怎麼寫，
才能讓那個代號"2330"的部分，
由固定，改成可以由我自行輸入，
然後套用進去。
還請路過的大大幫幫忙....
謝謝囉....
=====
Sub test1()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://dj.mybank.com.tw/z/zc/zcp/zcpa/zcpa_2330.asp.htm", Destination:=Range("$A$1"))
.Name = "test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
發表人:
陽だまり 時間: 2012-6-1 07:45 PM
名叫number的string
Dim number As String
number = "請輸入代號，空白預設為2330"
number = InputBox(number)
If number = "" Then
number = "2330"
End If
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://dj.mybank.com.tw/z/zc/zcp/zcpa/zcpa_" + number + ".asp.htm", Destination:=Range("$A$1"))
.Name = "test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
[陽だまり 在 2012-6-1 07:48 PM 作了最後編輯]
發表人:
XLOVER 時間: 2012-6-2 08:56 AM
非常感謝大大的幫忙喔....
^^
| 歡迎光臨 TWed2k (http://twed2k.org/) |
Powered by Discuz! 4.1.0 |