collapse

* Benutzer Info

 
 
Willkommen Gast. Bitte einloggen oder registrieren. Haben Sie Ihre Aktivierungs E-Mail übersehen?

* Wer ist Online

  • Punkt Gäste: 47
  • Punkt Versteckte: 0
  • Punkt Mitglieder: 0

Es sind keine Mitglieder online.

* Forenstatistik

  • stats Mitglieder insgesamt: 13913
  • stats Beiträge insgesamt: 65793
  • stats Themen insgesamt: 8879
  • stats Kategorien insgesamt: 5
  • stats Boards insgesamt: 17
  • stats Am meisten online: 415

Autor Thema: Abfrage in vorhandene Excel-Datei anfügen  (Gelesen 2127 mal)

Offline BananaDealer

  • Access-User
  • *
  • Beiträge: 55
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #15 am: Dezember 13, 2017, 13:49:31 »
Habe den Fehler gefunden... eine Laufvariable war als String deklariert ::)

Hier nun der Code mit einigen Kommentaren als Alternativ-Code:

Sub AbfrageAccess()
   
   Dim dbe As Object
   Dim dbfile As String
   Dim sSQL As String
   Dim i As Integer
   
   Dim db As Object
   Dim rs As Object
   Dim qdf As Object

   dbfile = "\Meine DB"
   'Set dbe = CreateObject("DAO.DBEngine.36")   ' Acc2000-2003
   Set dbe = CreateObject("DAO.DBEngine.120")   ' ab Acc2007
   Set db = dbe.OpenDatabase(dbfile)



  Set qdf = db.QueryDefs("AuswertungDLundMessung")
    qdf.Parameters![Welche Messung?] = InputBox("Welche Messung?")
    qdf.Parameters![Welcher DL?] = InputBox("Welcher DL?")




   
    'Set rs = qdf.OpenRecordset(dbOpenDynaset)
   'sSQL = "SELECT FeldX, FeldY FROM TabelleZ WHERE FeldX > 123"
   'Set rs = db.Openrecordset(sSQL)                      ' Verwendung SQL-Anweisung
   Set rs = db.OpenRecordset("AuswertungDLundMessung")   ' Verwendung gespeicherte Abfrage/Tabelle

   ' Feldnamen übernehmen
   For i = 0 To rs.Fields.Count - 1
      ' für ActiveSheet kann man auch etwas Intelligenteres einsetzen
      ActiveSheet.cells(1, i + 1) = rs.Fields(i).name
   Next

   ' Inhalt (gesamtes Recordset) übernehmen
   ActiveSheet.cells(2, 1).CopyFromRecordset rs

   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
   Set dbe = Nothing
   qdf.Close: Set qdf = Nothing
 
 
   
End Sub

Die Parameterabfrage funktioniert auch, nur bekomme ich danach einen Laufzeitfehler 3061 bzw. davor mit dem Code Set rs = qdf.OpenRecordset(dbOpenDynaset) war es der Laufzeitfehler 3001. Hat jemand da noch Ideen?

Danke
 

Offline ebs17

  • Access-Meister
  • ***
  • Beiträge: 881
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #16 am: Dezember 13, 2017, 14:06:09 »
Set rs = db.OpenRecordset("AuswertungDLundMessung") Diesen Teil streichen, der läuft unabhängig vom qdf-Objekt und der Parametereingabe und überschreibt nur das Recordset fehlerbehaftet.

Automatisierung und Inputboxen sind grauenhafter Stil. Parameter würde man Variablen, Zellen, Userformfeldern o.ä. entnehmen. Eine Auswahl und Eingabe sollte vorher erfolgen. Da hat man dann auch Möglichkeiten zur Prüfung, ob man etwas "Gültiges" übergibt.
Mit freundlichem Glück Auf!

Eberhard
 
Folgende Mitglieder bedankten sich: BananaDealer

