VBA - 从Access生成Excel文件（QueryTable） [英] VBA - Generate Excel File from Access (QueryTable)

问题描述

我有一个项目，基本上是为了生成使用VBA的Access中的按钮单击的Excel（Report）的目标。

此报告的内容是存储过程SQL Server数据库的结果。

错误行：

 使用MeuExcel.Worksheets（4）
 .QueryTables.Add连接：= rs，目的地：=。范围（A2）
结束
  

我得到的是：

 无效过程调用或参数（erro'5'）
  

完整代码（使用Remou编辑）用户提示：

  Sub GeraPlanilhaDT（）
 
 Dim MeuExcel As New Excel.Application 
 Dim wb As New Excel。工作簿
 
设置MeuExcel = CreateObject（Excel.Application）
 MeuExcel.Workbooks.Add 
 
 MeuExcel.Visible = True 
 
 Dim strNomeServidor，strBaseDados，strProvider，strConeccao，strStoredProcedure As String 
 
 strNomeServidor =m98\DE S; 
 strBaseDados =SGLD_POC; 
 strProvider =SQLOLEDB.1; 
 strStoredProcedure =SP_ParametrosLeads_DT
 
 strConeccao =Provider =& strProvider& Integrated Security = SSPI; Persist Security Info = True; Data Source =& strNomeServidor& 初始目录=& strBaseDados 
 
 Dim cnt As New ADODB.connection 
 Dim cmd As New ADODB.command 
 Dim rs As New ADODB.recordset 
 Dim prm As New ADODB.parameter 
 
 cnt.Open strConeccao 
 
 cmd.ActiveConnection = cnt 
 cmd.CommandType = adCmdStoredProc 
 cmd.CommandText = strStoredProcedure 
 cmd.CommandTimeout = 0 
 
设置prm = cmd.CreateParameter（DT，adInteger，adParamInput）
 cmd.Parameters.Append prm 
 cmd.Parameters（DT）。Value = InputBox （Digite oCódigoDT，Códigodo Distribuidor）
 
设置rs = cmd.Execute（）
 
 Dim nomeWorksheetPrincipal as String 
 nomeWorksheetPrincipal =Principal 
 
 Worksheets.Add（After：= Worksheets（Worksheets.Count））。Name = nomeWorksheetPrincipal 
 
 
 
带有MeuExcel.Worksheets（4） 
 .QueryTables.Add连接：= rs，目的地：=。范围（A2）
结束
 
 
 cnt.Close 
设置rs = Nothing 
设置cmd = Nothing 
设置strNomeServidor = Nothing 
 S et strBaseDados = Nothing 
设置strProvider = Nothing 
 
如果（ActiveSheet.UsedRange.Rows.Count> 1）然后
 FormataDadosTabela 
 Else 
 MsgBox（Nãofoi encontrado nenhum Distribuidor com esse DT）
 End If 
 
 
 End Sub 
  

奇怪的是，代码在Excel中运行但在Access中不起作用>

解决方案

在Access中，需要将Excel应用程序对象与Excel应用程序实例前缀，例如：

 使用MeuExcel.Worksheets（4）.QueryTables.Add（_ 
 connection = = recordset，_ 
目标：=范围（A2 ））
结束
  

此外，除非您有对Excel库的引用， ypu将需要为内置的Excel常量提供值。

使用对象的变量名称是个不错的主意。不要说：

  Dim recordset As recordset 
 Set recordset = New recordset 
  

说，例如：

  Dim rs As recordset 
  

或者更好：

  Dim rs As New ADODB.Recordset 
  

如果你有适当参考。然后，您可以跳过CreateObject。

编辑

提供者必须是Access OLEDB 10提供者，用于绑定记录集。这对我来说通过Access使用SQL Server创建一个数据表：

  strConnect =Provider = Microsoft.Access.OLEDB。 10.0; Persist Security Info = True; _ 
& Data Source = XYZ\SQLEXPRESS; Integrated Security = SSPI; _ 
& 初始目录= TestDB;数据提供者= SQLOLEDB.1
  

I have a project that basically the goal is to generate Excel (Report) starting the Click of a button in Access using VBA.

The contents of this report is the result of a Stored Procedure SQL Server Database.

the line of error:

With MeuExcel.Worksheets(4)
    .QueryTables.Add connection:=rs, Destination:=.Range("A2")
End With

I get is:

invalid procedure call or argument (erro '5')

Complete Code (Edited using Remou User tips):

Sub GeraPlanilhaDT()

Dim MeuExcel As New Excel.Application
Dim wb As New Excel.Workbook

Set MeuExcel = CreateObject("Excel.Application")
MeuExcel.Workbooks.Add

MeuExcel.Visible = True

Dim strNomeServidor, strBaseDados, strProvider, strConeccao, strStoredProcedure As String

strNomeServidor = "m98\DES;"
strBaseDados = "SGLD_POC;"
strProvider = "SQLOLEDB.1;"
strStoredProcedure = "SP_ParametrosLeads_DT"

strConeccao = "Provider=" & strProvider & "Integrated Security=SSPI;Persist Security Info=True;Data Source=" & strNomeServidor & "Initial Catalog=" & strBaseDados

Dim cnt As New ADODB.connection
Dim cmd As New ADODB.command
Dim rs As New ADODB.recordset
Dim prm As New ADODB.parameter

cnt.Open strConeccao

cmd.ActiveConnection = cnt
cmd.CommandType = adCmdStoredProc
cmd.CommandText = strStoredProcedure
cmd.CommandTimeout = 0

Set prm = cmd.CreateParameter("DT", adInteger, adParamInput)
cmd.Parameters.Append prm 
cmd.Parameters("DT").Value = InputBox("Digite o Código DT", "Código do Distribuidor")

Set rs = cmd.Execute()

Dim nomeWorksheetPrincipal As String
nomeWorksheetPrincipal = "Principal"

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = nomeWorksheetPrincipal



With MeuExcel.Worksheets(4)
    .QueryTables.Add connection:=rs, Destination:=.Range("A2")
End With


cnt.Close
Set rs = Nothing
Set cmd = Nothing
Set strNomeServidor = Nothing
Set strBaseDados = Nothing
Set strProvider = Nothing

If (ActiveSheet.UsedRange.Rows.Count > 1) Then
    FormataDadosTabela
Else
    MsgBox ("Não foi encontrado nenhum Distribuidor com esse DT")
End If


End Sub

The strange thing is that the code works when run in Excel but does not work in Access

解决方案

In Access, you need to prefix the Excel application objects with the Excel application instance, for example:

With MeuExcel.Worksheets(4).QueryTables.Add( _
    connection:=recordset, _
    Destination:=Range("A2"))
End With

Furthermore, unless you have a reference to the Excel library, ypu will need to provide the value for built-in Excel constants.

It is a very bad idea to use the name of objects for variables. Do not say:

Dim recordset As recordset
Set recordset = New recordset

Say, for example:

Dim rs As recordset

Or much better:

Dim rs As New ADODB.Recordset

If you have a suitable reference. You can then skip CreateObject.

EDIT

The provider must be the Access OLEDB 10 provider, as used to bind recordsets. This works for me to create a data table via Access using SQL Server:

strConnect = "Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=True;" _
& "Data Source=XYZ\SQLEXPRESS;Integrated Security=SSPI;" _
& "Initial Catalog=TestDB;Data Provider=SQLOLEDB.1"

这篇关于VBA - 从Access生成Excel文件（QueryTable）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！