Über Abfrage Formel in Excel einsetzen (Export)

[DF6GL]

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

[MaggieMay]

Hi,

In SQL direkt muss ich gucken wie es geht.

was meinst du damit, was genau willst du hier mit SQL lösen?

Oft 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.

Vielleicht 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.

[DiedrichsHW]

Allen Dank für Ratschläge, Geduld und Mühe.

[DiedrichsHW]

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

[DF6GL]

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""))))"

[DiedrichsHW]

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?

[DiedrichsHW]

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

[MaggieMay]

Hallo,

Aber 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.

Aber 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.

mit Punkt geht es auch nicht:

Code [Auswählen] Erweitern

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.

[DiedrichsHW]

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?

[MaggieMay]

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:

Code [Auswählen] Erweitern

letzteZeile = .ActiveSheet.Cells([color=red]Rows[/color].Count, 1).End(xlUp).Rowsondern so:

Code [Auswählen] Erweitern

letzteZeile = .ActiveSheet.Cells([color=blue].Rows[/color].Count, 1).End(xlUp).RowGleiches gilt für die folgenden Befehle:

Code [Auswählen] Erweitern

.Range([color=blue].Cells[/color](2, 12), [color=blue].Cells[/color](letzteZeile, 12)) = "Formel"usw.

[DiedrichsHW]

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

[MaggieMay]

Um 22:50 fehlte noch der Punkt vor "Rows".

[DiedrichsHW]

Danke. Ja. Aber  es hängt auch mit Punkt vor Rows. Also so: letzteZeile = .ActiveSheet.Cells(.Rows.Count, 1).End(xlUp).Row

[MaggieMay]

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.

[DiedrichsHW]

Fehlermeldung: Laufzeitfehler 1004, Anwendungs- und Objektorientdefinirter Fehler. Ich habe auch schon die Variable umbenannt. Das hilft auch nichts.