[發問] 活頁簿連線

想請教各位我自己參考版上資料寫了幾個簡單的查詢股票程式，
問題來了，我每按查詢一次，如圖的活頁簿連線就會多一筆，
等差不多10幾筆的時候，每次最後按儲存離開時，excel就當機，
所以我每次儲存前都要手動刪除所有連線，
不知有無語法可以每查詢一次就刪除該活頁簿連線? 謝謝各位 :Q

Sub 財務比率()
Call StockNo
Dim URL As String
'X As String
'X = Worksheets("營收").Range("A2")
URL = "URL;http://dj.mybank.com.tw/z/zc/zcq/zcra_" & 股票代號 & ".djhtm"
With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=ActiveSheet.Range("K68"))
.TablesOnlyFromHTML = True
.RefreshStyle = xlOverwriteCells
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.AdjustColumnWidth = False
.WebTables = "1"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
Cells.Hyperlinks.Delete
End With
'Cells.Hyperlinks.Delete
End Sub
Private Sub CommandButton1_Click()
'StockNo
財務比率
End Sub

複製代碼

2^#

發表於 2014-6-22 00:19 | 顯示全部帖子

回復 2# stillfish00

還是不行耶..每執行連線還是持續增加中...

3^#

發表於 2014-6-22 00:43 | 顯示全部帖子

終於找到了謝謝，方法如下:

複製代碼

執行Button Click時再加入下語法就搞定了:

複製代碼

4^#

發表於 2014-6-23 18:57 | 顯示全部帖子

回復 5# stillfish00

是的我手動全部刪除連線後測試，執行後還是有跑出來....對了我的是2010版本的感恩中...

Sub 財務比率()
Call StockNo
Dim URL As String
'X As String
'X = Worksheets("營收").Range("A2")
URL = "URL;http://dj.mybank.com.tw/z/zc/zcq/zcra_" & 股票代號 & ".djhtm"
With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=ActiveSheet.Range("K68"))
.TablesOnlyFromHTML = True
.RefreshStyle = xlOverwriteCells
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.AdjustColumnWidth = False
.WebTables = "1"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
Cells.Hyperlinks.Delete
.Delete
End With
'Cells.Hyperlinks.Delete
End Sub

複製代碼

5^#

發表於 2014-6-23 19:45 | 顯示全部帖子

回復 7# stillfish00

我把"股票代號"拿掉 StockNO也拿掉直接放入股票的代號連線還是有耶:'( 謝謝

Sub 財務比率()
'Call StockNo
Dim URL As String
'X As String
'X = Worksheets("營收").Range("A2")
URL = "URL;http://dj.mybank.com.tw/z/zc/zcq/zcra_" & 1101 & ".djhtm"
With ActiveSheet.QueryTables.Add(Connection:=URL, Destination:=ActiveSheet.Range("K68"))
.TablesOnlyFromHTML = True
.RefreshStyle = xlOverwriteCells
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.AdjustColumnWidth = False
.WebTables = "1"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
Cells.Hyperlinks.Delete
.Delete
End With