Offline BananaDealer

  • Access-User
  • *
  • Beiträge: 55
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #17 am: Dezember 13, 2017, 14:16:08 »
Da bekomme ich dann Fehler 91 wegen einer Objektblock oder With-Block Variable.
Ich muss doch irgendwie mein RecordSet rs festlegen. Also Zeile streichen und dann eine andere für die Zuweisung von rs vornehmen?
Set rs = db.OpenRecordset("AuswertungDLundMessung") Diesen Teil streichen, der läuft unabhängig vom qdf-Objekt und der Parametereingabe und überschreibt nur das Recordset fehlerbehaftet.

Automatisierung und Inputboxen sind grauenhafter Stil. Parameter würde man Variablen, Zellen, Userformfeldern o.ä. entnehmen. Eine Auswahl und Eingabe sollte vorher erfolgen. Da hat man dann auch Möglichkeiten zur Prüfung, ob man etwas "Gültiges" übergibt.

Inputbox geht jetzt erst über einen String :)

Set rs = db.OpenRecordset("AuswertungDLundMessung") Den durch irgendwas ersetzen? rs wird ja sonst nicht definiert.

EDIT: Kompilieren klappt auch fehlerfrei. Nur Ausführen gibt den Laufzeitfehler nach der Eingabe der Parameter zurück.
 

Offline Lachtaube

  • Access Guru
  • ****
  • Beiträge: 1170
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #18 am: Dezember 13, 2017, 14:27:48 »
Es muss nach der Parameterzuweisung Set rs = qdf.OpenRecordset() heißen.
Grüße von der (⌒▽⌒)
 
Folgende Mitglieder bedankten sich: BananaDealer

Offline BananaDealer

  • Access-User
  • *
  • Beiträge: 55
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #19 am: Dezember 13, 2017, 14:32:41 »
Es muss nach der Parameterzuweisung Set rs = qdf.OpenRecordset() heißen.

Danke! Endlich funktioniert es!  ;D
Nur eine Kleinigkeit noch: Das Format soll mit eingefügt werden, um als Zahlen in Access hinterlegte Eigenschaften in Textform nach Excel zu bekommen.

Edit: Ich habe eine Parameterübergabe für "Zwischen [Startdatum] und [Enddatum]" aus Access in Excel VBA mittels zwei String Variablen integriert. Nun bekomme ich nach Eingabe des Enddatums den Fehler 3265 zurück.

par1 = InputBox("Welches Stardatum?")
par2 = InputBox("Welches Enddatum?")
dbfile = "\ Meine DB"
   Set dbe = CreateObject("DAO.DBEngine.120")
   Set db = dbe.OpenDatabase(dbfile)



  Set qdf = db.QueryDefs("AuswertungGesamt")
    qdf.Parameters![Startdatum] = par1
    qdf.Parameters![Enddatum] = par2
« Letzte Änderung: Dezember 13, 2017, 15:29:29 von BananaDealer »
 

Offline ebs17

  • Access-Meister
  • ***
  • Beiträge: 881
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #20 am: Dezember 13, 2017, 15:54:42 »
Zitat
Fehler 3265
Als Mensch kann ich mit einer Textmeldung mehr anfangen, z.B. über deren Inhalt nachdenken.
Mit freundlichem Glück Auf!

Eberhard
 

Offline BananaDealer

  • Access-User
  • *
  • Beiträge: 55
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #21 am: Dezember 13, 2017, 16:12:50 »
Zitat
Fehler 3265
Als Mensch kann ich mit einer Textmeldung mehr anfangen, z.B. über deren Inhalt nachdenken.

Hier ein Screenshot im Anhang.
 

Offline ebs17

  • Access-Meister
  • ***
  • Beiträge: 881
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #22 am: Dezember 13, 2017, 17:03:58 »
Zitat
Nun bekomme ich nach Eingabe des Enddatums den Fehler
nach ... heißt mit Inputbox-Eingabe oder 28 Zeilen später?

Programmieren hat etwas mit Präzision zu tun. So sollte man auch formulieren.

Zitat
Edit: Ich habe eine Parameterübergabe für "Zwischen [Startdatum] und [Enddatum]" aus Access in Excel VBA mittels zwei String Variablen integriert.
Den Stolz darauf kann ich nicht teilen. Ein datum würde ich mit einem Datum (Date) bedienen.
Mit freundlichem Glück Auf!

