我希望将特定的访问表字段导出到特定的 excel 单元格。我的 vba 代码不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20253176/
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
I'm looking to export specific access table fields to specific excel cells. My vba code isn't working
提问by chris
Below is the code I'm currently am trying to use. Basically I want to pick the fields from a table and export them to a specific cell range in excel. When I run this code in Access I'm not getting any errors but I'm also not getting the 2nd excel temp file saved with the fields I selected in the cells I specified. Thanks for any help. Code below has been updated to reflect what is now working. See below comments that helped resolve the issues I ran into.
下面是我目前正在尝试使用的代码。基本上我想从表中选择字段并将它们导出到 excel 中的特定单元格范围。当我在 Access 中运行此代码时，我没有收到任何错误，但我也没有收到与我在指定单元格中选择的字段一起保存的第二个 excel 临时文件。谢谢你的帮助。下面的代码已更新，以反映现在的工作。请参阅以下有助于解决我遇到的问题的评论。
Option Explicit
'Enter Location of your Template Here
Const ExcelTemplate = "C:\Users\Desktop\Export Excel\Temp.xlsx"
'Enter the Folder Directory to save results to
Const SaveResutsFldr = "C:\Users\Desktop\Export Excel\"
Sub CreateWorkbook()
Dim SaveAsStr As String
Dim ExcelApp, WB As Object
Dim i As Integer
Dim c As Integer
c = DCount("*", "tbldata") + 4
'Create Reference to Run Excel
Set ExcelApp = CreateObject("Excel.Application")
'Create Reference to your Table
Dim T As Recordset
Set T = CurrentDb.OpenRecordset("tblData")
'Loop through all Record on Table
If Not (T.BOF And T.EOF) Then
T.MoveFirst
End If
Do While Not T.EOF
'Open Your Excel Template
Set WB = ExcelApp.Workbooks.Open(ExcelTemplate)
For i = 5 To c
'Enter your data from your table here to the required cells
WB.Worksheets("sheet1").Range("B" & i) = T("Field1")
WB.Worksheets("sheet1").Range("C" & i) = T("field2")
WB.Worksheets("sheet1").Range("D" & i) = T("field3")
WB.Worksheets("sheet1").Range("E" & i) = T("field4")
'Repeat this line for each piece of data you need entered
'Changing the Sheet name, cell range, a field name as per your requirements
'WB.Wor...
'WB.Wor...
T.MoveNext
Next i
i = i + 1
Loop
'Save and Close the Workbook
SaveAsStr = SaveResutsFldr & ("Temp1") & ".xlsx"
WB.SaveAs SaveAsStr
WB.Close
Set WB = Nothing
'Move to the Next Record
'Close down the Excel Application
ExcelApp.Quit
Set ExcelApp = Nothing
End Sub
回答by HansUp
Set a breakpoint on this line ...
在这一行设置断点...
Set T = CurrentDb.OpenRecordset("tblData")
You can set the breakpoint by pressing F9while the cursor is located anywhere on that line. Or right-click on the line, then select Toggle->Breakpoint from the shortcut menu. Either way you should see a reddish dot in the left margin next to that line.
F9当光标位于该行的任何位置时，您可以通过按 来设置断点。或者右键单击该行，然后从快捷菜单中选择 Toggle->Breakpoint。无论哪种方式，您都应该在该行旁边的左边距中看到一个红点。
Then run your code. When it hits the breakpoint, Access will go into Debug (break) mode. You can then execute one line at a time with the F8key, and follow the code.
然后运行你的代码。当它遇到断点时，Access 将进入调试（中断）模式。然后，您可以使用F8密钥一次执行一行，并按照代码进行操作。
You will discover the code within your Whileloop does not get executed due to the logic in the loop control ...
您会发现While由于循环控制中的逻辑，循环中的代码没有被执行......
While Not T.BOF And T.EOF
That says "enter the loop if BOFis False and EOF is True." When you first hit that line, you are on the first row of the recordset, so BOFis False, but EOFis also False. So when you ANDthose 2 values, the result is False ... and Access skips over the loop.
意思是“如果BOF为 False 且 EOF 为 True，则进入循环”。当您第一次点击该行时，您位于记录集的第一行，因此BOF为 False，但EOF也是 False。因此，当您使用AND这 2 个值时，结果为 False ...并且 Access 会跳过循环。
Here is a Access Immediate window session with a recordset whose current row is the first row.
这是一个 Access Immediate 窗口会话，其当前行是第一行的记录集。
' name and value of the first field in current row ...
? rs.Fields(0).Name, rs.Fields(0).Value
id 1
' this is the WHILE condition from your code ...
? Not rs.BOF And rs.EOF
False
Hope that all made sense. If still fuzzy, just start the loop like this ...
希望一切都说得通。如果仍然模糊，只需像这样开始循环......
If Not (T.BOF And T.EOF) Then
T.MoveFirst
End If
Do While Not T.EOF
And end it like this ...
然后就这样结束...
T.MoveNext
Loop
The breakpoint you set is temporary. It does not get saved with the code. So it will be gone if you restart Access. If you want to get rid of it during the current session, just "toggle" it again.
您设置的断点是临时的。它不会与代码一起保存。因此，如果您重新启动 Access，它将消失。如果您想在当前会话期间摆脱它，只需再次“切换”它。
回答by dee
The problem is in the While-Condition, HTH.
问题出在 While 条件 HTH 中。
If you open a Recordset object containing no records, the BOF and EOF properties are set to True, and the Recordset object's RecordCount property setting is 0.
如果打开不包含记录的Recordset 对象，则BOF 和 EOF 属性设置为 True，并且 Recordset 对象的 RecordCount 属性设置为 0。
If (T.EOF = True And T.BOF = True) Then
MsgBox "Recordset object contains no records."
Exit Sub
End If
'Create Reference to Run Excel
Set ExcelApp = CreateObject("Excel.Application")
When you open a Recordset object that contains at least one record, the first record is the current record and the BOF and EOF properties are Falseand ...
当您打开包含至少一条记录的Recordset 对象时，第一条记录是当前记录，并且BOF 和 EOF 属性为 False和 ...
While (T.BOF = False And T.EOF = False)
... they remain False until you move beyond the beginning or end of the Recordset object' by using the MovePreviousor MoveNextmethod, respectively.
...在您分别 使用MovePrevious或MoveNext方法移动到 Recordset 对象的开头或结尾之前，它们将保持为 False。
