Ich habe eine Abfrage, womit ich Daten nach Excel übertrage. Nun wäre es praktisch, wenn in der Excel-Tabelle neben dem Geburtstag auch das aktuelle Alter stünde.
Dazu gebe ich diese Formel in eine Spalte ein:
Alter: =WENN(ISTLEER(INDIREKT(ADRESSE(ZEILE();SPALTE()+1)));"*?";WENN(ISTFEHLER(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"));"J?";(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"))))
In Excel funktioniert die Formel
Wenn ich die Formel so in die Abfrage setzte, wie oben geht es nicht, weil das Abfrageformular den Wert prüft ud als fahs deklariert.
Setzte ich es Ausführungszeichnung (Alter: "= wenn....") geht es auch nicht
Bei Hochkomma (Alter: '= wenn....') geht es halb gut. In Excel erscheint der Wert korrekt rechnet aber nicht. Wenn ich ich in Excel das Zell-Format wechsele ändert sich nichts. Jedoch, wenn ich in der nach Excel exportierten Zelle nur das Gleichheitszeichen austausche (lösche und neu einsetze) rechnet Excel richtig.
Mit dem Wert soll später in Excel gerechnet werden
1) Was kann ich tun?
2) Die Formel wird noch erweitert und dann sollen über wenn dann unterschiedlich benutzerbedingt formatiert sein. Ich brauch dann "Einheiten" an der Zahl, z.B. 22g oder 22a, die weiterhin rechen sollen.
Hallo,
vergiss Excel , wenn Du Access benutzt.
Schreib in einer Abfrage-Spalte (im Abfrageentwurf) die Formel hinein, die Du hier
http://www.donkarl.com/?FAQ2.7
findest.
Das ist eine prinzipielle Sache. Einige Anwender sollen mit Excel arbeiten. (Datenschutz- und Sicherheitsgründe). Also will ich bequem bestimmte Datensätze nach Excel auswerfen
Danke für Deine Antwort. Der Link führt zu einer guten Sammlung.
Meine Frage zu 1) war was muss ich tun, damit Excel die Formel, die je funktioniert und richtig übertragen wir auch als Formel erkennt (vermutlich den Excel es sei Text).
Frage 2) Habe ich nachgeschoben
Hallo,
Zu 1)
wo ist das Problem, verwende in Access die Access Formel und in Exceld die Excel Formel.
Also für den Export nach Excel:
Alter: DatDiff("YYYY";[Geburtsdatum];Datum()) + (Format(Datum(); "mmdd") < Format([Datumsfeld]; "mmdd"))
Eventuell YYYY gegen JJJJ und DD gegen TT tauschen.
Zu 2)
Das ist im Prinzip bereits fasch. An eine Zahl hängt man keine Buchstaben. Mit Buchstaben ist es keine Zahl mehr und muss mit Konvertierungsfunktionen behandelt werden.
Die Buchstaben gehören in eine eigen Spalte der Tabelle.
Ein Datenbankfeld sollte grundsätzlich nur eine Info beinhalten (Atomare Inhalte = 1.Normalform)
Hallo,
Zitat von: DiedrichsHW am Mai 22, 2014, 13:03:18In Excel funktioniert die Formel
Klar, sind ja auch Excel-Funktionen. Aber Access kann damit nichts anfangen, da musst du den Ausdruck schon in "Access-Sprache" übesetzen.
Zitat
Ich brauch dann "Einheiten" an der Zahl, z.B. 22g oder 22a, die weiterhin rechen sollen.
Mit 22g oder 22a lässt sich schlecht rechnen, das sollte dir eigentlich klar sein.
...upp, da war ich mal wieder zu langsam ???
1) Das Problem ist dass Excel den String, der von Access kommt nicht als Formel interpretiert.
2) Es gibt in Excel die Möglichkeit über benutzerdefiniertere Formatierung, Zahlen mit Buchstaben zu kennzeichnen, z.B. "m²" "m" "Einheit", da ist alles möglich. nun möchte ich das über eine Wenn dann Formel variieren und such den Befehl "bedingte Formatieren" im Formelformat und nicht in Menüversion.
Ich widerspreche mit 22g lässt es sich gut rechnen, weil es als Einheit formatiert ist. Man kann ja auch mit farbigen Ziffern rechnen. Das ist eine "Format"- Sache.
Hallo,
willst Du die Formel exportieren, oder das Ergebnis ?
Wozu die Formel ? Wozu überhaupt das Alter exportieren.
Sowohl in Excel als auch in Access kann man ja das Alter jeweils rechen.
Du kannst über das Eingabeformat (nicht Format) dafür sorgen, dass die Einheit mit gespeichert wird, aber das geht nur mit dem Datentyp Text, damit ist die Zahl zum Rechnen verloren.
Die Einheiten sind in einer separaten Tabelle zu führen und als Fremdschlüssel in der Datentabelle zu speichern. Die Einheit als Anhängsel der Zahl zu formatieren halte ich für den falschen Weg. Wenn Du es doch willst, musst Du in der Abfrage zum Export den Formatbefehl anwenden.
PS1:
Die bedingte Formatierung als VBA (=Formel) ist ein ziemlicher Lernaufwand und relativ kompliziert.
PS2:
Alter als Feldname (oder Variable) ist zu vermeiden, da es einen gleichlautenden SQL Befehl gibt.
PS3:
Warum beschäftigst Du 2 Foren?
http://www.ms-office-forum.net/forum/showthread.php?t=309273 (http://www.ms-office-forum.net/forum/showthread.php?t=309273)
In beiden Foren ist das unerwünscht (siehe Regeln) und führt eher zu weniger Antworten als mehr. Viele klinken sich bei Grossposting aus.
Das mit zwei Foren, war am Anfang um schnell eine Antwort zu erhalten und wegen Unerfahrenheit. Nun geht im andren Forum gerade um "Function". Ferner muss ich probieren wo ich mich für die Zukunft wohler fühle, also vergleichen. Kannst Du mir da raten?
Zweitens habe ich im Augenblick das Gefühl, dass wir etwas aneinander vorbeireden. Vielleicht versterbst Du meine Sprache nicht so gut, weil ich unfachmännisch Rede.
Ich arbeite an einer hochflexiblen Excel-Tabelle und wollte sie gerne fertig aus Access heraus gebähren. Es geht gut und hängt nun nur am Übertragen der Formel.
Wenn letzeres nicht geht muss ich über eine VBA-Sache das in Excel dann generieren.
Ich neige zum Prinzipiellen und Perfekten und hätte gerne Excel fix und fertig aus Access mit einigen Formelspalten erzeugt.
Wenn das so ist, musst du dich für eine VBA-Lösung entscheiden.
Aber:
- Wie oft wird diese Tabelle denn erstellt?
- Warum muss die Excel-Tabelle Formeln enthalten, warum genügen nicht die damit berechneten Werte?
Es ist nicht für mich, sondern Mitglieder unseres Vereins sollen gefilterte Listen im Excel-Format bekommen (nicht alles wegen Datenschutz).
Die Listen werden von der zentralen Datenbank ab und dazu aktualisiert / verschickt.
Der Excel-Anwender soll natürlich immer das aktuelle Alter sehen, also muss die Formel in Excel stehen.
Ich hätte gerne eine hoch flexible Excel-Tabelle aus Access erzeugt ohne ohne nochmals in Excel eine VBA-Routine laufen zu lassen. Die Altersspalte von Hand einzubauen ist bei der Vielzahl der Exporte zu aufwändig.
Wen Ihr Experten keine Antwort habt muss ich wohl doch mit VBA frisierte Excel-Tabellen vorgeben, was auch umständlich ist.
Es gibt da noch weitere Umstände, was man hier aus Platzgründen nicht alles erläutern kann.
Hallo,
abschliessend:
ZitatIch hätte gerne eine hoch flexible Excel-Tabelle aus Access erzeugt
wie Anne Berg es schon sagte, da muss Automation von Excel herhalten ...
Ich befürchte aber, es wird schwierig , wenn Du nichts mit "Public Function .. " anfangen kannst. Deshalb rate ich zum Durcharbeiten eines VBA-Grundlagen-Tutorials für Access UND für Excel..
Zitatohne ohne nochmals in Excel eine VBA-Routine laufen zu lassen
Mir scheint da liegt ein Missverständnis vor. Hier denkt niemand (außer dir?) daran, die VBA-Nachbesserungen in Excel vorzunehmen. Du kannst die Tabelle wie gewünscht komplett aus Access heraus erzeugen, nur nicht mit einem einfachen Export-Befehl, sondern per Automatisierung von Excel via VBA.
Nachtrag:
Zitatwie Anne Berg es schon sagte
häh... ???
:) Vielen Dank!
A) Ihr habt mir wesentlich geholfen.
Ich löse dann mein Problem über Excel-VBA.
B) Mit VBA habe ich nur mittlere Erfahrung.
Aber mein Verwaltungssystem für unseren Verein funktioniert gut und die hochflexiblen Excel-Ableger auch. Alle enthalten sehr viele VBA-Routinen.
Durch die Autodidaktik habe ich spezielles Wissen neben vielen Lücken. Sorry und danke.
ZitatIch löse dann mein Problem über Excel-VBA.
da war ich wohl nicht deutlich genug:
Zitatsondern per Automatisierung von Excel - aus Access heraus - via VBA.
Danke, nochmals für den Hinweis.
Aber alles (auch Tabellengestallung) über VBA zu machen ist auch aufwändig, das geht über Export aus Abfrage gut.
Oder:
In SQL direkt muss ich gucken wie es geht. Oft geht dann die graphische Oberfläche nicht mehr. Ist aber ein Versuch Wert.
Vielleicht kann ich in die Graphische Abfrage eine Beug zu einem Modul machen.
Was empfiehlst Du?
Hallo,
@DiedrichsHW: Du sprichst in Rätseln..
PS: @MaggieMay: Mit "Anne Berg" habe ich auf das Crossposting angespielt..
http://www.ms-office-forum.de/forum/showthread.php?t=309273
Hi,
Zitat von: DiedrichsHW am Mai 22, 2014, 19:23:59
In SQL direkt muss ich gucken wie es geht.
was meinst du damit, was genau willst du hier mit SQL lösen?
ZitatOft geht dann die graphische Oberfläche nicht mehr.
Wenn du damit den Abfrageentwurf meinst, so gibt es durchaus Konstellationen, bei denen das passieren kann, bei einer Auswahlabfrage wohl eher nicht.
ZitatVielleicht kann ich in die Graphische Abfrage eine Beug zu einem Modul machen.
Du kannst in einer Abfrage (auch selbstgeschriebene) Funktionen einsetzen, ich wüsste aber nicht, was dir das nützen sollte.
Am Ende erzeugst du für Excel doch wieder nur Feldinhalte, Formeln kannst du auf diese Weise - wie bereits (mehrfach?) gesagt - nicht erzeugen.
In der Zeit des Diskutierens über unbegehbare Lösungswege hätte die Prozedur schon längst geschrieben sein können. Es ist wirklich nicht so schwer. Du brauchst ein Excel.Application-Objekt, öffnest damit eine Excel-Vorlage, kopierst die Daten via CopyFromRecordset in die Tabelle und kannst anschließend in Schleifen über die Zellen Formeln einsetzen oder Formatierungen vornehmen oder was immer du willst.
Allen Dank für Ratschläge, Geduld und Mühe.
Darf ich nochmals nerven:
Diese Formeln funktionieren in Excel einwandfrei:
'Funzt in Excel: =WENN(ISTLEER(K:K);"*?";WENN(ISTFEHLER(DATEDIF(K:K;HEUTE();"Y"));"J?";(DATEDIF(K:K;HEUTE();"Y"))))
'Funzt in Excel: =WENN(ISTLEER(INDIREKT(ADRESSE(ZEILE();SPALTE()+1)));"*?";WENN(ISTFEHLER(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"));"J?";(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"))))
'Funzt in Excel : 'Selection.Replace What:="a1b2c3", Replacement:="aaaa", LookAt:=xlPart, _
'SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,ReplaceFormat:=False
Sie funktionieren in VBA Aces bei Zugriff auf Excel aber nicht.
Das Prinpip müsst stimmen, weil sich einfache Formeln wie .Range("J2:J1000").Formula "=2*3" richtig übertragen.
Was könnte da falsch sein?
Hier mein ganzer Code:
Public Function excel_exp()
Dim xlAnw As Object
Dim xlMappe As Variant
xlMappe = CurrentProject.Path & "\StaMi_Übertrag_Kontakte" & Format(Now, "YYYY.MM.DD_HH.MM") & ".xls"
xlTabelle = "Transfer"
' Export: per Befehl: Ausdruck.TransferSpreadsheet(TransferType, SpreadsheetType,TableName, FileName, HasFieldNames, Range, UseOA)
On Error GoTo Fehler1
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "A_ExportKontakte", xlMappe, , xlTabelle
On Error GoTo Fehler1
' Set xlAnw = CreateObject("excel.application") Damit werden die Daten in Excel formatiert:
' TIPP: Danach kannst Du bei dem Excel Applikation Objekt mit den VBA Funktionen aus Excel arbeiten.
' TIPP: Bsp.: Excelapp.Range("A1").Offset(Reihe, Spalte).Value
' TIPP: Die Variablen Reihe und Spalte nutze ich für automatische Suchprozeduren in Exceltabellen.
' TIPP: '=WENN(ISTLEER(INDIREKT(ADRESSE(ZEILE();SPALTE()+1)));"*?";WENN(ISTFEHLER(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"));"J?";(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"))))'
On Error GoTo Fehler1
Set xlAnw = CreateObject("excel.application")
On Error GoTo Fehler1
xlAnw.Visible = False ' läuft unsichtbar im Hintergrund
On Error GoTo Fehler1
xlAnw.Workbooks.Open FileName:=xlMappe ' war: xlAnw.Workbooks.Open FileName:=CurrentProject.Path & "\StaMi_Übertrag_Kontakte.xls"
On Error GoTo Fehler1
With xlAnw
.Sheets(xlTabelle).select
.Rows("1:1").select
.selection.Font.Bold = True ' Beispiel für Schrift = Fett
.Cells.select
.selection.Columns.ColumnWidth = 10 'Splatenbereite einstellen
.Range("B1:M20").select ' Bereich für automatische Breite einstellen
.selection.Columns.AutoFit ' optimale Zellenbreite
'xlAnw.Range("J:J").Select
'xlAnw.Selection.Replace What:="a1b2c3", Replacement:="xx", LookAt:=xlWhole
'xlAnw.Selection.Replace , "a1b2c3", "bb", xlpart
.Columns("J:J").select
On Error GoTo Fehler2
'Selection.Replace What:="a1b2c3", Replacement:="aaaa", LookAt:=xlPart, _
'SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
'ReplaceFormat:=False
'.Range("J2:J1000").FormulaLocal = "=WENN(ISTLEER(INDIREKT(ADRESSE(ZEILE();SPALTE()+1)));"*?";WENN(ISTFEHLER(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"));"J?";(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"))))"
'.Range("J2:J1000").FormulaLocal = "=3*2" '=WENN(ISTLEER(INDIREKT(ADRESSE(ZEILE();SPALTE()+1)));"*?";WENN(ISTFEHLER(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"));"J?";(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"))))"
'.Range("J2:J1000").Formula = "=IF(IsEmpty(INDIRECT(ADDRESS(row(),COLUMN()+1))),"*?",IF(Iserror(DATEDIF(INDIRECT(ADDRESS(row(),COLUMN()+1)),TODAY(),"Y")),"J?",(DATEDIF(INDIRECT(ADDRESS(row(),COLUMN()+1)),TODAY(),"Y"))))"
'.Range("J2:J1000").FormulaLocal = "=WENN(ISTLEER(K:K);"*?";WENN(ISTFEHLER(DATEDIF(K:K;HEUTE();"Y"));"J?";(DATEDIF(K:K;HEUTE();"Y"))))"
'Funzt in Excel: =WENN(ISTLEER(K:K);"*?";WENN(ISTFEHLER(DATEDIF(K:K;HEUTE();"Y"));"J?";(DATEDIF(K:K;HEUTE();"Y"))))
'Funzt in Excel: =WENN(ISTLEER(INDIREKT(ADRESSE(ZEILE();SPALTE()+1)));"*?";WENN(ISTFEHLER(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"));"J?";(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"))))
'Funzt in Excel : 'Selection.Replace What:="a1b2c3", Replacement:="aaaa", LookAt:=xlPart, _
'SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,ReplaceFormat:=False
'Funzt aber nicht hier in VBA von Access bei Zugriff auf Excel
On Error GoTo Fehler2
'.Selection.Replace What:="a1b2c3", Replacement:="aaaa", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'.selection = "BB"
'"=WENN(ISTLEER(INDIREKT(ADRESSE(ZEILE();SPALTE()+1)));"*?";WENN(ISTFEHLER(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"));"J?";(DATEDIF(INDIREKT(ADRESSE(ZEILE();SPALTE()+1));HEUTE();"Y"))))"
End With
GoTo Schließen
Fehler1:
MsgBox "Da war ein Fehler bei der Speicherung"
GoTo Schließen
Fehler2:
MsgBox "Da war ein Fehler beim Suchen und Ersetzen"
GoTo Schließen
Schließen:
xlAnw.activeWorkbook.Save
xlAnw.activeWorkbook.Close
xlAnw.Quit
Set xlAnw = Nothing
Set xlMappe = Nothing
End Function
Hallo,
ersetz mal die Gänsefüße IM zugewiesenen String durch je einen Doppel-Gänsefuß..Evtl. funktioniert auch anstelle ein Hochkomma
z. B.:
Range("J2:J1000").FormulaLocal = "=WENN(ISTLEER(K:K);""*?"";WENN(ISTFEHLER(DATEDIF(K:K;HEUTE();""Y""));""J?"";(DATEDIF(K:K;HEUTE();""Y""))))"
Warum, habe ich nicht eher gefragt. DANKE. Es funktioniert. Aber woher soll man das wissen. Es stand öfters da man könne ganz normale Excel-Schreibweise benutzen. DANKE.
Aber warum funktioniert replace nicht hier (Access im Excel-Modus) jedoch dort (Excel) geht es?
Kannst Du mir da genau so einfach und gut helfen?
Dritte Frage:
warum geht auch das nicht, es steht innerhalb der With-Reihe (siehe oben):
letzteZeile = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
mit Punkt geht es auch nicht:
letzteZeile = .ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Hallo,
Zitat von: DiedrichsHW am Mai 24, 2014, 22:02:36Aber woher soll man das wissen.
Dass Gänsefüßchen innerhalb eines Strings zu verdoppeln sind, weil sie sonst nicht als solche erkannt werden können, gehört zum Grundwissen im Umgang mit String-Zuweisungen.
Mit ein wenig logischem Denken müsste man zumindest drauf kommen, dass es so wie du das geschrieben hast, nicht funktionieren
kann.
ZitatAber warum funktioniert replace nicht hier (Access im Excel-Modus) jedoch dort (Excel) geht es?
Die Replace-Funktion hat in Excel eine völlig andere Syntax, da könnte man drauf kommen, wenn man sich die jeweilige Hilfe mal anschaut.
Zitatmit Punkt geht es auch nicht:letzteZeile = .ActiveSheet.Cells([color=red]Rows[/color].Count, 1).End(xlUp).Row
Schau doch einfach mal ganz genau hin, was in dem Ausdruck alles angesprochen wird.
Und "geht nicht" ist übrigens eine ganz miserable Fehlerbeschreibung.
Und noch etwas zu deinem Code:
Die On Error-Anweisung muss nicht ständig wiederholt werden. Einmal gesetzt ist sie gültig bis eine andere On Error-Anweisung kommt.
Danke.
Ich lerne, danke Deiner Hilfe dazu.
Kann es sein, dass meinem Access ein Add-Inn für Excel-Befehle fehlt. Wo bekomme ich das ggf. her und wie heißt es?
Denn:
'so hätte ich es gerne:
'aber die Befehle, die in einer with-Reihe stehen funktionieren nicht. Das Programm bleibt in Access hängen und arbeitet NICHT von Access aus in der Exceldatei, wie es soll:
letzteZeile = .ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
.Range(Cells(2, 12), Cells(letzteZeile, 12)) = "Formel"
'deshalb habe ich einen Skript-Tests gemacht:
.Range("J2:J100").FormulaLocal = "Formel" ' Das funktioniert von Access aus in Exceldatei
.Range(Cells(2, 12), Cells(100, 12)) = "Formel" 'gibt Fehlermeldung: "Fehler beim Kompilieren. Sub oder Function nicht definiert."
Die gleichen Befehle arbeiten aber direkt in Excel eingesetzt.
Was kann da falsch sein?
Was daran falsch ist habe ich doch bereits versucht dir zu sagen.
Es müssen alle Excel-Objekte auf das Application-Objekt (oder ein anderes Excel-Objekt) bezogen werden, also nicht so:letzteZeile = .ActiveSheet.Cells([color=red]Rows[/color].Count, 1).End(xlUp).Rowsondern so:letzteZeile = .ActiveSheet.Cells([color=blue].Rows[/color].Count, 1).End(xlUp).RowGleiches gilt für die folgenden Befehle:.Range([color=blue].Cells[/color](2, 12), [color=blue].Cells[/color](letzteZeile, 12)) = "Formel"usw.
A) Danke - super und schnell.
B) Das mit dem Punkt habe ich bei Cells nicht beachtet. Jetzt läuft dieser Abschnitt.
c) Aber um 22:50:45 habe ich schon wegen mit und ohne Punkt gefragt. Denn an dieser Zeile bleibt es auch mit Punkt hängen, da muss wohl noch ein Fehler sein:
Dim letzteZeile As Long
letzteZeile = .ActiveSheet.Cells(.Rows.Count, 1).End(xlUp).Row
Um 22:50 fehlte noch der Punkt vor "Rows".
Danke. Ja. Aber es hängt auch mit Punkt vor Rows. Also so: letzteZeile = .ActiveSheet.Cells(.Rows.Count, 1).End(xlUp).Row
Was genau meinst du mit "es hängt"? Tritt ein Fehler auf? Wie lautet die Fehlermeldung?
Es wäre vermutlich leichter dir zu helfen, wenn man das mal an einer Beispiel-DB austesten könnte.
Fehlermeldung: Laufzeitfehler 1004, Anwendungs- und Objektorientdefinirter Fehler. Ich habe auch schon die Variable umbenannt. Das hilft auch nichts.
Hallo,
letzteZeile = .ActiveSheet.Cells(.ActiveSheet.Rows.Count, 1).End(xlUp).Row
wäre besser, wenn die einzelnen Objekte zunächst Variablen mit "set xyz = abc" zugewiesen würden und die Variablen weiterhin Verwendung fänden.
tut mir leid, funzt auch nicht.