- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
4#
發表於 2012-10-2 08:51
| 只看該作者
本帖最後由 GBKEE 於 2012-10-2 13:08 編輯
回復 1# BigDog
全文檢索中搜尋 網路連線
作業系統 Windows NT 且 網路IP 是浮動IP- Sub 練習()
- Dim motoSht As Worksheet, sakiSht As Worksheet, sakiRng As Range
- Dim motoHani() '宣告變數
- Set motoSht = Worksheets("財務分析") '設定個別財務分析工作表
- Set sakiSht = Worksheets("上市") '設定儲存全部上市工作表
- motoSht.Range("A1:T21") = ""
- For Each E In motoSht.Names ' 刪除 QueryTables
- E.Delete
- Next
- '將想要轉寫之儲存格的位置儲存在陣列中
- motoHani = Array("C18", "D18", "E18", "H18", "I18", "J18", "M18", "N18", "O18", "T18")
- '取得分析結果至上市總表的輸入位置
- motoaddress = Array("A1", "f1", "k1", "p1")
- motoyear = Array(93, 96, 99, 100)
- '取得載入財務分析及年度資料位置
- motoRowcon = sakiSht.Range("a1").CurrentRegion.Rows.Count
- '取得上市總列數
- For i = 2 To motoRowcon
- motoSht.Cells(25, 1) = sakiSht.Cells(i, 1)
- For x = 0 To 3
- RE:
- Application.StatusBar = False
- With motoSht.QueryTables.Add(Connection:= _
- "URL;http://mopsov.twse.com.tw/server-java/t05st22?colorchg=1&off=1&TYPEK=sii&isnew=false&year=" & motoyear(x) & "&co_id=" & motoSht.Range("A25") & "&" _
- , Destination:=motoSht.Range(motoaddress(x)))
- .Name = _
- "t05st22?colorchg=1&off=1&TYPEK=sii&isnew=false&year=100&co_id=1733&"
- .FieldNames = True
- .RowNumbers = False
- .FillAdjacentFormulas = False
- .PreserveFormatting = True
- .RefreshOnFileOpen = False
- .BackgroundQuery = True
- .RefreshStyle = xlInsertDeleteCells
- .SavePassword = False
- .SaveData = True
- .AdjustColumnWidth = True
- .RefreshPeriod = 0
- .WebSelectionType = xlSpecifiedTables
- .WebFormatting = xlWebFormattingNone
- .WebTables = "4"
- .WebPreFormattedTextToColumns = True
- .WebConsecutiveDelimitersAsOne = True
- .WebSingleBlockTextImport = False
- .WebDisableDateRecognition = False
- .WebDisableRedirections = False
- .Refresh BackgroundQuery:=False
- If Application.Count(.ResultRange) = 0 Then '到第九家過後就變的空白
- Application.StatusBar = "等候網頁中...."
- 重啟連線
- GoTo RE
- End If
- End With
- Next
- For j = 0 To UBound(motoHani)
- sakiSht.Cells(i, 25).Offset(0, j).Value = motoSht.Range(motoHani(j)).Value
- Next
- motoSht.Range("A1:T21") = ""
- For Each E In motoSht.Names
- E.Delete
- Next
- Next
- '將個別資料帶入上市總表
- MsgBox "輸入完畢。"
- End Sub
- Sub 重啟連線()
- ' 此程式作用:中斷網路連線再度連線重新取的網路IP,以重新取得 WEB SERVER限制 一分鐘讀取20次
- '網路連線 的名稱 adsl 請修改為 你PC 上正確名稱
- 'res = Shell("rasphone.exe [-d *****]", 1)
- res = Shell("rasphone.exe [-d adsl]", 1)
- AppActivate res, True
- SendKeys "%(U)", True
- Application.Wait Now + #12:00:01 AM# '延遲等候程式處裡
-
- SendKeys "%(Y)", True
- Application.Wait Now + #12:00:01 AM# '延遲等候程式處裡
-
- SendKeys "%(C)", True
- Application.Wait Now + #12:00:01 AM# '延遲等候程式處裡
-
- SendKeys "%(C)", True
- Application.Wait Now + #12:00:02 AM# '延遲等候曲程式處裡
- End Sub
|
|
發表於 2012-9-29 21:45
| 
