模組Module1
Sub Ex()
Dim i As Integer, Test() As New Class1
'Test指定為 新物件類別模組 : Class1物件
On Error Resume Next
With Sheet1
.Range("A1:A3").Value = .Range("C1:C3").Value
End With
For i = 1 To Sheet2.QueryTables.Count
ReDim Preserve Test(1 To i)
Set Test(i).Query = Sheet2.QueryTables(i)
Test(i).Query.Refresh False
Next
End Sub
物件類別模式 Class1
Option Explicit
Public WithEvents Query As QueryTable 'Query指定為QueryTable物件
Private Sub Query_AfterRefresh(ByVal Success As Boolean) '查詢後的事件
If Success = False Then '查詢失敗 'Success = True 查詢成功
With Query.ResultRange
.Interior.ColorIndex = 37
.Cells(1).Offset(1).Resize(.Rows.Count - 1, .Columns.Count) = "查無資料"
End With
End If
End Sub
Private Sub Query_BeforeRefresh(Cancel As Boolean) '查詢前的事件
With Query.ResultRange
.Interior.ColorIndex = xlNone
End With
End Sub作者: GBKEE 時間: 2011-11-17 17:37
即使是彈跳視窗，在網頁中，查一下html body 就有相關的字句，自然可以跳過，如果不這樣用，也可以下載後用查檢網頁大小就知道有資料與查無資料的網頁大小是不一樣的
以上作者: blue2263 時間: 2014-2-24 23:36
本帖最後由 blue2263 於 2014-2-24 23:47 編輯
謝謝j大解答
我將程式碼改為,On Error Resume Next
然後加一IF判斷,判斷資料無更改,就跳開,測試
看起來是沒問題,錯誤訊息不會跑出,且跳出,
我是新手,不知我這樣改,會不會有什麼問題?
還請指教謝謝!!
程式碼如下
Sub 資料下載整合()
'資料網路下載->匯總Sheets
Dim Rng As Range, Ar(1 To 3)
Dim err
Set Rng = Sheets("代碼").[a2] '代碼起始位置在a2
Do While Rng <> "" '無代碼 中斷
Dim myErrNum As Long
With Sheets("原始表")
.Range("a6") = Rng
On Error Resume Next '發生錯誤陳述式之後的陳述式繼續執行
err = Sheets("原始表").Range("az7")
.Range("az7").QueryTable.Refresh BackgroundQuery:=False
On Error GoTo 0 '停止error resume
If err = Sheets("原始表").Range("az7") Then GoTo 103
With .Range("BB12:BB27")
Ar(1) = Application.Transpose(.Cells) '人數
Ar(2) = Application.Transpose(.Offset(, 1)) '股數
Ar(3) = Application.Transpose(.Offset(, 2)) '佔集保庫存數比例 (%)
End With
End With
With Sheets("匯總").Range("A1").End(xlDown).Offset(1) '選擇工作表,到最底行
.Cells(1) = Rng
.Cells(1, 2) = Rng.Offset(, 1)
.Cells(1, "C").Resize(, UBound(Ar(1))) = Ar(1)
.Cells(1, "S").Resize(, UBound(Ar(1))) = Ar(2)
.Cells(1, "AI").Resize(, UBound(Ar(1))) = Ar(3)
.Cells(1, "AX") = ""
'.Range("A1:aw1").Value = Sheets("原始表").Range("A6:aw6").Value
End With
103
Set Rng = Rng.Offset(1) '下一個代碼
Sub test()
Dim x
Dim oie As Object: Set oie = CreateObject("internetexplorer.application")
With oie
.Visible = True '我 要改 False 不顯示 表現
.Navigate "" ' 這是 要放網址嗎?? 如有 3個 網址...的話 該???
Do While .readystate <> 4 Or .busy: DoEvents: Loop
Set wshshell = CreateObject("wscript.shell")
Do
ret = wshshell.AppActivate("Microsoft Excel")
Loop Until ret = True
Application.Wait Now + 2 / 86400 '等待 2秒
ret = wshshell.AppActivate("Microsoft Excel")
If ret = True Then
ret = wshshell.AppActivate("Microsoft Excel")
Application.Wait Now + 2 / 86400
wshshell.SendKeys "{enter}"
End If
Application.Wait Now + 2 / 86400
Do While .readystate <> 4 Or .busy: DoEvents: Loop
.Quit
End With
End Sub
