VBA Generieren von Excel-Datei von Access (QueryTable)

Habe ich ein Projekt, dass im Grunde das Ziel ist das generieren von Excel (Berichts -) ab dem Klick auf eine Schaltfläche in Access mit VBA.

Den Inhalt dieser Bericht ist das Ergebnis einer Gespeicherten Prozedur, SQL-Server-Datenbank.

die Zeile des Fehlers:

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

Den ich bekomme, ist:

invalid procedure call or argument (erro '5')

Komplette Code (Bearbeitet mit Remou User Tipps):

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

Das merkwürdige ist, dass der code funktioniert, wenn Sie ausführen, in Excel aber nicht in Access

Okay, so wie du, ich habe jetzt code, der in Excel funktioniert, aber nicht in Access. Es scheint ein problem zu sein das hinzufügen von Abfragen Tabellen. Ich kann nicht sehen, warum noch. Ich Frage mich, ob eine alternative Lösung passen würde? Zum Beispiel würde einfach schreiben der Datensätze auf einem Arbeitsblatt werden geeignet?
FYI: ich habe die automatisierte Excel aus Access-Abfragetabellen für Jahre ohne problem, wenn auch mit Access-Daten als Quelle. Allerdings habe ich nicht versucht, es in Office 2010.
Vielleicht könnten Sie schreiben einige arbeiten Zugangscode?
Ich aktualisiert meine Antwort mit Beispiel-code. Hoffe, es hilft!

InformationsquelleAutor Predoff | 2012-01-03

5

In-Anschluss, müssen Sie das Präfix der Excel-Anwendung Objekte mit der Excel-application-Instanz, zum Beispiel:
```
With MeuExcel.Worksheets(4).QueryTables.Add( _
    connection:=recordset, _
    Destination:=Range("A2"))
End With
```
Darüber hinaus, es sei denn, Sie haben einen Verweis auf die Excel-Bibliothek, man müssen den Wert für die eingebaute Excel-Konstanten.

Es ist eine sehr schlechte Idee, die Namen von Objekten, Variablen. Sagen Sie nicht:
```
Dim recordset As recordset
Set recordset = New recordset
```
Sagen, zum Beispiel:
```
Dim rs As recordset
```
Oder viel besser:
```
Dim rs As New ADODB.Recordset
```
Wenn Sie über eine entsprechende Referenz. Sie können dann überspringen CreateObject.

BEARBEITEN

Muss der Anbieter den Zugriff auf OLE DB-10-Anbieter, wie zum binden von recordsets. Dies funktioniert für mich zu erstellen, eine Tabelle mit Daten über Access mit 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"
```
- Danke für die Tipps, aber habe mein problem nicht lösen
InformationsquelleAutor Fionnuala
3

FWIW, zwei Dinge zeichnen Sie aus:
1. Als @Remou darauf hingewiesen, Excel-Verweise müssen qualifiziert werden. Derzeit Range("A2") unqualifiziert ist. Wenn der code ausgeführt wird, der in Excel, die ActiveSheet angenommen. Aber bei der Ausführung aus einer anderen Anwendung, die Anwendung sucht nach einer Methode oder Eigenschaft in eine eigene Bibliothek namens Range, die Ihnen, dass der Fehler in Microsoft Access.
2. Ist da kein code in der With block, so dass Sie können entfernen Sie die With und End With keywords; wenn Sie dies tun, entfernen Sie auch die äußeren (), etwa so:
wb.Worksheets(4).QueryTables.Add Connection:=rs, Destination:=wb.Worksheets(4).Range("A2")

Alternativ Verschiebung der With block der Worksheet level:
```
With wb.Worksheets(4)
    .QueryTables.Add Connection:=rs, Destination:=.Range("A2")
End With
```
Update—Zugriff auf Excel-Beispiel

Diesem Beispiel-code automatisiert in Excel aus Access, erstellen Sie eine neue Arbeitsmappe, und das hinzufügen einer Abfragetabelle (Querytable) auf das erste Blatt. Die Datenquelle ist eine Access-Tabelle. Diese läuft in Office 2007.
```
Public Sub ExportToExcel()
  Dim appXL As Excel.Application
  Dim wbk As Excel.Workbook
  Dim wst As Excel.Worksheet
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset

  Set appXL = CreateObject("Excel.Application")
  appXL.Visible = True
  Set wbk = appXL.Workbooks.Add
  Set wst = wbk.Worksheets(1)

  Set cn = CurrentProject.AccessConnection
  Set rs = New ADODB.Recordset
  With rs
    Set .ActiveConnection = cn
    .Source = "SELECT * FROM tblTemp"
    .Open
  End With

  With wst
    .QueryTables.Add Connection:=rs, Destination:=.Range("A1")
    .QueryTables(1).Refresh
  End With

End Sub
```
- Ungültiger Prozeduraufruf oder ungültiges argument
- Danke, aber habe mein problem nicht lösen :/
- Ich meine modifizierten Beispiel als Ersatz für die Workbook Objekt für die Excel Application Objekt. Nicht sicher, ob dies Ihr problem beheben, aber es ist mehr richtig.
- Ich habe auch versucht, diese zu arbeiten, und ich bin mir ziemlich sicher, dass es ein problem mit dem laufen in Access. Ein Arbeitsblatt, das die Daten annimmt noch nicht erlauben wird, von Abfragetabellen.Hinzufügen, es schlägt fehl mit Fehler 5, wie bereits von Predoff. Das Beispiel, das ich verwendet, funktionierte perfekt in Excel und wurde aus der MSDN-Website.
- Das funktioniert perfekt mit 2010, also sollte die Lösung für @Predoff. Ich muss prüfen, wo der Unterschied liegt!
- Es scheint in der Verbindungszeichenfolge, aber warum ich noch nicht sehen, da die Verbindung Zeichenfolge, die ich verwendet, erzeugt eine perfekt gültige recordset.
- Ich habe festgestellt, es ist der Anbieter, dass ist das problem. Ich habe eine Anmerkung zu meiner eigenen Antwort.
InformationsquelleAutor Rachel Hettinger
0

Ihnen nicht sagen, was Office-version, aber in Excel 2007/10 eine QueryTable ist eine Eigenschaft eines Listobject so würde der code Aussehen:
```
With MeuExcel.Worksheets.ListObjects.Add(Connection:=rs, Destination:=Range("A2")).QueryTable
```
- Nach re-Lektüre die Frage, merke ich, dass dies nicht das Problem - Sie haben einen Laufzeit-Fehler. Ich denke, @RachelHettinger (wie üblich) hat, trifft den Nagel auf den Kopf.
- Benanntes Argument Nicht Gefunden
InformationsquelleAutor Doug Glancy

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.