- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
2#
發表於 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
|
|
發表於 2011-10-5 15:27
| 
