如何在 Access VBA/SQL 中创建循环以选择记录并输出到 Excel？

How do I create a loop in Access VBA/SQL to select records and output to Excel?

提问by AVN

I need some help on writing some VBA/SQL code into a module in Access, which should do the following:

我需要一些帮助来将一些 VBA/SQL 代码写入 Access 中的模块，它应该执行以下操作：

• Loop through Table 'Schools', with just one field (SchoolName).

• Use each value in the first table to select records from Table 'ChildData', with several fields about individual children, including which school they attend (SchoolName).

• For each school, export the data for its attending children into a separate Excel workbook named for that school.

• 循环遍历表'Schools'，只有一个字段（SchoolName）。

• 使用第一个表中的每个值从表'ChildData' 中选择记录，其中有几个关于个别孩子的字段，包括他们就读的学校 ( SchoolName)。

• 对于每所学校，将就读儿童的数据导出到以该学校命名的单独 Excel 工作簿中。

Can anyone give me any starting points? Please be aware that I know very little about either VBA or SQL and am pretty much starting from scratch, therefore even the most basic explanations would be very helpful!

任何人都可以给我任何起点吗？请注意，我对 VBA 或 SQL 知之甚少，而且几乎是从头开始，因此即使是最基本的解释也会非常有帮助！

Thanks, AVN

谢谢，AVN

回答by David-W-Fenton

I can't see anything in your question that indicates you need anything complicated.

我在你的问题中看不到任何表明你需要复杂的东西。

The first step is to create a query that joins the schools and students and when that runs, export to Excel via the menus. If that works, then you need to alter it so that you are outputting one school at a time. The "easy" way to do this would be to add to your query a parameter on School so that each time the query runs, you're displaying only one school.

第一步是创建一个连接学校和学生的查询，并在运行时通过菜单导出到 Excel。如果可行，那么您需要对其进行更改，以便一次输出一所学校。执行此操作的“简单”方法是在查询中添加一个关于 School 的参数，以便每次运行查询时，您只显示一所学校。

Now, if you want to automate that, it becomes more complicated because the parameter will get in the way.

现在，如果你想自动化，它会变得更加复杂，因为参数会妨碍。

So, my suggestion would be to create an unbound form that has a combo box on it that displays the list of schools. You then use a reference to that combo box as criteria for your query, and use that to drive the code behind a command button:

所以，我的建议是创建一个未绑定的表单，上面有一个显示学校列表的组合框。然后，您可以使用对该组合框的引用作为查询条件，并使用它来驱动命令按钮后面的代码：

  Private Sub cmdOutputSchool_Click()
    If IsNull(Me!cmbSchool) Then
       MsgBox "You must first choose a school from the dropdown.", _
         vbExclamation, "Choose a school"
    Else
       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
         "qrySchoolOutput", CurrentProject.Path & "\" & Me!cmbSchool & ".xls"
    End If
  End Sub

Now, this doesn't automate the process, but it at least makes it easy to output each report in succession.

现在，这不会使过程自动化，但它至少可以轻松地连续输出每个报告。

If you wanted it to be automated, one easy way to do that would be to bind the form to the Schools table, and bind your query to the display of the School Name on the form. Then you could have the command button walk through the records of the form and output the report for each school:

如果您希望它自动化，一种简单的方法是将表单绑定到 Schools 表，并将您的查询绑定到表单上学校名称的显示。然后你可以让命令按钮遍历表单的记录并输出每个学校的报告：

  Private Sub cmdOutputSchool_Click()
    With Me.RecordsetClone
      .MoveFirst
      Do Until .EOF
        Me.Bookmark = .Bookmark
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
          "qrySchoolOutput", CurrentProject.Path & "\" & Me!SchoolName & ".xls"
        .MoveNext
      Loop
    End With
  End Sub

This will output a spreadsheet for each school in the table you've bound to the form.

这将为您绑定到表格的表格中的每所学校输出一个电子表格。

回答by Oorang

As mentioned, you can use "" inside a string to get a quote. Example:

如前所述，您可以在字符串中使用 "" 来获取引号。例子：

MsgBox "I am an ""Example""."

As for the other ,you don't really need VBA to do this. You can do it with a query:

至于其他，你真的不需要 VBA 来做到这一点。你可以用一个查询来做到这一点：

SELECT ChildData.* INTO [Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\Example.xls].[MyWorksheet]
FROM ChildData INNER JOIN Schools ON ChildData.SchoolName = Schools.SchoolName;

You can of course do the same thing from VBA if you really want to like so:

如果你真的想这样做，你当然可以从 VBA 做同样的事情：

CurrentDB.Execute "SELECT ChildData.* INTO [Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\Example.xls].[MyWorksheet] FROM ChildData INNER JOIN Schools ON ChildData.SchoolName = Schools.SchoolName;"

Still another way would be to create a select query that pulled your data and then use DoCmd.OutputTo:

还有一种方法是创建一个选择查询来提取您的数据，然后使用 DoCmd.OutputTo：

DoCmd.OutputTo acOutputQuery,"MyQuery",acFormatXLS,"C:\Test.xls"

You can also use DoCmd.TransferSpreadsheet:

您还可以使用 DoCmd.TransferSpreadsheet：

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "MyQuery", "C:\Test.xls", True

回答by Oorang

It would be simpler to create a query which joins the two tables on school, and then use a pivot in excel with school as the page, and use the show pages to get each school on a separate page

创建一个连接学校上的两个表的查询会更简单，然后在 excel 中使用一个以学校为页面的数据透视表，并使用显示页面将每个学校放在一个单独的页面上

If you want to do it the more complicated way then in VBA you need a recordset from the schools table which shows all the schools, and a recordset from the childdata with the required fields, and a parameter against the school field.

如果您想以更复杂的方式进行操作，那么在 VBA 中，您需要来自显示所有学校的学校表的记录集，以及来自具有必填字段的 childdata 的记录集，以及针对学校字段的参数。

Then in VBA you use a do loop control, which proceeds while school.EOF=false and for each school record get the childdata recordset, and put it to Excel

然后在 VBA 中使用 do 循环控件，该控件在 school.EOF=false 时进行，并为每个学校记录获取 childdata 记录集，并将其放入 Excel

I would tend to do this in Excel VBA and use copyfromrecordset to put the data into Excel -simple enough to create a new sheet for each school and set the sheet name to school. You would need to add the relevant references to the VBA project - DAO or ADO - to be able to connect to the Access data.

我倾向于在 Excel VBA 中执行此操作并使用 copyfromrecordset 将数据放入 Excel - 简单到可以为每所学校创建一个新工作表并将工作表名称设置为学校。您需要将相关引用添加到 VBA 项目 - DAO 或 ADO - 才能连接到 Access 数据。