請問如何自動匯入外部資料 - Excel程式區

[發問] 請問如何自動匯入外部資料

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

1^# 跳轉到 »

發表於 2011-10-6 15:29 | 顯示全部帖子

回復 1# sandra_wang
試試看

Sub 匯入文字檔_Ex()
Dim MyPath As String, TheDate As String, TheFile As String, ShName As String, Mystr As String, Rng As Range, E As Variant
MyPath = ThisWorkbook.Path & "\" '文字檔所在的目錄
TheDate = Format(Date - 1, "mm/dd") '取的日期
On Error Resume Next 'DIR 找不到檔案會產生錯誤
TheFile = Dir(MyPath & "*MB-*" & TheDate & "*.log") '尋找檔案
If Err.Number > 0 Then MsgBox "找不到 " & TheDate & " 檔案": Exit Sub
Do While TheFile <> ""
On Error GoTo ShAdd '工作表中沒有 ShName會產生錯誤
ShName = Mid(TheFile, InStr(TheFile, "MB-"), InStr(TheFile, "_") - InStr(TheFile, "MB-"))
Set Rng = Sheets(ShName).Cells(Rows.Count, "A").End(xlUp)
If Rng <> "" Then Set Rng = Rng.Offset(1)
Open MyPath & TheFile For Input As #1 '開啟文字檔
Do While Not EOF(1) '不是檔案底部時執行迴圈
Input #1, Mystr '從已開啟的循序讀取資料，並將資料指定給變數。->mystr
For Each E In Split(Mystr, Chr(10))
Rng = E
Set Rng = Rng.Offset(1)
Next
Loop
Close #1 '關閉文字檔
TheFile = Dir
Loop
Exit Sub
ShAdd:
ThisWorkbook.Sheets.Add.Name = ShName
Err.Clear
Resume
End Sub