Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Fehler im VBA-Code

Betrifft: Fehler im VBA-Code von: Torsten K.
Geschrieben am: 26.08.2004 08:56:00

Guten Morgen beisammen,

Ausgangslage: In meinen Budgetdateien (ca. 30 Dateien) möchte ich ein Worksheet und im Anschluß daran Formeln einfügen. Das zu kopierende WS liegt in der Datei ("AddWS.xls").Sheets("SumET"). Aus dieser Datei heraus möchte ich alle Budgetdateien , die in der gleichen Datei im Sheets("FILES") aufgelistet sind,
aufrufen und das WS("SumET") sowie die im Code enthaltenen Formeln einfügen.
Leider bleibt Excel bei all meinen Versuchen an der Stelle hängen:

With Workbooks("AddWS_TEST")
(Fehlermeldung: "Index außerhalb des gültigen Bereichs")

Hier der komplette Code:

Private Sub cmb_UpDate_Click()

Dim i As Integer
Dim Workbook As Workbook
Dim wbName As String

'On Error GoTo errHandler

Application.ScreenUpdating = False
Application.StatusBar = "Dieser Vorgang dauert ein paar Minuten. Bitte Geduld haben..."
Application.DisplayAlerts = False
Application.EnableEvents = False

For i = 1 To Sheets("FILES").Range("A1").End(xlDown).Row
wbName = Sheets("FILES").Cells(i, 1)
Workbooks.Open wbName, 3
    ActiveWorkbook.Save
    
'    Set ActiveWorkbook = Workbooks("AddWS_TEST")
'    Workbooks("AddWS_TEST").Sheets("SumET").Copy Before:=Workbooks(wbName).Sheets(17)
'    Set ActiveWorkbook = Workbooks(wbName)
    
With Workbooks("AddWS_TEST")
    .Activate
    .Sheets("SumET").Copy Before:=Workbooks(wbName).Sheets(17)
End With

    'Formeln einfügen in das bereits kopierte WS in wbName:
With Workbooks(wbName).Sheets("SumET")
    .Range("E3").FormulaR1C1 = "='ET120'!R6C4"
    .Range("F3").FormulaR1C1 = _
        "=SUMIF('ET120'!R[4]C[-2]:R[35]C[-2],""C"",'ET120'!R[4]C[-1]:R[35]C[-1])"
    .Range("E4").FormulaR1C1 = "='ET140'!R6C4"
    .Range("F4").FormulaR1C1 = _
        "=SUMIF('ET140'!R[4]C[-2]:R[35]C[-2],""C"",'ET140'!R[4]C[-1]:R[35]C[-1])"

End With
    ActiveWorkbook.Save
    ActiveWorkbook.Close , True
Next i

Application.DisplayAlerts = True
Application.StatusBar = False
Application.EnableEvents = True

MsgBox "Dateien wurden aktualisiert", vbOKOnly

Application.ScreenUpdating = True

Exit Sub

errHandler:
Application.DisplayAlerts = True
Application.StatusBar = False
Application.EnableEvents = True
MsgBox "Beim Update der Datei" & vbCr & wbName & vbCr & _
        "ist ein Fehler aufgetreten!", vbCritical + vbOKOnly, "ABBRUCH!"
'ActiveWorkbook.Close , True

End Sub


Könnt Ihr mir bitte helfen. Wäre wirklich wichtig, damit ich den Vorgang nicht manuelle ausführen muß. Vielen Dank im voraus.

Gruß
Torsten
  


Betrifft: AW: Fehler im VBA-Code von: Dan
Geschrieben am: 26.08.2004 09:07:00

Hallo Torsten
statt With Workbooks("AddWS_TEST") schreib With Workbooks("AddWS_TEST.xls"), dann sollte es funzen. Gruss Dan


  


Betrifft: AW: Fehler im VBA-Code von: Torsten K.
Geschrieben am: 26.08.2004 09:42:41

Hallo Dan,

vielen Dank für Deine Rückmeldung.
Das war ja schon ein echt peinlicher Fehler!!

Leider bleibt Excel jetzt an der Stelle hängen:

.Sheets("SumET").Copy Before:=Workbooks(wbName).Sheets(17)

mit der gleichen Fehlermeldung ("Index außerhalb des gültigen Bereichs").
Woran kann's denn jetzt noch liegen?

Gruß
Torsten


  


Betrifft: AW: Fehler im VBA-Code von: Dan
Geschrieben am: 26.08.2004 09:56:38

Hallo,
es kann daran liegen, dass :
Sheets("SumET") oder Workbooks(wbName) oder Sheets(17)
nicht existieren. Z.B. es gibt nur 16 Sheets oder so :-).
(benutze Locals Window oder Watch Window, Menu View)
Dan, CZ


  


Betrifft: AW: Fehler im VBA-Code von: Luschi
Geschrieben am: 26.08.2004 09:57:21

Hallo Torsten,

