将Access Query导出到Excel [英] Exporting Access Query to Excel

问题描述

我正在尝试发送在我单击基于参数查询的表单上的按钮时运行的查询结果。代码运行时没有任何错误，但没有任何内容导出到我的Excel电子表格中，我无法弄清楚为什么有人会在我的代码中看到我出错的地方。我目前正在使用DAO Recordset。以下是我的代码：

[code]


Private Sub btnJE_Click（）''将qryJE结果导出为ex​​cel

错误GoTo Err_btnJE_Click


MsgBox ExportQuery，vbInformation，Export Finished

Exit_btnJE_Click：

退出Sub


Err_btnJE_Click：

MsgBox Err.Description，vbCritical，Error

Resume Exit_btnJE_Click


End Sub

公共函数ExportQuery（）As String

On Error GoTo err_Handler


''Excel对象变量

Dim appExcel As Excel.Application

Dim wbk As Excel.Workbook

Dim wks As Excel.Worksheet


Dim sTemplate As String

Dim sTempFile As String

Dim sOutput As String


昏暗的dbs作为DAO.Database

Dim rst作为DAO.Recordset

Dim sSQL As String

Dim IRecords As Long

昏暗iRow as Integer

Dim iCol As Integer

Dim iFld as Integer


Const cTabOne As Byte = 1

Const cTabTwo By Byte = 2

Const cStartRow As Byte = 3

Const cStartColumn As Byte = 1


DoCmd 。沙漏真实


''设置为打破所有错误

Application.SetOption错误陷阱，0


''从模板文件构建的干净文件开始

sTemplate = CurrentProject.Path& " \ JournalEntryTest.xls"

sOutput = CurrentProject.Path& " \ JournalEntryFormTest.xls"

如果Dir（sOutput）<> ""然后Kill sOutput

FileCopy sTemplate，sOutput


''创建Excel应用程序，工作簿和工作表以及数据库对象

设置appExcel =新的Excel.Application

appExcel.Visible = True

设置wbk = appExcel.Workbooks.Open（sOutput）


sSQL =" SELECT * FROM tblAllPerPayPeriodEarnings" &安培; vbCrLf& WHERE PG =''" &安培;表格（frmJE）。控制（cboADPCompany）。价值& &'''AND（''LOCATION＃''）=''" &安培;表格（frmJE）。控制（cboLocationNo）。值和& "''和CHECK_DT在＃"之间&安培;表格（frmJE）。控制（txtFrom）。值和& ＃AND＃ &安培;形式（frmJE）。控制（txtTo）。值和& "＃" &安培; " ;;"


设置dbs = CurrentDb

设置rst = dbs.OpenRecordset（sSQL，dbOpenSnapshot）

如果不是rst.BOF那么

rst.MoveFirst

''对于这个模板，数据必须放在电子表格的相应单元格中

Do while notrst.EOF

with wbk

.Sheets（" JournalEntry"）。Range（" G3"）= rst.Fields（"分支号码）

。表格（JournalEntry）。范围（K15：K100）= rst.Fields（账户）

。表（JournalEntry）。范围（L15：L100）= rst.Fields（Sub Account）

.Sheets（" JournalEntry"）。范围（" O15： O100"）= rst.Fields（" SumOfGROSS"）

.Sheets（" JournalEntry"）。范围（" Q15：Q100"）= rst.Fields（" Account Description"）

.Sheets（" JournalEntry"）。范围（G3，K15：K100，L15：L10 0，O15：O100，Q15：Q100）。列.A utoFit

.SaveAs CurrentProject.Path& " \" &安培; rst.Fields（Branch Number）& .xls


结束

rst.MoveNext


循环

rst.Close


ExportQuery =" Total of" &安培;爱尔兰& "行处理。


exit_Here：

''清除所有对象（错误后续恢复）

设置wbk =没什么

appExcel.Quit

设置appExcel =没什么

设置rst =无什么

设置dbs = Nothing

DoCmd.Hourglass False

退出函数


err_Handler：

ExportQuery = Err.Description

简历exit_Here

结束如果

结束功能


[\ Code]

解决方案

那里。


你应该问自己遇到这种问题的第一个问题是 - 我的查询是否会返回记录？ "

• 在
  

  展开 | 选择 | Wrap | 行号

1。好吧，我弄清楚为什么没有输入电子表格。所有记录都互相覆盖。但在确保查询返回值后，我遇到了一个新问题。只有一个值返回到电子表格而不是查询生成的所有结果。同样使用下面的代码我收到错误消息：{Run-

time error''1004''：应用程序定义或对象定义错误。}同样

