Mit Access VBA Excel-Dateien erstellen / Excel blo

Computer: Software & Programmierung Visual Basic
#1

Hallo liebe WWW-ler,

ich habe eine recht umfangreiche ACCESS-Anwendung geschrieben, in der Excel-Dateien per VBA erstellt werden.
Das läuft auch alles wundervoll, es werden einige Dateien erstellt. Das dauert natürlich seine Zeit. Nun habe ich das Problem, dass ich jedesmal einen Absturz der Ausgabe produziere, wenn ich während die Ausgabe läuft Excel öffne. Wie kann ich Excel entweder im Hintergrund laufen lassen bzw. so sperren, dass man keine weitere Instanz öffnen kann.
Ich nutze Windows XP, Access 2003 (SP2), Excel 2003(SP3). Änderungen an der Konfiguration des Systems sind nicht möglich, da es ein Firmenrechner ist und alles Patches etc. von der Firma vorgegeben werden.
Hier Auszüge des VBA-Codes:

Dim oexcel As New Excel.Application
.
.
.

With oexcel

.Workbooks.Add
.Application.DisplayAlerts = False
.Worksheets.Add Count:=7
.
.
.
End with
oexcel.Quit
Set oexcel = Nothing

Ich hoffe, Ihr könnt mir helfen. Ich habe zwar schon viel programmiert, aber bei den Grundlagen hapert es…

Liebe Grüße
Tina

#2

Hi Tina,

ich habe eine recht umfangreiche ACCESS-Anwendung geschrieben,
in der Excel-Dateien per VBA erstellt werden.

zeige mal bitte den Code, rein aus Interesse wie so Access-Vba-Code aussieht.

Zur evtl. Problemlösung ist mir natürlich sehr viel lieber, ich las das Wort „umfangreiche“ :smile:, du bastelst eine mdb, so mit 3 Spalten und 100 zeilen, gefüllt mit völlig egalen Zufallswerten.

Dann bastelst du dazu Vba-Code, der dann Exel startet, ein Workbook anlegt samt 50 Worksheets und dann mit einer langsamen Schleife über alle Excelzellen alle deine Access-Zellen überträgt.

Also etwas was sehr lange läuft.

Dann kann ich das starten in Access, und während es läuft Excel in zweiter Instanz starten.

Das läuft auch alles wundervoll, es werden einige Dateien
erstellt. Das dauert natürlich seine Zeit. Nun habe ich das
Problem, dass ich jedesmal einen Absturz der Ausgabe
produziere, wenn ich während die Ausgabe läuft Excel öffne.

Wenn es nun mit deiner beispiel-Accessdatei klappt bei mir und bei dir , daß ich die zweite Excelinstanz problemlos starten kann liegt es irgendwie an dem „umfangreichen“ Code deiner Original-Accessdatei.

„Ausgabe“ ist vieldeutig, bau in den Beispielcode die gleiche Ausgabe ein. Was genau meinst du eigentlich damit? zelleneintrag?, UF?, Textbox?

Wichtig für mich ist, daß ich den Fehler nachstellen kann.

Sorry, ich habe grad, als ich mein Geschreibsel überflog, das ganze starke Gefühl, ich hätte als Antwort nur schreiben sollen: „Wichtig für mich ist, daß ich den Fehler nachstellen kann.“

Da steckt ja alles drin.

Und es gibt zwei konkrete Gründe die ich glaube zu wissen, aber die mich jetzt gewltig verwirren.

a) Es ist gar nicht so einfach aus einer Excelinstanz auf eine andere zuzugreifen
b) d.h. aber auch, der einen Instanz ist es völlig wurscht was die andere Instanz so treibt

Wie kann ich Excel entweder im Hintergrund laufen lassen

Im Hintergrund bedeutet mit minimiertem oder ausgeblendeten Fenster, wenn es da Abtsurz gibt wenn du eine neue Excel-Instanz startest ist das m.E. egal.

bzw.
so sperren, dass man keine weitere Instanz öffnen kann.

Dazu fällt mir jetzt was brutales ein, was auch klappen würde *denk*, aber ich schau mal obs was eleganteres gibt.
Ist jetzt nur fix spontan gedacht, eine Makro in der personl.xlx von Excel, daß beim Start von Excel einfach die Excel.exe umbenennt, beim Schließen wieder korrigiert.

