vba 如何将单个记录从 Access 导出到 Excel 中的特定单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20810306/
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
How do I export a single record from Access to specific cells in Excel
提问by user3141003
I am trying to export the record displayed on a form in Access to an Excel template which I would then rename and use the data in further calculations. I want to click a button on the form to transfer the record to Excel, I rename it and be done. The data fields will need to go to specific cells in the spreadsheet. Each record represents a seasoning formula with fields as such: Formula_name Formula_number Date_entered Ingredient1 Amount1 Ingredient2 Amount2 Ingredient3 And so on
我正在尝试将 Access 中表单上显示的记录导出到 Excel 模板，然后我将重命名该模板并在进一步计算中使用该数据。我想单击表单上的一个按钮将记录传输到 Excel，我重命名它并完成。数据字段将需要转到电子表格中的特定单元格。每条记录代表一个调味公式，其字段如下： Formula_name Formula_number Date_entered Ingredient1 Amount1 Ingredient2 Amount2 Ingredient3 等等
The amounts are a percent of total amount. Ingredients and amounts need to be in columns. I will send the newly named excel sheet (Batch Sheet) to a pc in production for pulling, weighing and lot code recording. I have looked at vba but don't have time to learn it in time for this project so I am hoping to just cut and paste the code or better-yet email the database and excel template to someone much smarter than me and have it working when returned. $$ Also can it work with different versions of MS Office. Thanks.
金额是总金额的百分比。成分和数量需要在列中。我会将新命名的 Excel 表（批处理表）发送到生产中的电脑，用于提取、称重和批号记录。我已经看过 vba，但没有时间及时为这个项目学习它，所以我希望只是剪切和粘贴代码或更好的 - 将数据库和 excel 模板通过电子邮件发送给比我更聪明的人并让它工作回来的时候。$$ 还可以与不同版本的 MS Office 一起使用。谢谢。
回答by serakfalcon
This is not exactly a trivial 'cut and paste' problem unless you really want a simple solution without any customization.
这不是一个微不足道的“剪切和粘贴”问题，除非您真的想要一个没有任何自定义的简单解决方案。
Here is a link to an article that shows how to do it: http://www.accessibledatasolutions.com/articles11/AccessToExcel.htm
这是一篇文章的链接，显示了如何做到这一点：http: //www.accessibledatasolutions.com/articles11/AccessToExcel.htm
回答by Graffl
In general you need to use something like the following code. You need to adjust the paths and then the SQL statement and you can put the extracted values from Access wherever you want. Maybe you can start with this and get some easy SQL extraction running.
通常，您需要使用类似以下代码的内容。您需要调整路径，然后调整 SQL 语句，您可以将从 Access 中提取的值放在您想要的任何位置。也许您可以从这个开始并运行一些简单的 SQL 提取。
Sub ConnectDatabase()
Dim con As New ADODB.Connection
Dim connected As Boolean
Dim rstAnswer As New ADODB.Recordset
Dim RootPath, DBPath As String
Dim tempString As String
connected = False
RootPath = "C:\Test\"
DBPath = RootPath & "Test.accdb"
con.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBPath & ";"
connected = True
rstAnswer.Open "SELECT CustomerName From tblCustomers " & _
"WHERE tblCustomers.Country = '" & Denmark & "';", con, adOpenKeyset, adLockOptimistic
Do Until rstAnswer.EOF
tempString = CStr(rstAnswer!CustomerName)
Application.ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = tempString
rstAnswer.MoveNext
Loop
rstAnswer.Close
con.Close
connected = False
End Sub