ich erkenne ím Code nicht, daß das Workbook "AddWS_TEST" überhaupt geöffnet ist.
Das solltest Du nicht manuell machen sondern im Code und mit vollständiger Laufwerks-, Pfad-, Datei- und Dateityp-Angabe.
Wenn Du mit mehreren Workbooks arbeitest, dann ist es wichtig, jedem Workbook ein Objekt zuzuordnen. Ansonsten kann es passieren, das nach dem Schließen des
"ActiveWorkbook" die angesprochenen Range- und Cell-Bereiche in der "Luft hängen"
So ist es bei folgender For-Schleife:
For i = 1 To Sheets("FILES").Range("A1").End(xlDown).Row
In der For-Schleife wird ein Workbook geöffnet. Dieses bekommt automatisch von Excel
den Status "ActiveWorkbook". Dann verschiebst Du den Status auf ein anderes Workbook.
Kurz vor Schleifendende schließt Du das Workbook mit dem o.g. Status. Und dann kommst
Du wieder an den Schleifenanfang und sprichst ein Scheet an, ohne genaue Zuweisung, in welchem Workbook dieses liegt. Das muß irgendwann schief gehen.
Deshalb sollte der Code wie folgt aussehen:

Private Sub cmb_UpDate_Click()

Dim i As Integer
Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
Dim wbName As String

'On Error GoTo errHandler

Application.ScreenUpdating = False
Application.StatusBar = "Dieser Vorgang dauert ein paar Minuten. Bitte Geduld haben..."
Application.DisplayAlerts = False
Application.EnableEvents = False


Set wb1 = ActiveWorkbook
Application.Workbooks.Open "c:\abc\AddWS_TEST.xls"
Set wb2 = ActiveWorkbook

For i = 1 To wb2.Sheets("FILES").Range("A1").End(xlDown).Row
    wbName = wb2.Sheets("FILES").Cells(i, 1).Value
    Application.Workbooks.Open wbName, 3
    Set wb3 = ActiveWorkbook
    wb3.Save
    
    With wb2
        .Activate
        .Sheets("SumET").Copy Before:=wb3.Sheets(17)
    End With

    'Formeln einfügen in das bereits kopierte WS in wbName:
    With wb3.Sheets("SumET")
       .Range("E3").FormulaR1C1 = "='ET120'!R6C4"
       .Range("F3").FormulaR1C1 = _
        "=SUMIF('ET120'!R[4]C[-2]:R[35]C[-2],""C"",'ET120'!R[4]C[-1]:R[35]C[-1])"
       .Range("E4").FormulaR1C1 = "='ET140'!R6C4"
       .Range("F4").FormulaR1C1 = _
        "=SUMIF('ET140'!R[4]C[-2]:R[35]C[-2],""C"",'ET140'!R[4]C[-1]:R[35]C[-1])"
    End With
    wb3.Save
    wb3.Close , True
Next i

Application.DisplayAlerts = True
Application.StatusBar = False
Application.EnableEvents = True

Set wb3 = Nothing
Set wb2 = Nothing
Set wb1 = Nothing

MsgBox "Dateien wurden aktualisiert", vbOKOnly

Application.ScreenUpdating = True

Exit Sub

errHandler:
Application.DisplayAlerts = True
Application.StatusBar = False
Application.EnableEvents = True
MsgBox "Beim Update der Datei" & vbCr & wbName & vbCr & _
        "ist ein Fehler aufgetreten!", vbCritical + vbOKOnly, "ABBRUCH!"
'ActiveWorkbook.Close , True

End Sub



Gruß von Luschi
aus klein-Paris


  


Betrifft: AW: Fehler im VBA-Code von: Torsten K.
Geschrieben am: 26.08.2004 10:15:19

Hallo Luschi,

vielen Dank für Deine ausführliche Rückmeldung.
Ich hatte geschrieben, daß der Code aus dem bereits geöffneten WB("AddWS_TEST.xls") heraus gestartet wird. Deshalb brauch' ich das doch nicht noch einmal zu öffnen, oder?
Wie müßte denn der Code aussehen, wenn man dies berücksichtigt? Sorry, ich bin noch nicht so fit, daß ich das "aus dem Ärmel schütteln" kann. Und den von mir erarbeiteten Code habe ich aus mehreren anderen einfach "zusammengeflickt".
Wäre nett, wenn Du mir noch ein bißchen unter die Arme greifen könntest.

Gruß
Torsten


  


Betrifft: AW: Fehler im VBA-Code von: andre
Geschrieben am: 29.08.2004 12:02:06

Hallo Torsten,
wenn es schon offen ist gehts auch ohne.
Wenn Du das Workbook ansprechen willst geht auch ThisWorkbook ....
Zusatzfrage: Warum tust Du das Workbook mit den daten gleich nach dem Öffnen Speichern? Hast Du da verknüpfungen drin oder willst Du das FileDatum ändern? wenn nicht, kansnst Du das weglassen. Außerdem speicherst Du es ja weiter unten noch mal.
Das .activate in
With wb2
.Activate
.Sheets("SumET").Copy Before:=wb3.Sheets(17)
End With
ist denke ich auch nicht nötig. Schreibe nur

wb2.Sheets("SumET").Copy Before:=wb3.Sheets(17)



  


Betrifft: AW: erledigt - mT von: Torsten K.
Geschrieben am: 31.08.2004 09:39:53

Hallo Andre,

vielen Dank für Deinen Tip.
Mit Deiner und anderer Teilnehmer Hilfe habe ich's nunmehr hinbekommen.

Gruß
Torsten


 

Beiträge aus den Excel-Beispielen zum Thema "Fehler im VBA-Code"