D.h. während Excel läuft, ist es egal ob du eine xls doppelklickst oder Excel starten willst, du kriegst eine Fehlermeldungg, da Excel.exe nicht gefunden wird.

Wie gesagt, nur ne spontane Idee.

Ich nutze Windows XP, Access 2003 (SP2), Excel 2003(SP3).
Änderungen an der Konfiguration des Systems sind nicht
möglich, da es ein Firmenrechner ist und alles Patches etc.
von der Firma vorgegeben werden.

Warum möchtest du wegen dieses Problems die Konfiguration des Systems ändern?

Aber da fällt mir ein, um mir unbekannte Fehlerquellen auszuschließen, passiert das Geleiche auch auf anderen Rechnern in euerem „System“ ?

Und, noch wichtiger, kannst du das mal an einem EinzelPC testen oder testen lassen durch einen dem du mal die Orginaldatei schickst?

Ich hoffe, Ihr könnt mir helfen. Ich habe zwar schon viel
programmiert, aber bei den Grundlagen hapert es…

Ist normal, VBa-Einsteiger überspringen sehr oft die Grundlagen, zumindest viel mehr als C, VB, Perl, java Programmierer.

Gruß
Reinhard

#3

Hallo Reinard,

vielen Dank, dass Du Dich meines Problems annimmst!!!

zeige mal bitte den Code, rein aus Interesse wie so
Access-Vba-Code aussieht.

Den willst Du nicht wirklich komplett sehen, ist einfach zu umfangreich, das eindenken würde einfach zu lange dauern, da dort zig Variablen definiert sind, Berechnungen durchgeführt werden, Zellen formatiert werden usw.

Zur evtl. Problemlösung ist mir natürlich sehr viel lieber,
ich las das Wort „umfangreiche“ :smile:, du bastelst eine mdb, so
mit 3 Spalten und 100 zeilen, gefüllt mit völlig egalen
Zufallswerten.

Dann bastelst du dazu Vba-Code, der dann Exel startet, ein
Workbook anlegt samt 50 Worksheets und dann mit einer
langsamen Schleife über alle Excelzellen alle deine
Access-Zellen überträgt.

Also etwas was sehr lange läuft.

Dann kann ich das starten in Access, und während es läuft
Excel in zweiter Instanz starten.

Das ist ne gute Idee, das mache ich und berichte dann, ob ich das gleiche Problem habe.

Das läuft auch alles wundervoll, es werden einige Dateien
erstellt. Das dauert natürlich seine Zeit. Nun habe ich das
Problem, dass ich jedesmal einen Absturz der Ausgabe
produziere, wenn ich während die Ausgabe läuft Excel öffne.

Wenn es nun mit deiner beispiel-Accessdatei klappt bei mir und
bei dir , daß ich die zweite Excelinstanz problemlos starten
kann liegt es irgendwie an dem „umfangreichen“ Code deiner
Original-Accessdatei.

„Ausgabe“ ist vieldeutig, bau in den Beispielcode die gleiche
Ausgabe ein. Was genau meinst du eigentlich damit?
zelleneintrag?, UF?, Textbox?

In der Ausgabe werden Werte in Zellen eingetragen und die Zellen werden formatiert.

Wichtig für mich ist, daß ich den Fehler nachstellen kann.

Sorry, ich habe grad, als ich mein Geschreibsel überflog, das
ganze starke Gefühl, ich hätte als Antwort nur schreiben
sollen: „Wichtig für mich ist, daß ich den Fehler nachstellen
kann.“

Da steckt ja alles drin.

Beim zweiten Mal lesen, habe ich das auch verstanden :wink:

Und es gibt zwei konkrete Gründe die ich glaube zu wissen,
aber die mich jetzt gewltig verwirren.

a) Es ist gar nicht so einfach aus einer Excelinstanz auf eine
andere zuzugreifen
b) d.h. aber auch, der einen Instanz ist es völlig wurscht was
die andere Instanz so treibt

Ich versteh nicht so ganz, was Du meinst…
Es wird ja leider keine zweite Instanz gestartet, wenn ich z.B. auf ein xls doppelklicke, sondern die gleiche und damit pfusche ich in das Makro rein.

Wie kann ich Excel entweder im Hintergrund laufen lassen

