csv file for report

csv file for report

CSV文件是一种文字档，各项资料使用”，”号分隔。例如

编号,分类,姓名,国文,数学,理化,社会,合计
78,A,王五,36,47,54,42,179
66,B,吴三,41,35,33,73,182
24,C,邓四,62,37,35,49,183
81,D,杨姐,43,46,32,64,185
58,A,杨兄,31,35,35,87,188
30,B,林董,50,34,35,70,189

当然Excel可以直接读取CSV档，这只是另一个做法而已。
有个好处可以指定读取后存放的位置。

'coded by crdotlin@2005.12.15
'Module: 一般模块-Module1

Option Explicit

Dim myDB As clsADODBopen

Sub Main()
Dim strCmn As String
Dim FN As String
FN = "SrcData.csv"
Set myDB = New clsADODBopen
strCmn = "select * from " & FN
With myDB
.subConn ThisWorkbook.Path & "\data\"
.subOpen strCmn
End With
subShow
Set myDB = Nothing
End Sub

Sub subShow()
Dim i As Integer
Dim pt As Range

Set pt = ActiveSheet.Range("a1")
With myDB.theRST
For i = 1 To .Fields.Count
pt.Offset(0, i - 1).Value = .Fields(i - 1).Name
Next
pt.Offset(1, 0).CopyFromRecordset myDB.theRST
End With
End Sub
'coded by crdotlin@2005.12.15
'Purpose: 读取CSV文件
'Method: ADO
'Requirement:Microsoft Active Data 2.x Object Library
'Module: 对象类别模块-clsADODBopen
'Emphases: 1. 每个csv档视为一个资料表
' 2. 连结字串"DBQ="后面只接路径即可, 不需文件名
' 3. 数据库引擎为"Driver={Microsoft Text Driver (*.txt; *.csv)};"

Option Explicit

Dim theCON As ADODB.Connection
Public theRST As ADODB.Recordset

Sub subConn(strFullName As String)
Dim strDrv As String

strDrv = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
" DBQ=" & strFullName & ";"
theCON.Open "Provider=MSDASQL; " & strDrv
End Sub

Sub subOpen(strCmn As String)
theRST.Open Source:=strCmn, ActiveConnection:=theCON
End Sub

Private Sub Class_Initialize()
Set theCON = New ADODB.Connection
Set theRST = New ADODB.Recordset
End Sub

Private Sub Class_Terminate()
theCON.Close
Set theRST = Nothing
Set theCON = Nothing
End Sub