访问未导出到EXCEL的vba代码“参数太少".预期为1.". [英] Access vba code not exporting to EXCEL "Too few parameters. Expected 1."

问题描述

我以前从其他Access数据库之一中获得了一些代码，效果很好.目的是将放入make表中的结果复制到空白的excel表中，然后格式化列.

I have some code I previous got from one of my other Access Databases, which works fine. It intended purpose was to copy the results that were put into a make table into a blank excel sheet then format the columns.

但是，我复制了代码并对其进行了一些调整.我正在尝试使用复制的代码将结果仍然导出到空白的excel工作表中，但是，这一次我不是从make表中进行选择，而是从select查询中进行处理.我不确定是否有可能，但是没有其他方法可以使它进入make表，它必须是一个select查询.有人可以提供任何帮助吗?我现在拥有的代码是:

I however copied the code and tweaked it a little. What I am trying to do with the copied code is still export my results into a blank excel sheet, however, this time I am not coping from a make table but from a select query. I am not sure if it is possible but there is no other way for me to make this into a make table, it has to be an select query. Is there any way someone can help? The code I have now is:

Option Compare Database
Public Function Export_EXCEL()


Dim dbs As DAO.Database
Dim Response As Integer
Dim strSQL As String
Dim Query1 As String
Dim LTotal As String
  Dim Excel_App As Excel.Application 'Creates Blank Excel File
  Dim strTable As String ' Table in access
    Dim queryDelete As String 'SQL to delete records in local table
    Dim strAssigned As DAO.Recordset

'-------------------------------------------------------------------------------
strTable = "Select * From cso_sup_SETUP" 'Access Query I am trying to copy

    Set Excel_App = CreateObject("Excel.Application")
        Set dbs = CurrentDb
'-------------------------------------------------------------------------------
Dim rs As DAO.Recordset
Set rs = dbs.OpenRecordset(strTable)

    Excel_App.Visible = True

Dim wkb As Excel.Workbook
  Set wkb = Excel_App.Workbooks.Add

Dim rg As Excel.Range
Dim i As Long
' Add the headings
For i = 0 To rs.Fields.Count - 1
    wkb.Sheets(1).Cells(1, i + 1).Value = rs.Fields(i).Name
Next i

Set rg = wkb.Sheets(1).Cells(2, 1)
rg.CopyFromRecordset rs

' make pretty
rg.CurrentRegion.EntireColumn.AutoFit


Set rs = Nothing
Set wkb = Nothing
Set dbs = Nothing

End Function

这是下面的修改代码:

Option Compare Database
Public Function Export_EXCEL()


Dim dbs As DAO.Database
Dim Response As Integer
Dim strSQL As String
Dim Query1 As String
Dim LTotal As String
  Dim Excel_App As Excel.Application 'Creates Blank Excel File
  Dim strTable As String ' Table in access
   Dim queryDelete As String 'SQL to delete records in local table
   Dim strAssigned As DAO.Recordset

 Dim rs As DAO.Recordset
 Dim db As DAO.Database
 Dim prm As DAO.Parameter
 Dim qdf As DAO.QueryDef

 '-------------------------------------------------------------------------------
 strTable = "Select * From cso_sup_SETUP" 'Access Query I am trying to copy

Set Excel_App = New Excel.Application
    Set dbs = CurrentDb
 '-------------------------------------------------------------------------------

Set rs = QuerDef.OpenRecordset(strTable)

Excel_App.Visible = True

Dim wkb As Excel.Workbook
  Set wkb = Excel_App.Workbooks.Add

Dim rg As Excel.Range
Dim i As Long
 ' Add the headings
For i = 0 To rs.Fields.Count - 1
   wkb.Sheets(1).Cells(1, i + 1).Value = rs.Fields(i).Name
 Next i

Set rg = wkb.Sheets(1).Cells(2, 1)
rg.CopyFromRecordset rs

' make pretty
rg.CurrentRegion.EntireColumn.AutoFit


 Set rs = Nothing
 Set wkb = Nothing
 Set dbs = Nothing

 End Function

推荐答案

您有一个名为"cso_sup_SETUP" 的Access QueryDef(已保存的查询).该查询引用了表单上的控件...类似于Forms!MyForm!MyTextbox. 当您在Access查询设计器中打开该查询时，它可以检索查询所需的文本框值.但是，当您尝试将查询与DAO OpenRecordset方法一起使用时，它无法检索文本框值，并且将Forms!MyForm!MyTextbox视为未提供值的参数.

You have an Access QueryDef (a saved query) named "cso_sup_SETUP". That query references a control on a form ... something like Forms!MyForm!MyTextbox. When you open that query in the Access query designer, it can retrieve the text box value the query needs. However when you attempt to use the query with the DAO OpenRecordset method, it is unable to retrieve the text box value and treats Forms!MyForm!MyTextbox as a parameter for which you have not supplied a value.

您可以通过将对象变量设置为QueryDef，然后在调用QueryDef.OpenRecordset方法之前将表单控件的值提供为Parameter来加载Recordset.

You can load your Recordset by setting an object variable to the QueryDef and then supplying the form control's value as a Parameter before calling the QueryDef.OpenRecordset method.

请注意，您的问题实际上与Excel没有任何关系---纯粹是Access问题.因此，这是Access VBA代码，应该为您提供我认为您需要的Recordset.建议您在代码模块中创建一个新过程进行测试.工作完成后，要么添加原始过程中的Excel位，要么修改原始过程以使用它.

Note your problem does not actually have anything to do with Excel --- it's a purely Access problem. So here is Access VBA code which should give you the Recordset I think you need. Suggest you create a new procedure in your code module to test this. Once you have it working, either add the Excel bits from the original procedure or adapt the original to use this.

还强烈建议您在模块的声明部分中添加Option Explicit.将其放在Option Compare Database

Also strongly suggest you add Option Explicit to your module's Declarations section. Put it on a new line immediately below Option Compare Database

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("cso_sup_SETUP")
For Each prm In qdf.Parameters
    prm.value = Eval(prm.Name)
Next
Set rs = qdf.OpenRecordset(dbOpenDynaset)

这篇关于访问未导出到EXCEL的vba代码“参数太少".预期为1.".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！