Im Hintergrund bedeutet mit minimiertem oder ausgeblendeten
Fenster, wenn es da Abtsurz gibt wenn du eine neue
Excel-Instanz startest ist das m.E. egal.

bzw.
so sperren, dass man keine weitere Instanz öffnen kann.

Dazu fällt mir jetzt was brutales ein, was auch klappen würde
*denk*, aber ich schau mal obs was eleganteres gibt.
Ist jetzt nur fix spontan gedacht, eine Makro in der
personl.xlx von Excel, daß beim Start von Excel einfach die
Excel.exe umbenennt, beim Schließen wieder korrigiert.

D.h. während Excel läuft, ist es egal ob du eine xls
doppelklickst oder Excel starten willst, du kriegst eine
Fehlermeldungg, da Excel.exe nicht gefunden wird.

Wie gesagt, nur ne spontane Idee.

Nicht so wirklich elegant, denn eigentlich würde ich ganz gerne während das Makro die 2. Excel-Datei schreibt, schon mal in die erste reingucken.

Ich nutze Windows XP, Access 2003 (SP2), Excel 2003(SP3).
Änderungen an der Konfiguration des Systems sind nicht
möglich, da es ein Firmenrechner ist und alles Patches etc.
von der Firma vorgegeben werden.

Warum möchtest du wegen dieses Problems die Konfiguration des
Systems ändern?

Wollte nur Tipps vorbeugen, die in die Richtung gehen, mit einer anderen Version probieren.

Aber da fällt mir ein, um mir unbekannte Fehlerquellen
auszuschließen, passiert das Geleiche auch auf anderen
Rechnern in euerem „System“ ?

Habe ich noch nicht getestet, muss ich mal ausprobieren.

Und, noch wichtiger, kannst du das mal an einem EinzelPC
testen oder testen lassen durch einen dem du mal die
Orginaldatei schickst?

Das wird schwierig. Einzel-PC haben wir hier nicht und zuhause hab ich kein Excel.

Ich hoffe, Ihr könnt mir helfen. Ich habe zwar schon viel
programmiert, aber bei den Grundlagen hapert es…

Ist normal, VBa-Einsteiger überspringen sehr oft die
Grundlagen, zumindest viel mehr als C, VB, Perl, java
Programmierer.

Gruß
Reinhard

Ich werde nun erst mal eine mdb zum Testen erstellen und melde mich dann wieder.
Gruß
Tina

#4

Hallo Reinhard,

ich habe jetzt eine mdb nach deinem Vorschlag erstellt.
Es werden 3 Excel-Dateien erstellt, in denen pro Datensatz ein Worksheet erstellt wird mit den Daten aus dem Datensatz. Der Einfachheit halber werden alle 3 Excel mit den gleichen Daten gefüllt.
Der VBA-Code sieht folgendermaßen aus:

Option Compare Database

Private Sub Excel_erstellen_Click()
On Error GoTo Err_Excel_erstellen_Click

Dim i As Long
Dim j As Long
Dim oexcel As New Excel.Application
Dim db As Database
Dim rs As Recordset
Dim VorgabePfad As String
Dim Pathname As String
Dim text As String

Set db = CurrentDb
Set rs = db.OpenRecordset(„Ausgabe_Daten“, dbOpenDynaset)

Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)

VorgabePfad = „C:\Daten“

With dlgOpen
.Title = „Ausgabepfad wählen:“
.AllowMultiSelect = False
.InitialFileName = VorgabePfad
If .Show = -1 Then
For Each FName In .SelectedItems
Pathname = FName
Next FName
End If
End With

j = 0
With oexcel

For i = 1 To 3 'Anzahl der Excel
.Workbooks.Add
Do While Not rs.EOF
j = j + 1
.Application.DisplayAlerts = False
.Worksheets.Add 'Count:=1
.Worksheets(j).Name = j
.Columns(1).ColumnWidth = 24
.Columns(2).ColumnWidth = 15
.Columns(3).ColumnWidth = 15
.Rows(1).RowHeight = 18
.Range(„A1:C2“).Interior.ColorIndex = 15
.Range(„A1:C2“).Interior.Pattern = xlSolid

.Range(„A1:C2“).Borders(xlEdgeLeft).LineStyle = xlContinuous
.Range(„A1:C2“).Borders(xlEdgeLeft).Weight = xlThin
.Range(„A1:C2“).Borders(xlEdgeLeft).ColorIndex = 1

