Excel->Makros->Visual Basic->Frage!

[misterbecks]

Ich versuche gerade, ein Makro für Excel zu schreiben, damit ich eine nervige Anwendung nicht dauernd 100-fach ausführen muss. Es geht darum, aus csv's von bestimmten Stellen (das definieren dieser Stellen hat wunderbar geklappt) Teile zu kopieren und zu einer großen Tabelle zu verbinden.

Nun scheitert es noch an einer Stelle: Der Schleife. Ich habe noch düster in Erinnerung, dass man eine for...next Schleife programmieren kann.

z.B.

for i = x to y
...
next

Aber kann ich denn nun auch die Variable während der Schleife benutzen?

z.B.

for i 0 x to y
...i.csv
next

Oder geht es am Ende viel einfacher mit Apple Script?

 

[Quassel]

Das geht

Aber kann ich denn nun auch die Variable während der Schleife benutzen?

Die Schleife wird funktionieren, allerdings muss die von dir angegebene Schleife in VB mit next i abgeschlossen werden. Da x und y Zahlen sind, wird i logischerweise auch eine Zahl sein und kann als solche im Ausdruck verwendet werden. Wenn die CSV-Dateinamen durchnummeriert sind, gibt es so keine Probleme (sonst bieten sich Listen an). Wenn die CSVs vom Script erst geöffnet werden müssen, auf die korrekten Pfade achten, und und und...

So hässlich VB auch ist, für Excel ist es sinnvoller als AppleScript.

 

[misterbecks]

So hässlich VB auch ist, für Excel ist es sinnvoller als AppleScript.

Die Zusammenfassung der csv's muss nicht zwingend in Excel passieren. Das Endergebnis wird auch wahrscheinlich auch in anderen Programmen (R, etc.) weiterbearbeitet....

Wenn es mit Apple Script eine grundsätzlich einfacherer Methode geben würde, wäre ich dem nicht abgeneigt...

 

[Quassel]

Da die Selektion der Teile in VB funktioniert, kannst du eigentlich dabei bleiben. Die Abarbeitung der Schleife sollte da das geringste Problem darstellen. Noch etwas: "i.csv" wird wohl nicht funktionieren (auch nicht in AppleScript), du musst das Suffix von der Variable trennen, etwa:

i & ".csv"

Darauf achten, dass zwischen i und & und " jeweils Leerzeichen sind. Solche Kleinigkeiten bringen den VB-Editor bei der Übersetzung zum Meckern.

Eine nahezu perfekte Integration von AppleScript bietet das Programm "Tex-Edit Plus", falls du die Angelegenheit doch mit AppleScript lösen willst.

Am Ende steht man so oder so mit einer (vermutlich als *.csv exportierten) Datei da. Der Vorteil von Excel ist sicher, dass man einzelne Werte (Zellen) schnell und direkt mittels VB ansprechen kann.

 

[misterbecks]

Da die Selektion der Teile in VB funktioniert, kannst du eigentlich dabei bleiben. Die Abarbeitung der Schleife sollte da das geringste Problem darstellen. Noch etwas: "i.csv" wird wohl nicht funktionieren (auch nicht in AppleScript), du musst das Suffix von der Variable trennen, etwa:

i & ".csv"

Darauf achten, dass zwischen i und & und " jeweils Leerzeichen sind. Solche Kleinigkeiten bringen den VB-Editor bei der Übersetzung zum Meckern.

Das habe ich gemerkt. Hier kommt der Teil, der, wie Du schon sagst, Schwierigkeiten macht.

For i = 1 to 9
   Workbooks.Open FileName:=_
      "\\......\data\beispiel_000i.csv"
...

Wie kann ich das i an dieser Stelle einbauen, damit er die csv's von 0001 bis 0009 automatisch abfragt? Mit &? Etwas weiter tritt das Problem in ähnlicher Form nochmal auf:

...
Range("A1:A5").Select
...

Anstelle der 1 und der 5 sollte hier nun ein zweiter Index der Form j = (i*7)+1 laufen. Kann ich die Variable hier wie oben einfügen?

 

[Quassel]

Wie kann ich das i an dieser Stelle einbauen, damit er die csv's von 0001 bis 0009 automatisch abfragt? Mit &?

Auch.

For i = 1 to 9
   Workbooks.Open FileName:=_
      "\\......\data\beispiel_000" & i & ".csv"
...

Du setzt also " & i & " an der Stelle ein. Funktioniert das mit den Backslashs auf dem Mac? Ich habe das immer ohne "FileName:=" gemacht, einfach den Pfad in Klammern eingesetzt: ("Wo:auch:immer").
Ich habe noch eine zarte Erinnerung, dass irgendetwas bei der Mac-Version nicht implementiert wurde. Entweder Wildcards oder Variablen im Pfad/Dateinamen.

