vba 有没有办法用 Access 数据库中的数据自动填充 Excel 单元格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3858279/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to autofill Excel cells with data from an Access Database?
提问by
Basically I have a lot of historical data, consisting of IPs, Contacts, and various other fields. Originally we kept all this data in an Excel spreadsheet. I recently created an Access Database with this historical data. Now I am wondering if there is any way to use VBA or something else to autofill fields in the Excel spreadsheet using data from the Access database. Example, I put in an IP Address and a search is done for matches in the database and the Contacts field is filled out automatically. If anyone has an idea on this please let me know.
基本上我有很多历史数据，包括 IP、联系人和其他各种领域。最初我们将所有这些数据保存在一个 Excel 电子表格中。我最近使用这些历史数据创建了一个 Access 数据库。现在我想知道是否有任何方法可以使用 VBA 或其他方法使用 Access 数据库中的数据自动填充 Excel 电子表格中的字段。例如，我输入一个 IP 地址并在数据库中搜索匹配项，并自动填写联系人字段。如果有人对此有任何想法，请告诉我。
EDIT: What I'm trying to do is have the field auto-populate with the information from the Access database when I fill in the cell in the Source IP column in the spreadsheet. I import these IPs in a csv file and paste them into the spreadsheet. We have to add anywhere from 10-100 IPs and I'm not wanting to run a Query on each individual one. I'm wanting to automate the process we have right now, so basically I cut and paste, a macro or something runs and the remaining fields auto-populate with the data that's in Access. Edit/Delete Message
编辑：当我填写电子表格中 Source IP 列中的单元格时，我想要做的是让该字段自动填充 Access 数据库中的信息。我将这些 IP 导入一个 csv 文件并将它们粘贴到电子表格中。我们必须添加 10-100 个 IP，我不想对每个 IP 运行查询。我想自动化我们现在拥有的过程，所以基本上我剪切和粘贴，运行宏或其他东西，其余字段自动填充 Access 中的数据。编辑/删除消息
采纳答案by Waller
It sounds as though you need to write an Excel macro to retrieve data from your Access database, based on data (eg. IP Address) already entered into the spreadsheet.
听起来好像您需要编写一个 Excel 宏来根据已输入电子表格的数据（例如 IP 地址）从 Access 数据库中检索数据。
EDIT: if I understand the edited question correctly, it sounds as though all the database values will have already been pasted into the spreadsheet, so that it won't be necessary to re-query the database. In which case, the contacts field can be populated using the Excel VLOOKUP function.
编辑：如果我正确理解了编辑的问题，听起来好像所有数据库值都已粘贴到电子表格中，因此无需重新查询数据库。在这种情况下，可以使用 Excel VLOOKUP 函数填充联系人字段。
回答by Waller
Could you provide some more information, I do not understand your question.
您能否提供更多信息，我不明白您的问题。
You have imported your information into your database, surely you just need to run a query which will display what you need. There does not seem to be any data manipulation required here at all, VBA or otherwise.
您已将信息导入数据库，当然您只需要运行一个查询即可显示您需要的信息。这里似乎根本不需要任何数据操作，VBA 或其他。
Set your IP address to your primary key, and use it to link into your other tables?
将您的 IP 地址设置为您的主键，并使用它来链接到您的其他表？
Reply back here with some more information and I will try to help further.
在这里回复更多信息，我会尽力提供更多帮助。
EDIT due to more information, I would consider importing Excel to Access and not the other way around, this will be much more manageable and will allow greater flexability.
编辑由于更多信息，我会考虑将 Excel 导入到 Access 而不是相反，这将更易于管理并允许更大的灵活性。
Import Excel sheet to access with the above code, play about in there by linking PK's then export back out to Excel for your users
导入 Excel 表格以使用上述代码进行访问，通过链接 PK 在其中播放，然后为您的用户导出回 Excel
