VBA在处理数据时会碰到需要处理csv文档数据的情况,这种情况与处理txt文档类似,都需要去读取文本,然后获取其中的字符串,然后将满足要求的字符串通过数组或者循坏的方式写入excel表格中,然后再进一步处理。
下述代码就是读取csv文档并将数据写入到excel工作表的示例,具体实现方法如下:
Sub Dat_anl()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'这部分代码是定义工作表名称,并清除指定范围内容
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Application.ScreenUpdating = False '禁止屏幕更新
Datatfile = ThisWorkbook.Name
Set shR = Workbooks(Datatfile).Sheets("Raw data") '定义worksheet名称
Set shM = Workbooks(Datatfile).Sheets("My data")
Set shF = Workbooks(Datatfile).Sheets("Fly data")
Set shD = Workbooks(Datatfile).Sheets("Delta data")
Set shS = Workbooks(Datatfile).Sheets("Start sheet")
Set shPivot = Workbooks(Datatfile).Sheets("Pivot")
shS.Range("F6:F11").ClearContents '清除指定range的内容
shD.Range("A2:W160000").ClearContents
shF.Range("A2:U160000").ClearContents
shM.Range("A2:U160000").ClearContents
shR.Range("A2:S160000").ClearContents
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'这部分代码是打开csv文件,并利用split函数拆分字符串
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
shS.Select
FilePath = "C:\Users\Desktop\Measurement data\"
ChDrive "C"
ChDir FilePath
Dim fso As Object, sFile As Object, blnExist As Boolean
Dim FileName As String, LineText As Variant, i As Integer, iCol As Integer
Const ForReading = 1
Set fso = CreateObject("Scripting.FileSystemObject") '创建FileSystemObject对象
Openfilename = Application.GetOpenFilename("raw files (*.csv), *.csv") '打开*.csv文件
blnExist = fso.FileExists(Openfilename) '判断文件是否存在,如果不存在,则退出过程
If Not blnExist Then MsgBox "Please Open .CSV File !": Exit Sub
Set sFile = fso.OpenTextFile(Openfilename, ForReading) '创建并打开名为sFile的TextStream对象
i = 0
j = 0
Openfile = Split(Openfilename, "\") 'Split函数拆分字符串
lotid = Split(Openfilename, "_")
shS.Cells(6, 6) = lotid(UBound(lotid) - 1)
shS.Cells(7, 6) = Openfilename
shS.Cells(10, 6) = Now()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'这部分代码是逐行读取csv文档,并寻找满足条件的字符串,先写入到二维数组中,再写入到工作表中
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Do While Not sFile.AtEndOfStream '如果不是文本文件的尾端,则读取数据
LineText = sFile.ReadLine '逐行读取文本
Check_Text = Left(LineText, 13)
If Check_Text = "[Measurement]" Then
i = i + 1
All_Text = Split(LineText, ",")
shR.Cells(2, j + 1) = All_Text(1)
j = j + 1
Dim Arr_MTF(0 To 288, 0 To 13)
For m = 0 To 288
For n = 0 To 13
Arr_MTF(m, n) = All_Text(n) '将满足条件的文本写入到二维数组
Next n
j = 0
Next m
shM.Cells(2 + i - 1, 1) = i
shM.Cells(2 + i - 1, 2) = shR.Cells(2, 1)
shM.Cells(2 + i - 1, 3) = shR.Cells(2, 2)
shM.Cells(2 + i - 1, 4) = shR.Cells(2, 1) & "," & shR.Cells(2, 2)
shF.Cells(2 + i - 1, 1) = i
shF.Cells(2 + i - 1, 2) = shR.Cells(2, 1)
shF.Cells(2 + i - 1, 3) = shR.Cells(2, 2)
shF.Cells(2 + i - 1, 4) = shR.Cells(2, 1) & "," & shR.Cells(2, 2)
shD.Cells(2 + i - 1, 1) = i
shD.Cells(2 + i - 1, 2) = shR.Cells(2, 1)
shD.Cells(2 + i - 1, 3) = shR.Cells(2, 2)
shD.Cells(2 + i - 1, 4) = shR.Cells(2, 1) & "," & shR.Cells(2, 2)
shR.Range("C2:P290") = Arr_MTF '二维数组的内容写入到工作表
shPivot.Select
ActiveSheet.PivotTables("Pivot").PivotCache.Refresh '刷新数据透视表
Range("B5:R5").Copy '复制单元格内容
shM.Cells(2 + i - 1, 5).Select '粘贴到指定单元格
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Pivot").Range("B10:R10").Select
Application.CutCopyMode = False
Selection.Copy
shF.Cells(2 + i - 1, 5).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
For k = 0 To 16
shD.Cells(2 + i - 1, 5 + k) = (shF.Cells(2 + i - 1, 5) - shF.Cells(2 + i - 1, 5 + k)) * 1000
Next k
shD.Cells(2 + i - 1, 22) = (shD.Cells(2 + i - 1, 18) + shD.Cells(2 + i - 1, 20)) / 2
shD.Cells(2 + i - 1, 23) = (shD.Cells(2 + i - 1, 19) + shD.Cells(2 + i - 1, 21)) / 2
Else
End If
Loop
sFile.Close
Application.ScreenUpdating = True '打开屏幕刷新,与Application.ScreenUpdating=false成对存在
shS.Cells(9, 6) = i
shS.Cells(11, 6) = Now()
MsgBox (" Done !")
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'这部分代码是将工作簿另存在指定路径下
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ThisWorkbook.SaveAs FileName:=FilePath & "\" & shS.Cells(6, 6) & "-data Summary.xlsm"
End Sub
上述代码要点总结:
1.读取csv文档内容及split拆分字符串(与读取txt文档内容的方法一致)
2.将多个字符串写入二维数组,进而写入到excel工作表
3.数据透视表的使用方法