Eberhard
 

Offline BananaDealer

  • Access-User
  • *
  • Beiträge: 55
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #23 am: Dezember 13, 2017, 21:21:09 »
Zitat
Nun bekomme ich nach Eingabe des Enddatums den Fehler
nach ... heißt mit Inputbox-Eingabe oder 28 Zeilen später?

Programmieren hat etwas mit Präzision zu tun. So sollte man auch formulieren.

Zitat
Edit: Ich habe eine Parameterübergabe für "Zwischen [Startdatum] und [Enddatum]" aus Access in Excel VBA mittels zwei String Variablen integriert.
Den Stolz darauf kann ich nicht teilen. Ein datum würde ich mit einem Datum (Date) bedienen.

Ich werde morgen den Code Zeile für Zeile ausführen lassen und die Problemzeile dann nochmal posten. Aber sehr wahrscheinlich ist es die Zuweisung der Variablen and die Übergabe des Parameters nach Access.

Also sinngemäß:
param1 = InputBox....
param1 = InputBox....

Methode.Parameter1inAccess = param1
Methode.Parameter2inAccess = param2

Und die Variablen als String. Problem also bei dieser Zuweisung, da der Code danach identisch mit der ersten Abfrage ist und diese funktioniert.

Und Stolz ist mit nirgends meiner Codierungen verbunden. Eher Unsicherheit, da ich erst seit wenigen Wochen mit Excel VBA und Access arbeite. Das Statement zu den Strings war nur als Info, dass evtl. direkt jemand schreit "falsch". ;) Als Datum bekomme ich den selben Fehler zurück, soweit war ich auch schon. Ich dachte mir, dass der String dann in der Übergabe an Access eher weniger Probleme bereitet, als das Date-Format (dieses kann ja auch als Zahl in Tagen glaube um die 43000 dargestellt werden).
 

Offline Lachtaube

  • Access Guru
  • ****
  • Beiträge: 1170
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #24 am: Dezember 14, 2017, 01:21:48 »
Du solltest sicherstellen, dass die Parameter auch Datumswerte darstellen. Das Ergebnis einer Inputbox ist vom Datentyp String (also Text) und sollte zu einem Datum durch einen Ausdruck wie z. Bsp. CDate(par1) konvertiert werden. Oder Du legst par1 und par2 per Deklaration schon als Date-Variable aus, was eine Datentypkonvertierung impliziert.
Grüße von der (⌒▽⌒)
 

Offline BananaDealer

  • Access-User
  • *
  • Beiträge: 55
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #25 am: Dezember 14, 2017, 09:00:24 »
Du solltest sicherstellen, dass die Parameter auch Datumswerte darstellen. Das Ergebnis einer Inputbox ist vom Datentyp String (also Text) und sollte zu einem Datum durch einen Ausdruck wie z. Bsp. CDate(par1) konvertiert werden. Oder Du legst par1 und par2 per Deklaration schon als Date-Variable aus, was eine Datentypkonvertierung impliziert.

Leider gibt das auch den gleichen Fehler (s.o. Screenshot) zurück.

'_____________Abfrage Stichprobenmenge____________________
 
 parD1 = InputBox("Welches Stardatum?")
 parD2 = InputBox("Welches Enddatum?")
 parD1 = CDate(parD1)
 parD2 = CDate(parD2)
 
 dbfile = "MeineDB"
   Set dbe = CreateObject("DAO.DBEngine.120")
   Set db = dbe.OpenDatabase(dbfile)

  Set qdf = db.QueryDefs("AuswertungGesamt")
    qdf.Parameters![Startdatum] = parD1
    qdf.Parameters![Enddatum] = parD2

    Set rs = qdf.OpenRecordset()
    Worksheets("Auswertung").cells(21, 19).CopyFromRecordset rs

   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
   Set dbe = Nothing
   qdf.Close: Set qdf = Nothing

