1、目录1、vba+ado读取当前目录下的CSV数据并保存为csv或本sheet中21.1 vba+ado读csv查询结果存为csv文件(逐行输出)21.2 vba+ado读csv查询结果另存为csv文件(逐个字段输出不换行)21.3 vba+ado读csv查询结果输出到本工作簿中的sheet32、vba+ado读取当前工作数据41、vba+ado读取当前目录下的CSV数据并保存为csv或本sheet中1.1 vba+ado读csv查询结果存为csv文件(逐行输出)SUB 将查询结果保存为CSV_1 ()Dim cnn, SQL$ Set cnn = CreateObject(adodb.connection) Set RS = CreateObject(adodb.recordset) cnn.Open Provider = Microsoft.ace.Oledb.12.0;Extended Properties =TEXT;Data Source = & ThisWorkbook.Path & SQL = SELECT * FROM KPI.CSV A GROUP BY ERBSSet
2、 RS = cnn.Execute(SQL) ARR = cnn.Execute(SQL).GETROWSBRR = RS.Fields.Count RS.FIELDS.COUNTOpen ThisWorkbook.Path & 小区列表.csv For Output As #1Print #1, RS.Fields(0).Name + Chr(44) + RS.Fields(1).Name + Chr(44) + RS.Fields(2).NameFor J = 0 To UBound(ARR, 2) 列数For L = 0 To BRR - 1Print #1, ARR(0, J) + Chr(44) + ARR(1, J) + Chr(44) + ARR(2, J)NextClose #1RS.Closecnn.CloseSet rst = NothingSet cnn = NothingWorksheets(结果).ActivateMsgBox (OK)END SUB1.2 vba+ado读csv查询结果另存为csv文件(逐个字段输出不换行)SUB 将查询结果保存为CSV_2(
3、)Set cnn = CreateObject(ADODB.CONNECTION)Set RS = CreateObject(ADODB.RECORDSET)cnn.Mode = adModeReadWritecnn.Open Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=text;HDR=Yes;FMT=Delimited(,);Data Source= & ThisWorkbook.Path & SQL = select * from EUtranCellRelation.CSVSet RS = cnn.Execute(SQL) 将SQL语句获得的数据传 Open ThisWorkbook.Path & output.CSV For Output As #1For M = 0 To RS.Fields.Count - 1 Print #1, RS.Fields(M).Name + Chr(44); Next Print #1, narr = cnn.Execute(SQL).GetRowsbrr = RS.Fields.
4、Count RS.FIELDS.COUNTFor j = 0 To UBound(arr, 2) 列数For L = 0 To brr - 1Print #1, arr(L, j) + Chr(44); ;分号表示不换行NextPrint #1, nNextClose #1cnn.CloseSet rst = NothingSet cnn = NothingMsgBox OKEND SUB1.3 vba+ado读csv查询结果输出到本工作簿中的sheetSub ado读CSV存sheet()Set cnn = CreateObject(adodb.connection)Set rs = CreateObject(adodb.recordset) cnn.Open Provider = Microsoft.ace.Oledb.12.0;Extended Properties =TEXT;Data Source = & ThisWorkbook.Path & SQL=select * FROM TEST.CSV Set rs = cnn.Execute(SQL)Sheets(结果).Cel
5、ls.ClearContentsSheets(结果).Range(a2).CopyFromRecordset rs For i = 0 To rs.Fields.Count - 1 Sheets(结果).Cells(1, i + 1) = rs.Fields(i).Name NextWorksheets(结果).ActivateMsgBox (OK) END SUB2、vba+ado读取当前工作数据SUB ado读工作簿中sheet数据()Dim cnn, SQL$ Set cnn = CreateObject(adodb.connection) Set rs = CreateObject(adodb.recordset) cnn.Open provider=microsoft.ace.oledb.12.0;Extended Properties =excel 12.0;data source= & ThisWorkbook.FullName a = “SELECT * FROM 结果$ Set rs = cnn.Execute(SQL) Sheets(修改前后输出).Cells.ClearContents Sheets(修改前后输出).Range(a2).CopyFromRecordset rs For i = 0 To rs.Fields.Count - 1 Sheets(修改前后输出).Cells(1, i + 1) = rs.Fields(i).Name Nextrs.Closecnn.CloseSet rst = NothingSet cnn = NothingWorksheets(修改前后输出).ActivateMsgBox (OK)END SUB
《VBA读取CSV内容方法》由会员yy****3分享,可在线阅读,更多相关《VBA读取CSV内容方法》请在金锄头文库上搜索。