Excel 抓取檔案範例

將某個目錄的測試檔案數值自動抓取到 Excel 表格內

Sub nn()
Dim Ar(), MyPath$, Op$, MyStr$, fs$, MyAr, A, s&
MyPath = "D:\SAVE\2008-11-26\"
fs = Dir(MyPath & "*.txt")
While fs <> ""
Op = MyPath & fs
Open Op For Input As #1
Do While Not EOF(1)
Input #1, MyStr
If MyStr <> "" Then
ReDim Preserve Ar(s)
Ar(s) = MyStr
s = s + 1
End If
Loop
A = Split(Join(Ar, Chr(10)), "! !")
MyAr = Split(A(UBound(A)), Chr(10))

[IV1].End(xlToLeft).Offset(, 1).Resize(UBound(MyAr) + 1, 1) = Application.Transpose(MyAr)

'[A65536].End(xlUp).Offset(1, 0).Resize(UBound(MyAr) + 1, 1) = Application.Transpose(MyAr)
Close #1
Erase Ar: s = 0
fs = Dir
Wend
End Sub