如何将单个记录从Access导出到Excel中的特定单元格 [英] How do I export a single record from Access to specific cells in Excel

问题描述

我正在尝试导出Excel中的表单中显示的记录，然后我将重命名并使用数据进一步的计算。我想单击表单上的一个按钮将记录传输到Excel，我重命名并完成。数据字段将需要转到电子表格中的特定单元格。每个记录代表一个调味公式，如下所示：
Formula_name
Formula_number
Date_entered
成分1
金额1
成分2
金额2
成分3
等等

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

金额是总金额的百分比。成分和数量需要在列中。我将把新命名的excel表（批量表）发送到生产中用于拉，称和批代码记录的pc。我已经看过vba，但是没有时间及时了解这个项目，所以我希望只是剪切和粘贴代码或更好 - 但是将数据库和excel模板发送给比我更聪明的人，并使其工作返回时。 $$也可以使用不同版本的MS Office。谢谢。

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.

推荐答案

一般来说，您需要使用以下代码。您需要调整路径，然后调整SQL语句，您可以将来自Access的提取值放在任何位置。也许你可以从这开始，并获得一些简单的SQL提取运行。

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.

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

这篇关于如何将单个记录从Access导出到Excel中的特定单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！