' 之前 是抓 C碟 的文字檔
' 現在 欲抓 網頁 的文字檔
' 該 如何 處理 可以請 高手 幫忙 感謝
'1之前抓 C碟
SS = "C:\123\123.txt"
If Dir(SS) <> "" Then
Open SS For Input As #1
Line Input #1, DataLine
Close #1
Dataarray = Split(DataLine, ",")
Sht1.Range("U4") = Dataarray(0) '時間
Sht1.Range("V4") = Dataarray(1) '數字
Sht1.Range("W4") = Dataarray(2) '數字
Sht1.Range("X4") = Dataarray(3) '數字
Sht1.Range("Y4") = Dataarray(4) '數字
Sht1.Range("Z4") = Dataarray(5) '數字
Sht1.Range("AA4") = Dataarray(6) '數字
'2之後抓 網頁
' 經由 錄製 如下 內容如下 (時間+ 6筆數字) 19:01:01,11,22,33,44,55,66
' 好比 http://192.168.1.1/123/123.txt (網址假的)
SS = Sht1.[P34]
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://" & SS & ".txt", Destination:=Sht1.Range("$U$4"))
.CommandType = 0
.Name = "123"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
' 錄製 後 每執行一次，都會"出現" 這裡已經有資料，你要取代這筆資料嗎? (確定) (取消)
如 圖
' 資料剖析，將 單一儲存格內的資料，依逗號 拆解
Range("U4").Select
Selection.TextToColumns Destination:=Range("U4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
End With
感謝 |
發表於 2020-5-27 19:59
| 