Etwas weiter tritt das Problem in ähnlicher Form nochmal auf:
Anstelle der 1 und der 5 sollte hier nun ein zweiter Index der Form j = (i*7)+1 laufen. Kann ich die Variable hier wie oben einfügen?

Ja. Du kannst j (oder was auch immer) entweder vorher definieren - oder die Rechenanweisung direkt einfügen. Ersteres ist übersichtlicher. Beispiel:

...
j = (i*7) +1
k = (i*7) + 6
Range("A" & j & ":A" & k).Select
...

Du musst auch darauf achten, dass das richtige Workbook bearbeitet wird. Das ".Select" kann man sich meist sparen und gleich den gewünschten Befehl benutzen (bspw. ".ClearContents"). Soll Inhalt aus Zelle A1 in A10 eingesetzt werden, reicht auch:

Range("A10") = Range("A1") 'Gleiches Blatt
Range("A10") = Sheets(1).Range("A1")  'Aus anderem Blatt
[a10] = [a1] 'Gleiches Blatt. Eckige Klammern immer für aktives Blatt
[a10] = Workbooks("Blubb").Sheets(1).Range("A1") 'Aus anderer Mappe

uswusf.


P.S.: Um die Geschwindigkeit zu erhöhen, kann man zu Beginn des Makros einige Dinge abstellen (wenn du es nicht brauchst, im Zweifelsfall ausprobieren - aber erst mit dem funktionierenden Makro) und am Ende wieder einschalten:

Sub blubb()
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlManual

...

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub

 

[misterbecks]

Du setzt also " & i & " an der Stelle ein. Funktioniert das mit den Backslashs auf dem Mac?

Ehrlich gesagt keine Ahnung. Excel habe ich nur auf einer Dose in der Arbeit. Aber danke auf alle Fälle. Eine Frage habe ich noch: Wie kommt es, dass Du schon über zwei Jahre hier registriert bist und erst jetzt deine ersten Beiträge schreibst?

 

[Quassel]

Wie kommt es, dass Du schon über zwei Jahre hier registriert bist und erst jetzt deine ersten Beiträge schreibst?

Ich gehe nicht sehr schnell aus mir heraus.

 

[misterbecks]

Ja. Du kannst j (oder was auch immer) entweder vorher definieren - oder die Rechenanweisung direkt einfügen. Ersteres ist übersichtlicher. Beispiel:

...
j = (i*7) +1
k = (i*7) + 6
Range("A" & j & ":A" & k).Select
...

Nun funktioniert "fast" alles. Beim Schließen der ganzen csv's (wieder über eine for...next Schleife) fragt er mich, ob die Änderungen speichern möchte, da die Dateien nicht das Excel-Format haben. Nun ist das natürlich bei 30 geöffneten Dateien nervig, kann man das deaktivieren? Auch stört die Frage, ob der Speicher der Zwischenablage freigegeben werden kann....

 

[Quassel]

Nun funktioniert "fast" alles. Beim Schließen der ganzen csv's (wieder über eine for...next Schleife) fragt er mich, ob die Änderungen speichern möchte, da die Dateien nicht das Excel-Format haben. Nun ist das natürlich bei 30 geöffneten Dateien nervig, kann man das deaktivieren? Auch stört die Frage, ob der Speicher der Zwischenablage freigegeben werden kann....

Wenn sich dieses "fast" nur noch auf die Warnmeldungen bezieht: Herzlichen Glückwunsch zum funktionierenden Makro!

OK, jetzt ist es an der Zeit, einige Programmfunktionen auf Eis zu legen:

Application.EnableEvents = False

Damit sollte Ruhe einkehren. Wie ich weiter oben schon schrieb, muss du diese Zeile zu Beginn des Makros einfügen und mit

Application.EnableEvents = True

am Ende die Events wieder einschalten.

Sollen während der Bearbeitung keine Berechnungen durchgeführt werden, kann mit

Application.Calculation = xlManual

die Abarbeitungsgeschwindigkeit tüchtig (Faktor 3-4 ist allemal drin) erhöht werden (s.o.). Zur Not immer nur einschalten, wenn es wirklich nötig ist.
Eine schöne Statusmeldung erhält man, wenn im Makro an strategischen Stellen (bspw.)

Application.StatusBar = "Bearbeite Datum " & x & " von " & y

einfügt. Sie erscheint dann unten in der Fussleiste. Am Ende mit

Application.StatusBar = False

ausschalten.


P.S.: Ich habe gerade ein Scharmützel mit AppleScript und AppleWorks hinter mir... Schande über Apple! Schande, Schande, Schande! Vodoo auf hohem Niveau! Ich sage nichts mehr gegen VB in Office.

 

[misterbecks]

Ich habe mal wieder ein Makro-Problem:

Ich möchte ein Excel-File so aufbauen, dass ein Benutzer in bestimmte (noch nicht gesperrte) Zellen etwas eintragen kann, danach ein Makro aufruft und damit die gerade geänderten Zellen sperrt, damit nachfolgende Benutzer in diese Zellen nichts mehr schreiben können. Der einzelne Benutzer soll natürlich keinen Zugriff auf das Schutz-Passwort bekommen.

