vba读取csv文件,根据csv文件不同,会有不同格式:
1. 单纯的逗号分割,但是数字或备注字段中包含逗号,取入时,字段会被拆分
2. 以逗号分割,但是,每个字段都有引号
vba读取入csv后的格式,基本都是字符串,然后通过分隔符进行拆分,所以,遇到一个字段中包含逗号的,很头疼,但是,根据取入的字符串格式,还是有办法可以解决的
' filePath 文件路径
' sheetName 目标sheet页名
Public Sub inputCsv(filePath As String, sheetName As String)
Dim Buf As String
Dim i, j As Long
Dim tmp2() As Variant
Dim cnt As Integer
Dim myflag As Boolean
With Worksheets(sheetName)
.Visible = xlSheetVisible
.Activate
.Cells.Select
Selection.NumberFormatLocal = "@"
.Cells(1, 1).Select
End With
' 開ける
Set adoSt = CreateObject("ADODB.Stream")
i = 1
With adoSt
.Charset = "Shift-JIS"
.Open
.LoadFromFile (filePath)
Do Until .EOS
Buf = .Readtext(-2)
tmp = Split(Buf, ",")
myflag = False
cnt = 0
For j = LBound(tmp) To UBound(tmp)
If myflag = False And Left(tmp(j), 1) = """" And Right(tmp(j), 1) = """" Then
cnt = cnt + 1
ReDim Preserve tmp2(cnt)
tmp2(cnt) = Mid(tmp(j), 2, Len(tm