9
verschiedene Access Tabellen/ Abfragen in eine bestehende Arbeitsmappe auf einem Sheet an mehren stellen exportieren
Hallo ich bin Anfänger, vorab gesagt. Mein "Progrämmchen läuft auch ohne Fehlermeldung durch aber ...
folgendes Problem, ich möchte aus ca.100 Abfragen bestimmte Werte in ein ExcelBlatt an verschiedenen Stellen exportieren, nicht meine Idee, mein Chef hat eine Excelvorlage gebastelt, die ich mit Daten befüllen muss, da ich das nicht jeden Tag manuell machen möchte, habe ich mich an VBA gewagt. Access 02 Windows Xp/ alternativ Access 03 Windows NT.
Hier meine ersten Strickversuche:
Option Compare Database
Option Explicit
Sub Makro3()
Dim objExcel As New Excel.Application
Dim objExcelbook As Object
Set objExcelbook = GetObject("C:\Mappe1.xls")
Dim objExcelSheet As Object
Set objExcelSheet = objExcelbook.Worksheets(1)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT [Daten gedreht].ProdWk, [Daten gedreht].ID FROM [Daten gedreht]")
objExcelSheet.Range("A4").CopyFromRecordset rst
rst.Close
objExcelbook.SaveAs "C:Mappe1.xls"
Set objExcelSheet = Nothing
Set objExcel = Nothing
End Sub
läuft ohne Fehlermeldung durch, aber in der Mappe dann nichts mehr, kein Tabellenblatt garnichts.
Kann mir jemand weiterhelfen?
Danke im Vorraus
LG
Elke
Hier meine ersten Strickversuche:
Option Compare Database
Option Explicit
Sub Makro3()
Dim objExcel As New Excel.Application
Dim objExcelbook As Object
Set objExcelbook = GetObject("C:\Mappe1.xls")
Dim objExcelSheet As Object
Set objExcelSheet = objExcelbook.Worksheets(1)
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT [Daten gedreht].ProdWk, [Daten gedreht].ID FROM [Daten gedreht]")
objExcelSheet.Range("A4").CopyFromRecordset rst
rst.Close
objExcelbook.SaveAs "C:Mappe1.xls"
Set objExcelSheet = Nothing
Set objExcel = Nothing
End Sub
läuft ohne Fehlermeldung durch, aber in der Mappe dann nichts mehr, kein Tabellenblatt garnichts.
Kann mir jemand weiterhelfen?
Danke im Vorraus
LG
Elke
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 31887
Url: https://administrator.de/contentid/31887
Printed on: April 23, 2024 at 23:04 o'clock
9 Comments
Latest comment
Moin Elke,
ich weiß gar nicht, was Du an dem Ansinnen Deines Chefs so verwunderlich findest...
... ich könnte Dir da Anekdoten von meinem Chef erzählen... aber erstmal der Lösungsvorschlag.
Grundsätzlich müsste es mit Deinem Code genau so funktionieren laut M$-Dokumentation.
Etwas stabiler würde es so werden:
Hope That Helps
Biber
ich weiß gar nicht, was Du an dem Ansinnen Deines Chefs so verwunderlich findest...
... ich könnte Dir da Anekdoten von meinem Chef erzählen... aber erstmal der Lösungsvorschlag.
Grundsätzlich müsste es mit Deinem Code genau so funktionieren laut M$-Dokumentation.
Etwas stabiler würde es so werden:
Sub Makro3()
Dim objExcel As New Excel.Application
Dim objExcelbook As Excel.Workbook ' statt Object
Dim objExcelSheet As Excel.Worksheet ' statt Object
Dim rs As DAO.Recordset 'wer kommt denn auf die Idee, einen RecordSet mit "rst" abzukürzen??? *gg
Set objExcelbook = GetObject("C:\Mappe1.xls")
Set objExcelSheet = objExcelbook.Worksheets(1)
Set rs = CurrentDb.OpenRecordset("SELECT [Daten gedreht].ProdWk, [Daten gedreht].ID FROM [Daten gedreht]")
objExcelSheet.Range("A4").CopyFromRecordset rs, 5, 1000
' Ja, ich weiß, die Parameter 5 für MaxCols und 1000 für MaxRows sind laut M$-Doku optional und nicht erforderlich
' Andererseits... wie sollte M$ denn ahnen, dass jemand die Doku liest...
rs.Close
' objExcelbook.SaveAs "C:Mappe1.xls" 'auf gar keinen Fall!!
' hier poppt eine Meldung hoch, ob Du denn wirklich die bereits existierende Mappe1.xls überschreiben willst
objExcelbook.Save
' unter dem bisherigen Namen speichern
set rs = nothing
Set objExcelSheet = Nothing
Set objExcel = Nothing
End SubHope That Helps
Biber
Hallo Biber,
erst mal danke für deine Antwort, leider geht das dumme Ding immer noch nicht, Fehlermeldung bei Range von CopyFromRecordset .... snief, die Zahlen wieder manuell eintragen. Rst ist doch ne' schöne Abkürzung, gibt es eigentlich irgendwo Vorgaben, wie man die Sachen abkürzt? Ich doch Neuling
Gruß und danke nochmal
LG Elke
erst mal danke für deine Antwort, leider geht das dumme Ding immer noch nicht, Fehlermeldung bei Range von CopyFromRecordset .... snief, die Zahlen wieder manuell eintragen. Rst ist doch ne' schöne Abkürzung, gibt es eigentlich irgendwo Vorgaben, wie man die Sachen abkürzt? Ich doch Neuling
Gruß und danke nochmalLG Elke
lallalallala....falls es jemanden interessiert, es funktioniert mit workbook.open anstelle des getobject....nie wieder manuell dusselige Zahlen eintragen
Gruß Elke
Gruß Elke
...lalala... natürlich interessiert das jemanden...
.. und inzwischen könnte ich den Fehler von gestern auch erklären (wenn es jemand interessiert *gg)
Wenn Du also tatsächlich ein bestehendes WorkSheet mit Deinen dusseligen Zahlen zukleisterst, dann lass die obige Zeile weg:
lass etwas tiefer das schon richtige
So herum ist es eigentlich richtig.
Bei mir lief es gestern im zweiten Versuch nicht etwa wegen der Parameter-Änderungen im CopyFromRecordSet-Aufruf, sondern weil Excel schlicht und einfach beim zweiten Test bereits offen war.
CopyFromRecordSet war eine vollkommen falsche Fährte *schäm*...
Ich habe es gestern auch überlesen... das "doppelte" Öffnen von Excel als New Application und über Bande mit GetObject() war der eigentliche Klemmer.
Grüße
Biber
.. und inzwischen könnte ich den Fehler von gestern auch erklären (wenn es jemand interessiert *gg)
Wenn Du also tatsächlich ein bestehendes WorkSheet mit Deinen dusseligen Zahlen zukleisterst, dann lass die obige Zeile weg:
Dim objExcel As New Excel.Application
-und-lass etwas tiefer das schon richtige
Set objExcelbook = GetObject("C:\Mappe1.xls") 'oder wie die nun wirklich heißen mag...
...stehen.So herum ist es eigentlich richtig.
Bei mir lief es gestern im zweiten Versuch nicht etwa wegen der Parameter-Änderungen im CopyFromRecordSet-Aufruf, sondern weil Excel schlicht und einfach beim zweiten Test bereits offen war.
CopyFromRecordSet war eine vollkommen falsche Fährte *schäm*...
Ich habe es gestern auch überlesen... das "doppelte" Öffnen von Excel als New Application und über Bande mit GetObject() war der eigentliche Klemmer.
Grüße
Biber
bei mir funktioniert das nicht, so ganz habe ich dich auch nicht verstanden was du mit doppeltem Öffnen meinst. Ich als Laie habe VBA so verstanden:
dass ich mit dim as Excel.Application nur die reine Anwendung anspreche, also eine Verbindung zu Excel erstelle,
mit getobject die Mappe quasi öffne, aber nicht Excel zum Zweiten mal starte
????
LG Elke
dass ich mit dim as Excel.Application nur die reine Anwendung anspreche, also eine Verbindung zu Excel erstelle,
mit getobject die Mappe quasi öffne, aber nicht Excel zum Zweiten mal starte
????
LG Elke
Jein, Elke,
Eskommt auf den Kontext an bzw. auf das, was Du voraussetzt zur Laufzeit Deines Makros.
Es gibt ja selbst unter optimistischen Annahmen mehrere Szenarios:
Wenn Dein (Access-VBA-) Makro gestartet wird, kann
a) Excel bereits gestartet sein
a1) User hat Excel über Start-Programme gestartet, "neue" Mappe mit Defaultname Mappe1.xls ist angelegt.
a2) User hat andere Mappe(n) in Arbeit
a3) User ist gerade dabei, eine neue Datei Mappe1.xls anzulegen, allerdings mit ganz anderem Inhalt. Hat drei Stunden die letzten Inventurdaten eingetippt, aber noch nicht gespeichert.
b) Excel ist noch nicht gestartet
..und zu a) und b) gibt es jeweils noch die Varianten, dass die Excel-Datei "c:\Mappe.xls", in die Du schreiben willst, exuistiert oder nicht existiert.
Für die a)-Fälle wäre es unter Umständen sowohl ärgerlich für den Anwender, wenn Du dieselbe-Excel-Instanz "mitbenutzt" als auch blutdruckerhöhend, wenn Du das Excel am Ende beendest (ohne vorher zu prüfen, ob es aktiv war oder nicht).
Um diese Fälle handeln zu können, bietet M$ auch verschiedenen Möglichkeiten an, diese COM-Objekte anzusprechen.
Und die Möglichkeiten, die IMHO für eine "robuste" Anwendung sinnvoll wären, sind sicherlich:
- Wenn Excel noch nicht läuft: Excel-Instanz öffnen, xls-Datei vollschreiben, Excel schließen
- wenn Excel schon läuft; Deine xls-Daten schreiben, Excel danach nicht schließen, alten Zustand herstellen
(Ich könnte das bestimmt besser erklären, wenn hier im Forum ein Flipchart-Ständer stehen würde..
Da ließe sich schneller eine Matrix zur Verdeutlichung skizzieren...)
Für welche Variante, welche Strategie auch immer Du Dich entscheidest, rechne immer mit M$-Bugs..
Einige GetObject(,xxx.Application) gehen in die Grütze, OBWOHL die Application schon läuft, andere CreateObject()-Aufrufe hauen ins Mett, WENN die Applikation schon läuft... nicht immer ganz konform zur M$-Doku.
Was ich allerdings (obwohl ich bekennender MS-Fan bin, da kannst Du jeden hier im Forum fragen *gg) als selbst für Redmonder Verhältnisse auboptimal programmiert ist das
Das Schlüsselwort New in Kombination mit Applikation.
Wenn Du New bei der Deklaration des Objekts angibst, wird eine neue Instanz des Objekts erstellt.
Eine Set-Anweisung für die Zuweisung des Objekts brauchst Du nicht.
Buggy: Die so erstellte (Excel-)Instanz kann nicht beendet werden und der Objektverweis lässt sich auch nicht aus dem Speicher entfernen.
Deshalb lieber:
Ein CreateObject("DeineMappe.xls") versuchen, wenn das Fehler wirft (weil Excel schon läuft), dann eben das Object mit GetObject() erzeugen.
Die Kombination "Dim objXXX" und " as New Excel.Application" würde ich meiden.
Liebe Grüße zurück
Biber
dass ich mit dim as Excel.Application nur die reine Anwendung anspreche,
also eine Verbindung zu Excel erstelle...
also eine Verbindung zu Excel erstelle...
Eskommt auf den Kontext an bzw. auf das, was Du voraussetzt zur Laufzeit Deines Makros.
Es gibt ja selbst unter optimistischen Annahmen mehrere Szenarios:
Wenn Dein (Access-VBA-) Makro gestartet wird, kann
a) Excel bereits gestartet sein
a1) User hat Excel über Start-Programme gestartet, "neue" Mappe mit Defaultname Mappe1.xls ist angelegt.
a2) User hat andere Mappe(n) in Arbeit
a3) User ist gerade dabei, eine neue Datei Mappe1.xls anzulegen, allerdings mit ganz anderem Inhalt. Hat drei Stunden die letzten Inventurdaten eingetippt, aber noch nicht gespeichert.
b) Excel ist noch nicht gestartet
..und zu a) und b) gibt es jeweils noch die Varianten, dass die Excel-Datei "c:\Mappe.xls", in die Du schreiben willst, exuistiert oder nicht existiert.
Für die a)-Fälle wäre es unter Umständen sowohl ärgerlich für den Anwender, wenn Du dieselbe-Excel-Instanz "mitbenutzt" als auch blutdruckerhöhend, wenn Du das Excel am Ende beendest (ohne vorher zu prüfen, ob es aktiv war oder nicht).
Um diese Fälle handeln zu können, bietet M$ auch verschiedenen Möglichkeiten an, diese COM-Objekte anzusprechen.
Und die Möglichkeiten, die IMHO für eine "robuste" Anwendung sinnvoll wären, sind sicherlich:
- Wenn Excel noch nicht läuft: Excel-Instanz öffnen, xls-Datei vollschreiben, Excel schließen
- wenn Excel schon läuft; Deine xls-Daten schreiben, Excel danach nicht schließen, alten Zustand herstellen
(Ich könnte das bestimmt besser erklären, wenn hier im Forum ein Flipchart-Ständer stehen würde..
Da ließe sich schneller eine Matrix zur Verdeutlichung skizzieren...)
Für welche Variante, welche Strategie auch immer Du Dich entscheidest, rechne immer mit M$-Bugs..
Einige GetObject(,xxx.Application) gehen in die Grütze, OBWOHL die Application schon läuft, andere CreateObject()-Aufrufe hauen ins Mett, WENN die Applikation schon läuft... nicht immer ganz konform zur M$-Doku.
Was ich allerdings (obwohl ich bekennender MS-Fan bin, da kannst Du jeden hier im Forum fragen *gg) als selbst für Redmonder Verhältnisse auboptimal programmiert ist das
Das Schlüsselwort New in Kombination mit Applikation.
Wenn Du New bei der Deklaration des Objekts angibst, wird eine neue Instanz des Objekts erstellt.
Eine Set-Anweisung für die Zuweisung des Objekts brauchst Du nicht.
Buggy: Die so erstellte (Excel-)Instanz kann nicht beendet werden und der Objektverweis lässt sich auch nicht aus dem Speicher entfernen.
Deshalb lieber:
Ein CreateObject("DeineMappe.xls") versuchen, wenn das Fehler wirft (weil Excel schon läuft), dann eben das Object mit GetObject() erzeugen.
Die Kombination "Dim objXXX" und " as New Excel.Application" würde ich meiden.
Liebe Grüße zurück
Biber
Hallo Biber,
uuuiiiiii, habe mir deine Antwort jetzt viermal durchgelesen .
Nein, ich bin nicht blond aber Anfängerin
Wofür steht IMHO, M$ ,Redmonder,auboptimal ?Letzteres Schröder? =suboptimal
Ich habe mich ja für workbook.open entschieden, wie oben erwähnt, damit funktioniert alles.
Egal ob User Excel auf hat oder zu, ob eine andere Mappe bearbeitet wird, mein Mappe heißt ja nur im Testlauf Mappe1, damit hat sich das Problem, wenn user stundenlange Arbeit noch nicht abgespeichert hat auch erledigt.
Ist das denn so verkehrt was ich geschrieben habe?
Grüße und einen schönen Sonntag
Elke
'
'Description: Exportiert Recordsets nach Excel
' Funktioniert mit DAO /ADO nicht getestet
'References: Microsoft Excel 10.0 Object Library
'
Public Sub ExportRStoExc()
Dim objExcel As Excel.Application ' Excel.Application
Dim objExcelbook As Excel.Workbook ' Excel.Workbook
Dim objExcelSheet As Excel.Worksheet ' Excel.Worksheet
Dim intExcelCalcMode As Integer 'Excel Berechnungsmodus
Dim rst As DAO.Recordset 'Access Tabelle oder Abfrage
On Error GoTo ExportRStoExc_Error
'Excel-Objekt öffnen
Set objExcel = CreateObject("Excel.Application")
'falls gewünscht sichtbar
'objExcel.Visible = True
'gewünschtes Workbook öffnen, Referenz setzen
Set objExcelbook = Workbooks.Open("C:\Mappe1.xls")
'Tastatureingabe blockieren
objExcel.Interactive = False
'Den alten Berechnungsmodus speichern
'und temporaer auf manuell setzen
intExcelCalcMode = objExcel.Calculation
objExcel.Calculation = xlCalculationManual
'die Anzeigeaktualisierung unterdrücken
objExcel.ScreenUpdating = False
'Wert aus 1.Abfrage in Zelle A4 kopieren, Recordset einlesen
Set objExcelSheet = objExcelbook.Worksheets(1)
Set rst = CurrentDb.OpenRecordset("Abfrage1")
objExcelSheet.Range("A4").CopyFromRecordset rst
rst.Close
'Wert aus 2.Abfrage in Zelle C4 kopieren
Set objExcelSheet = objExcelbook.Worksheets(2)
Set rst = CurrentDb.OpenRecordset("Abfrage2")
objExcelSheet.Range("C4").CopyFromRecordset rst
rst.Close
'die Anzeigeaktualisierung einschalten
objExcel.ScreenUpdating = True
'Den alten Berechnungsmodus reaktivieren
objExcel.Calculation = intExcelCalcMode
'Tastatur- und Mauseingaben wieder zulassen
objExcel.Interactive = True
objExcelbook.Save
objExcelbook.Close
'Referenzen zerstören
Set rst = Nothing
Set objExcelSheet = Nothing
Set objExcelbook = Nothing
Set objExcel = Nothing
ExportRStoExc_Error:
End Sub
uuuiiiiii, habe mir deine Antwort jetzt viermal durchgelesen .
Nein, ich bin nicht blond aber Anfängerin
Wofür steht IMHO, M$ ,Redmonder,auboptimal ?Letzteres Schröder? =suboptimal
Ich habe mich ja für workbook.open entschieden, wie oben erwähnt, damit funktioniert alles.
Egal ob User Excel auf hat oder zu, ob eine andere Mappe bearbeitet wird, mein Mappe heißt ja nur im Testlauf Mappe1, damit hat sich das Problem, wenn user stundenlange Arbeit noch nicht abgespeichert hat auch erledigt.
Ist das denn so verkehrt was ich geschrieben habe?
Grüße und einen schönen Sonntag
Elke
'
'Description: Exportiert Recordsets nach Excel
' Funktioniert mit DAO /ADO nicht getestet
'References: Microsoft Excel 10.0 Object Library
'
Public Sub ExportRStoExc()
Dim objExcel As Excel.Application ' Excel.Application
Dim objExcelbook As Excel.Workbook ' Excel.Workbook
Dim objExcelSheet As Excel.Worksheet ' Excel.Worksheet
Dim intExcelCalcMode As Integer 'Excel Berechnungsmodus
Dim rst As DAO.Recordset 'Access Tabelle oder Abfrage
On Error GoTo ExportRStoExc_Error
'Excel-Objekt öffnen
Set objExcel = CreateObject("Excel.Application")
'falls gewünscht sichtbar
'objExcel.Visible = True
'gewünschtes Workbook öffnen, Referenz setzen
Set objExcelbook = Workbooks.Open("C:\Mappe1.xls")
'Tastatureingabe blockieren
objExcel.Interactive = False
'Den alten Berechnungsmodus speichern
'und temporaer auf manuell setzen
intExcelCalcMode = objExcel.Calculation
objExcel.Calculation = xlCalculationManual
'die Anzeigeaktualisierung unterdrücken
objExcel.ScreenUpdating = False
'Wert aus 1.Abfrage in Zelle A4 kopieren, Recordset einlesen
Set objExcelSheet = objExcelbook.Worksheets(1)
Set rst = CurrentDb.OpenRecordset("Abfrage1")
objExcelSheet.Range("A4").CopyFromRecordset rst
rst.Close
'Wert aus 2.Abfrage in Zelle C4 kopieren
Set objExcelSheet = objExcelbook.Worksheets(2)
Set rst = CurrentDb.OpenRecordset("Abfrage2")
objExcelSheet.Range("C4").CopyFromRecordset rst
rst.Close
'die Anzeigeaktualisierung einschalten
objExcel.ScreenUpdating = True
'Den alten Berechnungsmodus reaktivieren
objExcel.Calculation = intExcelCalcMode
'Tastatur- und Mauseingaben wieder zulassen
objExcel.Interactive = True
objExcelbook.Save
objExcelbook.Close
'Referenzen zerstören
Set rst = Nothing
Set objExcelSheet = Nothing
Set objExcelbook = Nothing
Set objExcel = Nothing
ExportRStoExc_Error:
End Sub
*grins*
Moin Elke,
IMHO - in my humble opinion, meiner unmaßgeblichen Meinung nach
M$ = Micro$oft = Microsoft, Redmonder Konzern mit genialem Marketing und zusammengeklauter Software
Redmonder=auch Microsoft. So wie man/frau "die Rüsselsheimer" statt Opel sagt oder "die Wolfsburger" statt VW
auboptimal= Tippfehler. Sollte suboptimal heißen. Synonym für grottenschlecht.
Und wenn Dein Makro läuft, dann kann er ja nicht grundsätzlich daneben sein.
Und es gibt dann keinen Grund, ihn grundsätzlich anders zu gestalten.
Never change a running system.
Ich bin ja auch keine kompetente Instanz für ein "Richtig" oder "Falsch" bei VBA-Makros oder gar Programmierung im Allgemeinen.
Ich kann ja bestenfalls ein paar Tipps weitergeben, wenn ich irgendwo einen Codeschnipsel mal zum Fliegen gebracht habe oder wenn ich an einer bestimmten Stelle mal in ein Minenfeld gelaufen bin.
Also lass Deinen Makro ruhig so, wie er ist, oder vielmehr, suche und finde weiterhin Deinen eigenen Stil beim Coden.
Ist sinnvoller, als sich irgendwo aus dem Netz fremde Schnipsel per Copy und Paste einzubauen und nicht zu verstehen.
Schönen Sonntag
Biber
Moin Elke,
Wofür steht IMHO, M$ ,Redmonder,auboptimal ?Letzteres Schröder? =suboptimal
IMHO - in my humble opinion, meiner unmaßgeblichen Meinung nach
M$ = Micro$oft = Microsoft, Redmonder Konzern mit genialem Marketing und zusammengeklauter Software
Redmonder=auch Microsoft. So wie man/frau "die Rüsselsheimer" statt Opel sagt oder "die Wolfsburger" statt VW
auboptimal= Tippfehler. Sollte suboptimal heißen. Synonym für grottenschlecht.
Ist das denn so verkehrt was ich geschrieben habe?
Nein... es führen ja immer viele Wege gleichermaßen zum Ziel.Und wenn Dein Makro läuft, dann kann er ja nicht grundsätzlich daneben sein.
Und es gibt dann keinen Grund, ihn grundsätzlich anders zu gestalten.
Never change a running system.
Ich bin ja auch keine kompetente Instanz für ein "Richtig" oder "Falsch" bei VBA-Makros oder gar Programmierung im Allgemeinen.
Ich kann ja bestenfalls ein paar Tipps weitergeben, wenn ich irgendwo einen Codeschnipsel mal zum Fliegen gebracht habe oder wenn ich an einer bestimmten Stelle mal in ein Minenfeld gelaufen bin.
Also lass Deinen Makro ruhig so, wie er ist, oder vielmehr, suche und finde weiterhin Deinen eigenen Stil beim Coden.
Ist sinnvoller, als sich irgendwo aus dem Netz fremde Schnipsel per Copy und Paste einzubauen und nicht zu verstehen.
Schönen Sonntag
Biber
Hallo Biber,
...IMO trotzdem Danke für deine Unterstützung. Bis zum nächsten Problemchen...
LG
Elke
...IMO trotzdem Danke für deine Unterstützung
. Bis zum nächsten Problemchen...LG
Elke