.Range(„A1:C2“).Borders(xlEdgeTop).LineStyle = xlContinuous
.Range(„A1:C2“).Borders(xlEdgeTop).Weight = xlThin
.Range(„A1:C2“).Borders(xlEdgeTop).ColorIndex = 1

.Range(„A1:C2“).Borders(xlEdgeBottom).LineStyle = xlContinuous
.Range(„A1:C2“).Borders(xlEdgeBottom).Weight = xlThin
.Range(„A1:C2“).Borders(xlEdgeBottom).ColorIndex = 1

.Range(„A1:C2“).Borders(xlEdgeRight).LineStyle = xlContinuous
.Range(„A1:C2“).Borders(xlEdgeRight).Weight = xlThin
.Range(„A1:C2“).Borders(xlEdgeRight).ColorIndex = 1

.Range(„A1:C2“).Borders(xlInsideVertical).LineStyle = xlContinuous
.Range(„A1:C2“).Borders(xlInsideVertical).Weight = xlThin
.Range(„A1:C2“).Borders(xlInsideVertical).ColorIndex = 1

.Range(„A1:C2“).Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Range(„A1:C2“).Borders(xlInsideHorizontal).Weight = xlThin
.Range(„A1:C2“).Borders(xlInsideHorizontal).ColorIndex = 1

.Range(„a1:C2“).Font.Name = „Arial“
.Range(„A1:C2“).Font.Size = 14

'Schriftformatierung
.Range(„A1:C2“).Font.Bold = True
.Range(„A1:C2“).HorizontalAlignment = xlCenter

.cells(1, 1) = „Feld1“
.cells(1, 2) = „Feld2“
.cells(1, 3) = „Feld3“
.cells(2, 1) = rs!Feld1
.cells(2, 2) = rs!Feld2
.cells(2, 3) = rs!Feld3

rs.MoveNext
Loop
.Application.DisplayAlerts = True
text = Pathname & „\Excel“ & i & „.xls“
.ActiveWorkbook.SaveAs FileName:=text
Next i
End With
oexcel.Quit
Set oexcel = Nothing

Exit_Excel_erstellen_Click:

Exit Sub

Wenn ich das nun laufen lasse und währenddessen eine andere Excel-Datei öffne, dann erhalte ich die Meldung: „Index außerhalb des gültigen Bereichs“, weil dann nämlich das Makro versucht, in meine neu geöffnete Datei zu schreiben. In dem Moment, wo auch Worksheet(j) zugegriffen wird, bricht es ab, denn meine andere Excel-Datei hat nur 3 Worksheets.
Ich habe das noch mal im Debugging-Mode schrittweise laufen lassen, es ist so, dass ich Excel nicht als offene Anwendung auf meinem Rechner sehe, wenn das Makro läuft. Öffne ich jedoch die andere Excel-Datei, sehe ich 2 offene Arbeitsmappen, nämlich die, die ich gerade geöffnet habe und eine Mappe, in die das Makro gerade schreibt. Durch das Öffnen der anderen Excel-Datei wird diese aktiviert und das Makro schreibt munter in dieser Excel-Datei weiter, oder versucht es zumindest, solange bis irgendein Fehler auftritt.
Das möchte ich nun irgendwie verhindern. Möglichst so, dass eine zweite Excel-Instanz geöffnet wird, wenn ich eine andere Datei öffne.
Soll ich Dir mal die ganze mdb schicken?
Viele Grüße
Tina

#5

Hallo Tina,

With dlgOpen
.Title = „Ausgabepfad wählen:“
.AllowMultiSelect = False
.InitialFileName = VorgabePfad
If .Show = -1 Then
For Each FName In .SelectedItems
Pathname = FName
Next FName
End If
End With

Für was die For-Schleife? Durch „.AllowMultiSelect = False“ kann es doch nur eine Auswahl geben, oder sehe ich das falsch?

j = 0

Kannst du weglassen.

