返回csv文件作为记录集 [英] return csv file as recordset

问题描述

我有一个将数据导出到CSV文件的外部程序。我的用户希望通过Excel中的VBA函数访问此数据。为了做到这一点，我想到将CSV文件读入一个返回ADODB.Recordset的函数。我的代码是

 公共函数getData（fileName As String）作为ADODB.Recordset 
 Dim path As String 
 path =C：\testDir\
 Dim cN As New ADODB.Connection 
 Dim RS As New ADODB.Recordset 
 cN.Open（Provider = Microsoft.Jet.OLEDB .4.0;& _ 
Data Source =& path&;& _ 
Extended Properties =text; HDR = Yes; FMT = Delimited; IMEX = 1 ;）
 RS.ActiveConnection = cN 
 RS.Source =select * from& fileName 
设置getData = RS 
结束函数
  

我试图调用这个功能使用

  Dim a As ADODB.Recordset 
设置a = getData（testFile.csv）
 a.Open（）
  

在这一点上，我收到一个编译错误，说'='。有人可以指出我正确的方向，我应该如何调用我的函数并循环访问数据？

解决方案

通过我自己的一些调整以及Tim Williams的输入来解决它。以下是可能需要帮助的任何人的代码

 公共函数getData（fileName As String）作为ADODB.Recordset 
 
 Dim path As String 
 path =C：\testDir\
 Dim cN As ADODB.Connection 
 Dim RS As ADODB.Recordset 
 Set cN = new ADODB.Connection 
设置RS = new ADODB.Recordset 
 cN.Open（Provider = Microsoft.Jet.OLEDB.4.0;& _ 
Data Source =&路径&;& _ 
扩展属性=文本; HDR =是; FMT =分隔; IMEX = 1;）
 RS.ActiveConnection = cN 
 RS.Source =select * from& fileName 
设置getData = RS 
 
结束功能
  

该函数可以调用为

  Dim a As ADODB.Recordset 
设置a = getData（testFile.csv ）
 a.Open 
 MsgBox（a.GetString（））
 a.Close 
  

I have an external program that exports data into CSV files. My users would like to have access to this data through a VBA function in excel. In order to do this, I thought about wrapping the CSV file read into a function that returns a ADODB.Recordset. My code is

Public Function getData(fileName As String) As ADODB.Recordset
Dim path As String
path = "C:\testDir\"
Dim cN As New ADODB.Connection
Dim RS As New ADODB.Recordset
cN.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & path & ";" & _
               "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")
RS.ActiveConnection = cN
RS.Source = "select * from " & fileName
Set getData = RS
End Function

I am trying to call this function using

Dim a As ADODB.Recordset
Set a = getData("testFile.csv")
a.Open()

At this point, I get a compile error saying '=' expected. Could someone point me in the right direction on how I should call my function and loop through the data?

解决方案

Solved it with some tweaks of my own along with input from Tim Williams. Here is the code for anyone else who might need help

Public Function getData(fileName As String) As ADODB.Recordset

    Dim path As String
    path = "C:\testDir\"
    Dim cN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Set cN = new ADODB.Connection
    Set RS = new ADODB.Recordset
    cN.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & path & ";" & _
                   "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")
    RS.ActiveConnection = cN
    RS.Source = "select * from " & fileName
    Set getData = RS

End Function

Now, the function can be called as

Dim a As ADODB.Recordset
Set a = getData("testFile.csv")
a.Open
MsgBox(a.GetString())
a.Close

这篇关于返回csv文件作为记录集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！