Eins zu eins besteht dieses Code-Stück auch erneut im Makro für eine andere Abfrage (auch mit Parametern) und diese funktioniert.

Bin etwas ratlos. In Access ist das Kriterium für das Datum so gebaut: Zwischen [Startdatum] und [Enddatum]
Erzeugt das evtl ein Problem?

Ich bräuchte auch noch einen Ratschlag, wie ich das Format aus Access mit importiere per Code.

Vielen vielen Dank Euch bis hier!
 

Offline Lachtaube

  • Access Guru
  • ****
  • Beiträge: 1170
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #26 am: Dezember 14, 2017, 09:39:28 »
Wenn man das Drumherum nicht versteht, ist es immer besser, die komplette Routine zu zeigen.   'bei
   Dim parD1 As String

   'so verfahren
   parD1 = InputBox("Welches Stardatum?")
   '...
   qdf.Parameters![Startdatum] = CDate(parD1)

   '-----------
   'bei
   Dim parD1 As Date

   'so verfahren
   parD1 = InputBox("Welches Stardatum?")
   '...
   qdf.Parameters![Startdatum] = parD1
Grüße von der (⌒▽⌒)
 

Offline BananaDealer

  • Access-User
  • *
  • Beiträge: 55
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #27 am: Dezember 14, 2017, 09:52:48 »
Wenn man das Drumherum nicht versteht, ist es immer besser, die komplette Routine zu zeigen.   'bei
   Dim parD1 As String

   'so verfahren
   parD1 = InputBox("Welches Stardatum?")
   '...
   qdf.Parameters![Startdatum] = CDate(parD1)

   '-----------
   'bei
   Dim parD1 As Date

   'so verfahren
   parD1 = InputBox("Welches Stardatum?")
   '...
   qdf.Parameters![Startdatum] = parD1

Beide Alternativen geben wieder den selben Laufzeitfehler zurück. :-\

Hier nochmal der gesamte Code:
Sub AbfrageAccess()
   
   Dim dbe As Object
   Dim dbfile As String
   Dim i As Integer
   Dim par1 As String
   Dim par2 As String
   Dim parD1 As String
   Dim parD2 As String
   Dim db As Object
   Dim rs As Object
   Dim qdf As Object
   
   
   
   Dim x As Integer
   Dim y As Integer
   Dim z As Integer
 

   par2 = InputBox("Welcher DL?")

   dbfile = "\MeineDB.accdb"
   Set dbe = CreateObject("DAO.DBEngine.120")
   Set db = dbe.OpenDatabase(dbfile)


  Set qdf = db.QueryDefs("AuswertungDLundMessung")
    qdf.Parameters![Welcher DL?] = par2


    Set rs = qdf.OpenRecordset()
    Worksheets("Auswertung").cells(4, 1).CopyFromRecordset rs

   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
   Set dbe = Nothing
   qdf.Close
   Set qdf = Nothing
 
 
 '_____________Abfrage über alle DL____________________
 
 dbfile = "\MeineDB.accdb"
   Set dbe = CreateObject("DAO.DBEngine.120")
   Set db = dbe.OpenDatabase(dbfile)

  Set qdf = db.QueryDefs("AuswertungGesamt")
    qdf.Parameters![Welcher DL?] = "*"

    Set rs = qdf.OpenRecordset()
    Worksheets("Auswertung").cells(4, 11).CopyFromRecordset rs

   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
   Set dbe = Nothing
   qdf.Close: Set qdf = Nothing
 
 '_____________Abfrage Stichprobenmenge____________________
 
 dbfile = "\MeineDB.accdb"
   Set dbe = CreateObject("DAO.DBEngine.120")
   Set db = dbe.OpenDatabase(dbfile)

 parD1 = InputBox("Welches Stardatum?")
 parD2 = InputBox("Welches Enddatum?")

  Set qdf = db.QueryDefs("AuswertungGesamt")
    qdf.Parameters![Startdatum] = CDate(parD1)
    qdf.Parameters![Enddatum] = CDate(parD2)

    Set rs = qdf.OpenRecordset()
    Worksheets("Auswertung").cells(21, 19).CopyFromRecordset rs

   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
   Set dbe = Nothing
   qdf.Close: Set qdf = Nothing


 '________________________Auswertung in Excel____________________________
 
