Hallo zusammen,
ich möchte gerne Positionen einer Ausschreibung, die in einer Abfrage in Access vorliegen, in Excel exportieren und dem Bieter mailen, damit der seine Einheitspreise (EP) direkt dort eintragen kann. Diese soll er mir dann wieder zurück mailen.
In der Excel-Arbeitsmappe sind neben der eingefügten Abfrage eine Spalte für die Eingabe des EPs sowie eine Spalte für den Gesamtpreis (GP) mit entsprechender Formel (Menge x EP) vorhanden.
Momentan habe ich die Daten der Abfrage in Excel ab einer bestimmten Zelle importiert. Soweit so gut.
Jedoch bietet mir Excel über EXTERNE DATEN IMPORTIEREN nur Tabellen und bestimmte Abfragen an. Bei den Abfragen dürfen keine Bezüge zu anderen Formularen vorhanden sein. Das ist bei mir jedoch der Fall. Gebe ich in der Abfrage die entsprechenden IDs als Kriterium per Hand ein, funktioniert es.
Gibt es eine Möglichkeit, per VBA die Werte selbst als Parameter an die Abfrage zu übergeben?
Ich möchte nur ungern die Sache über eine Tabellenerstellungsabfrage realisieren, um die Daten in Excel immer aktuell zu halten.
Der 2. Schritt wird sein, dass die Spalte der EPs der ausgefüllten Exceltabelle wieder in Access eingelesen werden muss. Aber Step by Step.
Danke vorab.
Gruß
Max
Hallo,
Gibt es eine Möglichkeit, per VBA die Werte selbst als Parameter an die Abfrage zu übergeben?
Ja sicher, das wäre auch eher der Normalfall (bzw. die zu bevorzugende Methode) neben der Verwendung der Parameters-Sektion gegenüber den Verweisen auf Formular-Steuerelemente.
Zitat.
.
Dim strSQL as String, strCond as String
strCond = Me!ID
strSQL = "Select irgendwas from tblIrgendwo where ID = " & strCond
Currentdb.Querydefs("ExportAbfrage").SQL = strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "ExportAbfrage", "c:\temp\Export.xlsx", True, "SheetName"
'oder evtl.
DoCmd.OutputTo acOutputQuery, "ExportAbfrage", acFormatXLSX, "c:\temp\Export.xlsx"
.
.
Das Einfügen ab bestimmter Zelle wird vermutlich nur mit Excel-Automation gelingen.
Hallo Franz,
beim Exportieren aus Access mit TransferSpreadsheet sind wohl nur 1:1 "Kopien" der Abfrage möglich. D.h. in vorhandene u. bereits ergänzte (EP/GP-Spalten) Exceldateien kann nicht exportiert werden.
Deshalb habe ich mich von diesem Ansatz verabschiedet und mir folgendes Konzept überlegt:
Ich habe eine Exceldatei mit bereits importierter Abfrage, wie weiter oben beschrieben.
Um diese Daten dort zu aktualisieren, muss folgendes über VBA geschehen:
- In Access BieterID u. LvID an Abfrage übergeben
(Da weiß ich halt nicht, wie weit ich deinen Code benutzten kann. In der Selectanweisung würde bei mir dann die Exportabfrage genannt, oder?)
- Exceldatei öffnen
- importierten Inhalt aktualisieren
(wird leider nicht automatisch gemacht, obwohl in den Einstellungen eingeschaltet)
- Blattschutz einschalten
- Unter einem bestimmten Dateinamen speichern
- Datei schließen
Wäre top, wenn das funktioniert!
Gruß
Max
Hallo,
Zitat- In Access BieterID u. LvID an Abfrage übergeben
dann mach es doch so, wie von mir beschrieben...
Wenn Du noch den SQL-String der vorhandenen Abfrage postest und die im benutzten Formular verwendeten Steuerelementnamen für die Werte von BieterID u. LvID, dann kann ich konkret die Einstellung der Abfrage zeigen.
Hallo,
die Steuerelemente im Formular heißen ebenfalls wie die Feldnamen Bieter-ID und LV-ID.
(Sorry wegen der Feldnamen mit Bindestrich. Ich weiß inzwischen, dass das nicht gut ist, aber eine konsequente Änderung der alten DB wäre sehr viel Arbeit!)
Dim strSQL As String, strCond As String
strCond = Me![Bieter-ID]
strSQL = "Select [Bieter-ID] from qry_LvPosExport where [Bieter-ID] = " & strCond
CurrentDb.QueryDefs("qry_LvPosExport").SQL = strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_LvPosExport", "D:\Export.xls", True, "SheetName"
'DoCmd.OutputTo acOutputQuery, "qry_LvPosExport", acFormatXLS, "D:\Export2.xls"
Gruß
Max
Hallo,
poste bitte den SQL-String der Abfrage qry_LvPosExport .........
Ich hoffe, ich hab Dich richtig verstanden.
Hier der SQL-Code der Abfrage qry_LvPosExport:
SELECT IIf([GWZähler]=1,[Titel_Sort] & "." & [Pos_Sort],[Gewerk_Sort] & "." & [Titel_Sort] & "." & [Pos_Sort]) AS PosNr, tbl_Gewerk_Katalog.GwBez, tbl_Titel.Titel_Bez, tbl_Pos.Menge, tbl_Pos.Einheit, tbl_Pos.Pos_Kurztxt, IIf([Nur_EP]=-1,"Nur EP",Null) AS Eventual, DCount("Gewerk_Sort","tbl_Gewerke","[LV-ID]=" & [tbl_Pos.LV-ID]) AS GWZähler, LV.[BV-NR], LV.[LV-lfdNr], LV.[LV-Bez], qry_Bieter.Name, qry_Bieter.Ort, tbl_Pos.[LV-ID], qry_Bieter.[Bieter-ID], tbl_Pos.[Pos-ID]
FROM (((tbl_Pos LEFT JOIN LV ON tbl_Pos.[LV-ID] = LV.[LV-ID]) LEFT JOIN tbl_Titel ON tbl_Pos.Titel_ID = tbl_Titel.Titel_ID) LEFT JOIN (tbl_Gewerke LEFT JOIN tbl_Gewerk_Katalog ON tbl_Gewerke.Gewerk_KatID = tbl_Gewerk_Katalog.GwID) ON tbl_Pos.Gewerk_ID = tbl_Gewerke.Gewerk_ID) LEFT JOIN qry_Bieter ON LV.[LV-ID] = qry_Bieter.[LV-ID]
WHERE (((tbl_Pos.[LV-ID])=51) AND ((qry_Bieter.[Bieter-ID])=220));
Gruß
Max
Hallo,
ja, das hast Du richtig verstanden. Dies sind doch die Daten, die exportiert werden sollen?
Luftcode:
.
.
Dim strSQL As String
strSQL = "SELECT IIf([GWZähler]=1,[Titel_Sort] & '.' & [Pos_Sort],[Gewerk_Sort] & '.' & [Titel_Sort] & '.' & [Pos_Sort]) AS PosNr, " & _
" tbl_Gewerk_Katalog.GwBez, tbl_Titel.Titel_Bez, tbl_Pos.Menge, tbl_Pos.Einheit, tbl_Pos.Pos_Kurztxt, IIf([Nur_EP]=-1,'Nur EP',Null) AS Eventual, " & _
" DCount(""Gewerk_Sort"",""tbl_Gewerke"",""[LV-ID]="" & [tbl_Pos.LV-ID]) AS GWZähler, LV.[BV-NR], LV.[LV-lfdNr], " & _
" LV.[LV-Bez], qry_Bieter.Name, qry_Bieter.Ort, tbl_Pos.[LV-ID], qry_Bieter.[Bieter-ID], tbl_Pos.[Pos-ID] " & _
" FROM (((tbl_Pos LEFT JOIN LV ON tbl_Pos.[LV-ID] = LV.[LV-ID]) LEFT JOIN tbl_Titel ON tbl_Pos.Titel_ID = tbl_Titel.Titel_ID) LEFT " & _
" JOIN (tbl_Gewerke LEFT JOIN tbl_Gewerk_Katalog ON tbl_Gewerke.Gewerk_KatID = tbl_Gewerk_Katalog.GwID) ON " & _
" tbl_Pos.Gewerk_ID = tbl_Gewerke.Gewerk_ID) LEFT JOIN qry_Bieter ON LV.[LV-ID] = qry_Bieter.[LV-ID]"
CurrentDb.QueryDefs("qry_LvPosExport").SQL = strSQL & " Where [LV-ID]= " & Me![LV-ID] & " And [Bieter-ID] = " & Me![Bieter-ID]
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_LvPosExport", "D:\Export.xls", True, "SheetName"
.
.
.
Hallo,
mit dem Befehl "TransferSpreadsheet acExport" kann ich nicht in vorh. Exceldateien exportieren. Da das jedoch Vorraussetzung ist, möchte ich es andersherum versuchen und eine bestehende Exceldatei mit verknüpfter Access-Tabelle öffnen, die Verknüpfung aktualisieren, die Datei speichern u. schliessen.
Sitze schon den ganzen Tag dran und krieg es nicht hin.
Da beim Öffnen der Datei diese nicht aktualisiert wird (updateLinks:=3 wird ignoriert), starte ich stattdessen ein Excel-Makro, in dem dieses realisiert wird.
Hier der Code in Access:
Private Sub Befehl54_Click()
Dim xlApp As Object, xlWB As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("Pfad\LV.xls")
'auch mit dem Update-Befehl wird die Verknüfung nicht aktualisiert
xlApp.Run "Makro2"
ActiveWorkbook.Close 'hier wird jedes zweite Mal gemeckert.
End Sub
Hier der Code in Excel:
Sub Makro2()
Range("C24").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.SaveAs Range("I1").Value & Range("K1").Value & ".xls"
'Der neue Dateiname wird aus den beiden Werten gebildet.
End Sub
In Excel erscheint diese Fehlermeldung (image143). Bricht man diese ab, läuft das Makro zwar weiter, die Datei wird erzeugt, aber die Verknüpfung nicht aktualisiert. Teste ich das Makro nur in Excel, funktioniert es.
Kann es sein, dass das Problem dadurch entsteht, dass sowohl die DB als auch die LV.xls auf einem Nas liegt?
In Access erscheint zum Schluss diese Fehlermeldung (image144). Jedoch immer nur bei jedem zweiten Durchlauf!?
Gruß
Max