当我使用以下代码时出现消息

展开 | 选择 | Wrap | < span class =codeLinkonclick =LineNumbers（this）;>行号

那里。


虽然你可以期待这个领域的名字

....总和（tblAllPerPayPeriodEarnings.GROSS）......

将是[GROSS]，但事实并非如此。 Access将为其提供默认名称，如[Expr1]。

要明确地命名查询字段，请使用AS，例如

展开 | 选择 | Wrap | 行号

I am trying to send the results of a query that runs when I click a button on my form that is based on a parameter query. The code runs without any errors but nothing is exported into my excel spreadsheet and I can''t figure out why does anyone see where I went wrong in my code. I am currently using DAO Recordset. Here is what my code looks like:
[code]

Private Sub btnJE_Click() ''Exports qryJE results into excel

On Error GoTo Err_btnJE_Click

MsgBox ExportQuery, vbInformation, "Export Finished"

Exit_btnJE_Click:
Exit Sub

Err_btnJE_Click:
MsgBox Err.Description, vbCritical, "Error"
Resume Exit_btnJE_Click

End Sub
Public Function ExportQuery() As String
On Error GoTo err_Handler

''Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim IRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTabOne As Byte = 1
Const cTabTwo As Byte = 2
Const cStartRow As Byte = 3
Const cStartColumn As Byte = 1

DoCmd.Hourglass True

''Set to break on all errors
Application.SetOption "Error Trapping", 0

''Start with clean file built from template file
sTemplate = CurrentProject.Path & "\JournalEntryTest.xls"
sOutput = CurrentProject.Path & "\JournalEntryFormTest.xls"
If Dir(sOutput) <> "" Then Kill sOutput
FileCopy sTemplate, sOutput

''Create the Excel Application, Workbook and Worksheet and Database object
Set appExcel = New Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Open(sOutput)

sSQL = "SELECT * FROM tblAllPerPayPeriodEarnings " & vbCrLf & "WHERE PG = ''" & Forms("frmJE").Controls("cboADPCompany").Value & "'' AND (''LOCATION#'') = ''" & Forms("frmJE").Controls("cboLocationNo").Value & "'' AND CHECK_DT Between #" & Forms("frmJE").Controls("txtFrom").Value & "# AND #" & Forms("frmJE").Controls("txtTo").Value & "#" & ";"


Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
If Not rst.BOF Then
rst.MoveFirst
''For this template, the data must be placed in the appropriate cells of the spreadsheet
Do While Not rst.EOF
With wbk
.Sheets("JournalEntry").Range("G3") = rst.Fields("Branch Number")
.Sheets("JournalEntry").Range("K15:K100") = rst.Fields("Account")
.Sheets("JournalEntry").Range("L15:L100") = rst.Fields("Sub Account")
.Sheets("JournalEntry").Range("O15:O100") = rst.Fields("SumOfGROSS")
.Sheets("JournalEntry").Range("Q15:Q100") = rst.Fields("Account Description")
.Sheets("JournalEntry").Range("G3,K15:K100,L15:L10 0,O15:O100,Q15:Q100").Columns.AutoFit
.SaveAs CurrentProject.Path & "\" & rst.Fields("Branch Number") & ".xls"

End With
rst.MoveNext

Loop
rst.Close

ExportQuery = "Total of " & IRecords & " rows processed."

exit_Here:
''Cleanup all objects (resume next on errors)
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

err_Handler:
ExportQuery = Err.Description
Resume exit_Here
End If
End Function

[\Code]

解决方案

Hi, there.

The first question you should ask yourself having encountered such a kind of problem is - "Does my query return records?"

• Toggle breakpoint on the next line after
  

  Expand|Select|Wrap|Line Numbers

1. Okay I figured out why nothing was being inputted to the spreadsheet. All the records kept overwriting each other. But I have a new problem after making sure that the query returned values. Only one value is returned to the spreadsheet not all the results produced by the query. Also using the code below I get the error message: {Run-
time error ''1004'': Application-defined or object-defined error.} This same
message appears when I use the following pieces of code

Expand|Select|Wrap|Line Numbers

Hi, there.

Though you may expect the name of the field
.... Sum(tblAllPerPayPeriodEarnings.GROSS)...
will be [GROSS], it isn''t so. Access will give it default name like [Expr1].
To explicitely name query field use "AS", e.g.

Expand|Select|Wrap|Line Numbers

这篇关于将Access Query导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！