ActiveWorkbook.Worksheets("Auswertung").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Auswertung").Sort.SortFields.Add Key:=Range("B4:B1500" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Auswertung").Sort
        .SetRange Range(cells(4, 1), cells(1500, 5))
        .Header = xlGuess
        .MatchCase = True
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
 ActiveWorkbook.Worksheets("Auswertung").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Auswertung").Sort.SortFields.Add Key:=Range("K4:K1500" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Auswertung").Sort
        .SetRange Range(cells(4, 11), cells(1500, 13))
        .Header = xlGuess
        .MatchCase = True
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
 z = 0
 x = 0
 
 While x < 5
 
 Do
 
 z = z + 1
 
 If Worksheets("Auswertung").cells(z + 3, 3) = "1" Then
 Worksheets("Auswertung").cells(x + 4, 16) = Worksheets("Auswertung").cells(z + 3, 1)
 
 Exit Do
 End If
 Loop
 x = x + 1
 Wend
 
 End Sub
 

Offline ebs17

  • Access-Meister
  • ***
  • Beiträge: 881
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #28 am: Dezember 14, 2017, 11:56:47 »
   Dim par2 As String
...
   par2 = InputBox("Welcher DL?")
...
    qdf.Parameters![Welcher DL?] = par2
Deine Umsetzung ignoriert beide Vorschläge von Lachtaube.

Nebenbei sollte man sich vielleicht noch dafür interessieren, ob das Datumsfeld in der abgefragten Tabelle tatsächlich ein Datum oder vielleicht doch Text ist sowie was denn konkret in die (unsägliche) InputBox geschrieben wird.

Zitat
    qdf.Parameters![Welcher DL?] = "*"
Das Sternchen lässt sich dann trotz aller Konvertierungskünste, die die Parameterabfrage selber vornehmen würde oder die man zusätzlich anwendet, NIE im Zusammenhang mit Date-Werten verwenden.

Statt Panikprobiererei könntest Du mal eine halbe Stunde frische Luft schnappen und dann abgekühlt und aufgefrischt von vorne anfangen.
Mit freundlichem Glück Auf!

Eberhard
 

Offline BananaDealer

  • Access-User
  • *
  • Beiträge: 55
Re: Abfrage in vorhandene Excel-Datei anfügen
« Antwort #29 am: Dezember 14, 2017, 12:40:15 »
Die Ausführung der Parameterübergabe für den Dienstleister klappt wunderbar.


Das Sternchen kommt bei der Datumseingabe nicht zum Einsatz (zu unterscheiden dort auch par1 und parD1) und ist accessbedingt, da dort eine Möglichkeit besteht, in Access Daten gefiltert abzufragen (aber nur für DL; hat mit dem Datum nichts zu tun). Die Eingabe des Sternchens erzeugt die Abfrage aller "DL" und wird in Excel VBA einfach nur weitergegeben, da dort immer nach allen "DL" abgefragt wird. dies funktioniert auch absolut problemfrei.
Das ganze ist aber in Access ohne Probleme (bis jetzt) implementiert.

Bei Einzelschrittausführung wird der Fehler in dieser Zeile erzeugt (und das bei allen Alternativen):
qdf.Parameters![Startdatum] = CDate(parD1)
Ich habe bereits jede vorgeschlagene Lösung ausprobiert und Lachtaubes Rat befolgt, sprich als String mit und ohne Typecast und auch als Variable mit Date-Format. Alle Alternativen erzeugen denselben Fehler (siehe Screenshot).

Ich denke du interpretierst meine Situation etwas zu dramatisch, da ich absolut keine "Panikprobiererei" walten lassen, sondern ganz gelassen verschiedene Möglichkeiten ausprobiere, welche entweder durch andere Codes aus Threads, oder eigenes Nachdenken entstehen.