通过 Microsoft OLE DB 提供程序使用 EXCEL 作为数据源

发布于 2024-11-09 04:26:03 字数 323 浏览 5 评论 0原文

我们经常使用一些 Excel 文件作为数据库中大量导入的数据源。在代码级别，我们总是将相应的数据源称为：

set rs = New ADODB.recordset
rs.open "SELECT * FROM [sheet1$]", myConnectionString, etc

当然，此过程仅在 Excel 文件中存在名为 [sheet1] 的工作表时才有效。我想在此处添加一些工作表管理代码，但无需创建原始 Excel 文件的实例、打开它等等（我的用户可能会得到一个具有不同工作表名称的文件，并且可能没有安装 Excel ）。

有什么想法吗？

原文

We are frequently using some Excel files as a datasource for massive imports in our database. At the code level, we always refer to the corresponding data source as:

set rs = New ADODB.recordset
rs.open "SELECT * FROM [sheet1$]", myConnectionString, etc

Of course, this procedure only works when there's a sheet in the Excel file which is named [sheet1]. I'd like to add some sheet management code here, but without having to create an instance of the original Excel file, opening it, and so on (my users might get a file with a different sheet name, and might not have Excel installed).

Any idea?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

冷血 2024-11-16 04:26:03

您可以使用 ADO OpenSchema 方法打开记录集，然后列出工作簿中的表（工作表）名称。

Public Sub SheetsInWorkbook()
    Dim strConnect As String
    Dim cn As Object
    Dim rs As Object
    Dim strPath As String

    strPath = CurrentProject.Path & Chr(92) & "temp.xls"
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source='" & strPath & "';" _
        & "Extended Properties='Excel 8.0';"
    Set cn = CreateObject("ADODB.Connection")
    cn.ConnectionString = strConnect
    cn.Open
    Set rs = cn.OpenSchema(20) '20 = adSchemaTables '
    Debug.Print "TABLE_NAME"
    Do While Not rs.EOF
        Debug.Print rs!TABLE_NAME
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

You can open a recordset with the ADO OpenSchema method and then list the table (sheet) names in your workbook.

Public Sub SheetsInWorkbook()
    Dim strConnect As String
    Dim cn As Object
    Dim rs As Object
    Dim strPath As String

    strPath = CurrentProject.Path & Chr(92) & "temp.xls"
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source='" & strPath & "';" _
        & "Extended Properties='Excel 8.0';"
    Set cn = CreateObject("ADODB.Connection")
    cn.ConnectionString = strConnect
    cn.Open
    Set rs = cn.OpenSchema(20) '20 = adSchemaTables '
    Debug.Print "TABLE_NAME"
    Do While Not rs.EOF
        Debug.Print rs!TABLE_NAME
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

回复收藏 0 原文

~没有更多了~