Alle Codezeilen die mit „.Range(„A1:C2“).Borders(“ beginnen kannst du löschen und dadurch ersetzen:

For N = 7 To 12
 .Range("A1:C2").Borders(N).LineStyle = xlContinuous
 .Range("A1:C2").Borders(N).Weight = xlThin
 .Range("A1:C2").Borders(N).ColorIndex = 1
Next N

Durch das Öffnen
der anderen Excel-Datei wird diese aktiviert und das Makro
schreibt munter in dieser Excel-Datei weiter, oder versucht es
zumindest, solange bis irgendein Fehler auftritt.

Ich sehe im Code nichts was Excel veranlassen könnte die Mappe in das es schreibt zu wechseln.

Das möchte ich nun irgendwie verhindern. Möglichst so, dass
eine zweite Excel-Instanz geöffnet wird, wenn ich eine andere
Datei öffne.
Soll ich Dir mal die ganze mdb schicken?

Lade sie bitte hoch mittels FAQ:2861 o.ä. und poste bitte hier den Link.

Gruß
Reinhard

#6

Hallo Tina,

With dlgOpen
.Title = „Ausgabepfad wählen:“
.AllowMultiSelect = False
.InitialFileName = VorgabePfad
If .Show = -1 Then
For Each FName In .SelectedItems
Pathname = FName
Next FName
End If
End With

Für was die For-Schleife? Durch „.AllowMultiSelect = False“
kann es doch nur eine Auswahl geben, oder sehe ich das falsch?

Ich sag ja, mir fehlen die Grundlagen, das ist zusammenkopiert und läuft…
Werde ich mir für die Zukunft merken und in meinen VBA noch ändern.

j = 0

Kannst du weglassen.

Alle Codezeilen die mit „.Range(„A1:C2“).Borders(“ beginnen
kannst du löschen und dadurch ersetzen:

For N = 7 To 12
.Range(„A1:C2“).Borders(N).LineStyle = xlContinuous
.Range(„A1:C2“).Borders(N).Weight = xlThin
.Range(„A1:C2“).Borders(N).ColorIndex = 1
Next N

Supi, danke, wenn ich das in meine DB einbaue wird der VBA-Code drastisch kürzer.
Wo finde ich denn eine Beschreibung, für welche Border welche Zahl steht?

Durch das Öffnen
der anderen Excel-Datei wird diese aktiviert und das Makro
schreibt munter in dieser Excel-Datei weiter, oder versucht es
zumindest, solange bis irgendein Fehler auftritt.

Ich sehe im Code nichts was Excel veranlassen könnte die Mappe
in das es schreibt zu wechseln.

Das möchte ich nun irgendwie verhindern. Möglichst so, dass
eine zweite Excel-Instanz geöffnet wird, wenn ich eine andere
Datei öffne.
Soll ich Dir mal die ganze mdb schicken?

Lade sie bitte hoch mittels FAQ:2861 o.ä. und poste bitte
hier den Link.

Hier der Link:
http://rapidshare.com/files/141767698/Excel_Test.mdb

Gruß
Reinhard

Gruß
Tina

#7

Hallo Tina,

Alle Codezeilen die mit „.Range(„A1:C2“).Borders(“ beginnen
kannst du löschen und dadurch ersetzen:

Supi, danke, wenn ich das in meine DB einbaue wird der
VBA-Code drastisch kürzer.
Wo finde ich denn eine Beschreibung, für welche Border welche
Zahl steht?

das weiß ich leider auch nicht.
Ich denke in Access wird es genauso sein, wenn nicht mach es in Excel, gehe in den VB-Editor, über Ansicht blende das Direktfenster ein.
Dort gibst du ein:

?xledgeright

und drückst die Entertaste, dann wird die Zahl angezeigt für die xledgeright steht.

Und, bei kleinen Codes würde ich xledgeright benutzen, wegen der besseren lesbarkeit, aber bei langen Codes die viel machen würde ich die Zahl benutzen, ist schlichtweg schneller.

Apropos Schnelligkeut, ich habe echt von Access-Vba keine Ahnung, aber probier das mal:

Sub xyz()
On errror goto Fehler
Application.screenupdating =False
’ dein Code
Fehler:
Application.screenupdating =True
End Sub

In Excel-Vba verhindert dies das während der Code-Ausführung die Bildschirmanzeige geändert wird, ist wie ein Turbo für den Code.

http://rapidshare.com/files/141767698/Excel_Test.mdb

Schaue ich mir morgen an oder vielleicht nachher, muß nochma weg.

Gruß
Reinhard