(不定期更新)使用VBA解決 excel web 查詢無法匯入、匯入太慢的股市資料

cji3cj6xu6 wrote:
初步判斷是因為我的excel 檔案裡面的儲存格有交叉的運算，
例如 : e30 = (A1:A30)/30 ，F......之類
當把彼此交叉的運算儲存格移除，則getxml_tbl可正常使用(抓完掉回0.2秒內)。
當採用getxml_tbl 的方式，不曉得要如何避免這類問題。...(恕刪)

'方法1
'=============================
Sub test()

    Dim  ie As Object, i As Integer

        Set ie = CreateObject("InternetExplorer.Application")

        With ie
            .Visible = True
            .navigate ThisWorkbook.Path & "\main.html"

            Do Until .readyState = 4: DoEvents: Loop
            Do While .Busy: DoEvents: Loop
     '如果activex有設定好，msgbox可以取消不使用          
    '        MsgBox "注意：請先設定activex控制項的安全等級" & vbNewLine & _
            "或手動點選允許被封鎖的內容"
            

            For i = 0 To .document.frames.Length - 1 Step 2
            
            .document.frames.Item(i).document.all("TAChartPeriod").Value = "30m"
            '5m、10m、30m、d、w、m
            .document.frames.Item(i).document.all("TAChartIndex").Value = "KD"
            'VOL、KD、MACD、RSI、BIAS、WR、BS、CDP、DMI
                     
            Next i
            
            
            
            .Refresh
            
        End With
        
        
        'ie.Quit
        Set ie = Nothing
        
End Sub

'=============================

'方法2
'=============================
Sub test1()

    Dim ie As Object, DOM_event As Object, i As Integer

        Set ie = CreateObject("InternetExplorer.Application")

        With ie
            .Visible = True
            .navigate ThisWorkbook.Path & "\main.html"

            Do Until .readyState = 4: DoEvents: Loop
            Do While .Busy: DoEvents: Loop
             
            Set DOM_event = .document.createEvent("HTMLEvents")
            DOM_event.initEvent "change", True, False

     
         '   MsgBox "注意：請先設定activex控制項的安全等級" & vbNewLine & _
            "或手動點選允許被封鎖的內容"

            
            For i = 0 To .document.frames.Length - 1 Step 2
            
                  
                  '.document.frames.Item(i).document.getElementById("TAChartPeriod").Focus
                   .document.frames.Item(i).document.getElementById("TAChartPeriod").selectedindex = 2
                   .document.frames.Item(i).document.getElementById("TAChartPeriod").dispatchEvent DOM_event
            
                  '.document.getElementById("TAChartIndex").Focus
                   .document.frames.Item(i).document.getElementById("TAChartIndex").selectedindex = 1
                   .document.frames.Item(i).document.getElementById("TAChartIndex").dispatchEvent DOM_event
                
            Next i

'注意：使用方法2，如果activex沒有設定好，msgbox 會讓.dispatchEvent DOM_event 失效
'圖表不會更新，需啟用下面這行 .refresh
'有興趣的可以先刪掉msgbox，用f8逐行執行，測試.dispatchEvent的功能

            '.Refresh 
        End With
        
        
        'ie.Quit
        Set ie = Nothing
        Set DOM_event = Nothing
        
End Sub
'=============================

water-仔 wrote:
1. 我進入程式原始碼後,不知道要在哪邊輸入資料才能進行table查詢
...(恕刪)

water-仔 wrote:
2. 暫存資料的部分如圖,如果把註記拿掉執行就會產生錯誤,所以不理解為什麼...(恕刪)

momolittle wrote:
有誰可以幫我看看下方是借用某位大大寫的程式碼，...(恕刪)