Meine Idee war die:

Das Makro hebt den Schutz des Blattes auf, sucht alle Zellen ab (per Schleife) und setzt bei denen mit Inhalt den Haken auf Gesperrt. Danach wird der Blattschutz wieder aktiv. So, bis hierhin alles klar. SheetActive.unprotect nehme ich für den Schutz, aber wo übergebe ich dem Makro das Passwort? SheetActive.unprotect, Password="...." akzeptiert er nicht. Gleiches Spiel beim Aufsetzen des Schutzes....

Oder kann man das Problem auch komfortabler lösen?

 

[Jabba]

SheetActive.unprotect nehme ich für den Schutz, aber wo übergebe ich dem Makro das Passwort? SheetActive.unprotect, Password="...." akzeptiert er nicht. Gleiches Spiel beim Aufsetzen des Schutzes....

SheetActive? Du schreibst es zweimal falsch... liegt vielleicht darin der Hund begraben?

ActiveSheet.unprotect ("password")
geht problemlos.
Ob es komfortabler geht? K.A., aber es liest sich schon ganz gut. Hast Du ein Formular, was ausgefüllt werden soll? Oder sind es x-beliebige Felder?

Gespannt sei!
HTH

 

[Incoming1983]

Die Zusammenfassung der csv's muss nicht zwingend in Excel passieren. Das Endergebnis wird auch wahrscheinlich auch in anderen Programmen (R, etc.) weiterbearbeitet....

Wenn es mit Apple Script eine grundsätzlich einfacherer Methode geben würde, wäre ich dem nicht abgeneigt...

Für sowas bietet sich perl an. Damit gehen solche Dinge kinderleicht.
Das Ergebnis hättest du beispielsweise wieder als CSV.

 

[misterbecks]

SheetActive? Du schreibst es zweimal falsch... liegt vielleicht darin der Hund begraben?

ActiveSheet.unprotect ("password")
geht problemlos.
Ob es komfortabler geht? K.A., aber es liest sich schon ganz gut. Hast Du ein Formular, was ausgefüllt werden soll? Oder sind es x-beliebige Felder?

Gespannt sei!
HTH

Habe mich verschrieben, meine natürlich ActiveSheet.Unprotect.

D.h. richtig ist es so...

(...)
ActiveSheet.Unprotect ("test")
(...)
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="test"
(...)

...?

Für sowas bietet sich perl an. Damit gehen solche Dinge kinderleicht.

Das war zwar noch das alte (schon gelöste) Problem, aber Perl schaue ich mir mal an, danke.
Das Ergebnis hättest du beispielsweise wieder als CSV.

 

[Jabba]

@Mister
Ja, und? Geht doch? Oder nicht?

 

[misterbecks]

@Mister
Ja, und? Geht doch? Oder nicht?

Doch, anscheindend schon. Jetzt habe ich noch ein Problem mit den Schleifen:

Sub test()
    ActiveSheet.Unprotect ("test")
    For i = A To f
    If Not (i & "1") = 0 Then
    Range(i & "1").Locked = True
    End If
    Next i
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="test"
End Sub

Ist das richtig, um eine Zelle zu überprüfen, ob sie leer ist oder nicht? Und kann ich bei den for-Schleife auch mit Buchstaben arbeiten?

 

[Jabba]

Wie?
Willst Du in diesem Beispiel überprüfen, ob A1 bis F1 Werte enthält?
Das geht so gar nicht.
Mach doch einfach folgendes:

Sub test()
    Range("A1:F1").Select
    For Each i In Selection
        If i = 0 Then
            Else
            i.Locked = True
        End If
    Next
End Sub

Die Range kannst Du beliebig verändern, oder besser, vergiebst einen Namen.
Bei FOR kannst Du nicht mit einem boolean/Variant Wert arbeiten, da brauchst Du immer einen nummerischen Ausdruck.

 

[misterbecks]

Willst Du in diesem Beispiel überprüfen, ob A1 bis F1 Werte enthält?

Nein, ob die Zelle leer ist oder nicht. Den Inhalt habe ich mit der Gültigkeit schon begrenzt. Das jetzige Beispiel erzeugt mir einen Laufzeitfehler wegen der Typenunverträglichkeit.

 

[Jabba]

Muah!
Ist ja wohl das gleiche...

Mir ging es darum, ob Du den Bereich A1 bis F1 meinst
Mein Beispiel funktioniert einwandfrei!

 

[misterbecks]

Muah!
Ist ja wohl das gleiche...

Mir ging es darum, ob Du den Bereich A1 bis F1 meinst
Mein Beispiel funktioniert einwandfrei!

Ja, der Bereich ist richtig, aber darauf kommt es doch nicht an, oder?

Bei mir bricht das Beispiel mit einem Laufzeitfehler ab (siehe Screenshot).