如何用VBA匯入CSV檔?

1^# 跳轉到 »

發表於 2011-9-27 23:25 | 顯示全部帖子

本帖最後由 luhpro 於 2011-9-27 23:27 編輯

有高手可以教我一下如何用VBA匯入CSV檔呢?還可以自選列和分隔的符號,謝謝~~!
am0251 發表於 2011-9-3 17:09

是底下這樣嗎?

複製代碼

Sub openCSV(ByVal sFileName$, lStartLine As Long, lLoadLine As Long, sChar$)
' sFileName : CSV檔案名稱, lStartLine : 開始列號, lLoadLine : 讀取列數, sChar : 分隔字元
Dim iI%
Dim sFullName$, sStr$
Dim lJ As Long, lPos As Long
Dim vData
sFullName = ThisWorkbook.Path & "\" & sFileName
If Dir(sFullName) <> "" Then
Open sFullName For Input Access Read Shared As #1
lJ = 1
Do While lJ < lStartLine
Line Input #1, sStr
lJ = lJ + 1
Loop
For lJ = 1 To lLoadLine
Line Input #1, sStr
ReDim vData(0)
vData = Split(sStr, sChar)
' 題外話 : Cells(lJ, 1).Resize(, UBound(vData) + 1) = Application.Transpose(vData) 此行結果不正確不知錯在哪裡, 只好改成以下這樣的迴圈...
For iI = 0 To UBound(vData)
Cells(lJ, iI + 1) = vData(iI)
Next iI
Next lJ
Close (1)
Else
MsgBox ("找不到 " & sFileName & " 檔案")
End If
End Sub

複製代碼

靜思自在 : 話多不如話少，話少不如話好。