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
No comments:
Post a Comment