VBA - 从Access生成Excel文件(QueryTable) [英] VBA - Generate Excel File from Access (QueryTable)
问题描述
此报告的内容是存储过程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屋!