有没有办法将 Excel 电子表格作为记录集导入? [英] Is there a way to import an Excel spreadsheet as a recordset?

问题描述

我有一个 Access 数据库，该数据库将从另一个系统导出的人员数据汇总并格式化为 Excel 文件.当前流程要求用户遵循以下步骤/规则:

I have an Access database that summarizes and formats personnel data exported from another system as an Excel file. The current process requires the user to follow the following steps/rules:

1. 文件必须命名为 Alpha_Roster.xls* 或 Alpha_Roster.xlsx 并与数据库位于同一文件夹中
2. 必须删除前两行，以便将列标题从第 3 行移到第 1 行
3. 必须从 Alpha_Roster 文件中删除所有多余"列，因为这些列中的数据未被使用，并且 DoCmd.TransferSpreadsheet 如果它们存在就会失败

1. file must be named Alpha_Roster.xls* or Alpha_Roster.xlsx and located in the same folder as the database
2. the top two rows must be deleted as to bring the column headers from row 3 to row 1
3. all "excess" columns must be deleted from the Alpha_Roster file as the data in those columns are not used and DoCmd.TransferSpreadsheet will fail if they're present

如您所见，当您只处理几个目标列时，这还不算太糟糕，但我们正在寻找特定于 25 列左右的信息，并且它们与多余的列混合在一起我们需要删除的列(导出的文件中有 76 列)，因此准备更新文件是一个相当乏味的过程，我们必须每周进行一次.

As you can see, this isn't too bad when you're dealing with just a couple of target columns, but we're looking for information from specifically 25 or so columns, and they're mixed in with the excess columns we need to remove (there are 76 columns in the exported file), so it's a rather tedious process to prepare the file for update, which we're having to do weekly.

一旦标题被排序，脚本从 AlphaList 表中擦除数据，然后使用 DoCmd.TransferSpreadsheet acImport, 10, "AlphaList", FilePath &"Alpha_Roster", True 将新数据移入.

Once the headers are sorted, the script wipes the data from the AlphaList table, then uses DoCmd.TransferSpreadsheet acImport, 10, "AlphaList", FilePath & "Alpha_Roster", True to move the new data in.

因此，我有几个更新目标:

So with this, I've got a few goals for the update:

1. 删除整个文件名/位置位 - 完成，使用 Application.FileDialog(msoFileDialogFilePicker) 和一些 If 语句来检查我们正在拉哪个扩展
2. 删除编辑 Access VBA 代码的要求，如果导出数据的布局发生变化 - 考虑完成，使用循环提取数据库所需的字段以组装 SQL 查询以更新数据库
3. 不再需要编辑导出的 Excel 文件.

这让我明白了为什么我在这里 - 我正在寻找一种方法来基本上将我的 Excel 文件拉到一个临时记录集或表中，然后我可以针对它运行 SQL 查询.不确定我是否可以完全避免删除前两行，但这样做比删除所有列要好得多.我想我已经到了，但我在谷歌上搜索的所有内容都与我正在寻找的解决方案相差甚远.

And this now brings me to why I'm here - I'm looking for a way to basically pull my Excel file into a temporary recordset or table that I can then run the SQL query against. Not sure if I can completely get away from removing the top two rows, but just doing that is far better than having to wipe out all of the columns. I think I'm about there, but everything I'm Googling is coming up just a bit short of the solution I'm looking for.

这是我目前使用的测试代码的内容:

Here's what I have so far for the test code I'm playing with:

    Dim cnn As New ADODB.Connection
    Dim fDialog As Office.FileDialog
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
        .Title = "Select Alpha Roster File"
        .AllowMultiSelect = False
        .ButtonName = "Open"
        .Filters.Clear
        .Filters.Add "Excel Workbook", "*.xls;*.xlsx"
        If .Show = True Then
            SelFile = .SelectedItems.Item(1)
        End If
    End With
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
             "Data Source=" & SelFile & ";" & _
             "Extended Properties='Excel 12.0 XML;HDR=Yes'"

我的不足之处是如何从这里获得某种可以运行更新查询的记录集?或者我可以从这里运行我的查询 ("SELECT " & qryStr & " from ...")?还是我在这里完全走错了路?

Where I'm falling short is how do I get from here to some kind of a recordset that I can run my update query against? Or can I run my query ("SELECT " & qryStr & " from ...") from here? Or am I completely on the wrong track here?

请注意，出于某种原因，每当我尝试定义 ADODB.Recordset 时，代码检查器都会使 Access 完全崩溃，因此不幸的是，我无法完全反映某些我在网上看到的例子.

As a note, for some reason or another any time I try to define an ADODB.Recordset the code checker completely crashes Access, so unfortunately I haven't been able to completely mirror some of the examples I've seen online.

需要考虑的一些事情 - 我正在尝试在未来证明这一点，因为我不知道可以将哪些字段添加到导出的文件中.这就是为什么我不只是直奔DoCmd.TransferSpreadsheet.虽然将整个文件转储到一个表中，其中已经设置了标题以匹配我们当前拥有的内容将是最简单的选择，但就标题而言，源文件中的第二个变化，过程中断.此外，虽然我可能精通技术，可以拆开代码并弄清楚事情的发展方向，但这里的最终最终用户......好吧，我们只能说他们的技能水平非常基础.他们不在乎它是如何工作的，只关心它的工作原理.

Some things to consider - I'm trying to future proof this just a bit, since I don't know what fields may be added to the exported file. This is why I'm not just diving straight for DoCmd.TransferSpreadsheet. While dumping the entirety of the file into a table with the headers already setup to match what we currently have would be the simplest option, the second something changes in the source file as far as the headers are concerned, the process breaks. Also, while I may be tech savvy enough to tear the code apart and figure out where things are going sideways, the ultimate end users here... well, let's just say their skill level is pretty basic. They don't care how it works, just that it works.

推荐答案

您可以使用 SheetName$ 查询工作表.(需要美元符号)

You can query the sheet by using SheetName$. (The dollar sign is required)

Dim db As ADODB.Connection, rs As ADODB.Recordset
Set db = New ADODB.Connection
Set rs = New ADODB.Recordset
ExelFileFullPath = "FilePath\filename.xlsx"
db.Open ("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & ExelFileFullPath & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;""")
SQL = "SELECT * FROM [SheetName$]"
rs.Open SQL, db, adOpenKeyset, adLockReadOnly

您的列必须具有具有唯一名称的标题以避免出现问题.

Your columns must have headers with Unique names to avoid issues.

这篇关于有没有办法将 Excel 电子表格作为记录集导入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！