Supportnet / Forum / Tabellenkalkulation

Liebes Forum, Ihr habt mir schon mehrmals sehr geholfen, daher hoffe ich auch bei der folgenden Frage auf einen Tip ;-) Meine Frage bezieht sich auf die Folgende Aufgabe: Gesucht wird ein VBA-Script, das einen "Öffnen-Dialog" öffnet, welches dem User ermöglicht, eine Datei auszuwählen, die nach entsprechender Auswahl automatisch weiterverarbeitet wird (genauer gesagt, Kommas und Tabstops in Spalten konvertieren und irgendwo in ein Sheet des Workbooks kopieren). Ist das irgendwie machbar? Über einen Lösungsansatz würde ich mich sehr freuen. Grüße von Chrisnelly

Hi Chrisnelly,

hier mal ein Lösungsvorschlag. Kopiere nachfolgenden Code in ein StandardModul.

Sub Daten_einlesen()
Dim dat$
dat = Application.GetOpenFilename
   Application.ScreenUpdating = False
    If Dir(dat) <> "" Then
    Workbooks.OpenText Filename:=dat
        Workbooks.OpenText Filename:= _
        dat, Origin:= _
        xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1))
    Else
    Exit Sub
    End If
 Dateiname = ActiveWorkbook.Name
Blattname = ActiveSheet.Name
Workbooks(Dateiname).Worksheets(Blattname).Range("A1:H100").Copy _
Workbooks("Mappe1.xls").Worksheets("Tabelle1").Range("A1")
ActiveWindow.Close (False)
End Sub

Der Code macht folgendes: Es wird das Öffnen-Fenster geöffnet. Die ausgewählte Datei wird über den Tabellenkonvertierungsassistenten geöffnet (Trennzeichen Tab, Komma). Danach wird der Bereich A1:H100 aus dieser Datei kopiert und in die Datei "Mappe1" in Blatt "Tabelle1" in Zelle A1 kopiert. Zum Schluß wird die durch die Anweisung geöffnete Datei wieder geschlossen.
Wenn ein anderer Bereich als A1:H100 kopiert werden soll, muss in Zeile

Workbooks(Dateiname).Worksheets(Blattname).Range("A1:H100").Copy _


der Bereich, der nach Range in den Klammern steht geändert werden. Da Deine Datei und auch Dein Blattname anders lauten wird als in diesem Beispiel, muss in Zeile

Workbooks("Mappe1.xls").Worksheets("Tabelle1").Range("A1")


der Dateiname "Mappe1.xls" (.xls bitte nicht vergessen) und der Blattname "Tabelle1" gegen Deine aktuellen Namen geändert werden. In dieser Zeile wird auch angegeben an welcher Stelle die Daten eingefügt werden sollen. Wenn also nicht in A1, dann die Bezeichnung ändern.

Da ich nicht weiß, ob Du Dich mit VBA (Visual Basic) auskennst, kommt hier eine kurze Anweisung, wie Du den Code in Deine Tabelle bekommst.

1. Markiere die Anweisung aus diesem Beitrag und Kopiere (Strg c) diesen.

2. Klicke in Deiner Exceldatei in der Menüleiste nacheinander auf Extras => Makro => Visual Basic Editor oder drücke die Tastenkombination Alt F11

3. In dem neu geöffneten Fenster suche auf der linken Seite nach dem Eintrag VBA Projekt(Hier der Name Deiner Tabelle) und klicke doppelt auf diesen Eintrag

4. Klicke danach nacheinander in der Menüleiste auf Einfügen => Modul.

5. Füge nun die vorher kopierte Anweisung in das rechte leere Fenster ein.

Aufrufen kann man das makro z.B. über eine Schaltfläche. Da ich auch nicht weiß, ob Du weißt, wie man eine Befehlsschaltfläche in ein Tabellenblatt einfügst, kommt auch hierzu eine kurze Anweisung.

1. Gehe in der Menüleiste auf Ansicht => Symbolleiste => Steuerelement-Toolbox

2. Klicke auf die 6.Schaltfläche von links (Schaltflächenkommentar: Befehlsschaltfläche)

3. Positioniere die Schaltfläche in Deinem Tabellenblatt und passe gegebenenfalls die Größe an.

4. Klicke in der Symbolleiste auf die 3.Schaltfläche von links (Schaltflächenkommentar: Code anzeigen).

5. In das geöffnete Fenster schreibst Du zwischen die Texte “Private Sub CommandButton1_Click()“ und “End Sub“ in die Leerzeile Call Makroname oder nur der Makroname.

6. Klicke in der Symbolleiste auf die erste Schaltfläche von links um den Entwurfmodus zu beenden (Schaltflächenkommentar: Entwurfmodus beenden).

Wenn Du alles richtig gemacht hast, sollte das Makro, nachdem Du die Schaltfläche betätigt hast, abgearbeitet werden.


Du solltest zum Abschluss noch die Sicherheitseinstellung, die beim Öffnen der Datei abgefragt wird, kontrollieren. Klicke dazu nacheinander in der Menüleiste wieder auf Extras => Makro und dann auf Sicherheit.... Gehe, falls nicht schon angezeigt, auf die Registerkarte Sicherheitsstufe und schaue dort nach, ob die Einstellung Mittel aktiviert ist. Wenn nicht, aktiviere diese Einstellung, da sonst der Code nicht ausgeführt werden kann. Beim nächsten Öffnen Deiner Datei kommt eine Abfrage, ob Makros aktiviert oder deaktiviert werden sollen. Klicke auf aktivieren und der Code wird ausgeführt.

Ich hoffe, dass Makro ist so geworden, wie Du es Dir vorgestellt hattest. Bei Fragen oder Änderungswünschen melde Dich noch mal.

MfG,
coros
_{Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du ein}
^{Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.}

Hallo coros,
ich fass es nicht. Super erklärt und funktioniert auch noch!
Das wird in Zukunft ein wirklich grooooßes Problem bei mir lösen. Ich danke Dir sehr dafür.
Hmm, erfüllte Wünsche kalben aber sehr schnell. So auch bei mir ;-)
Also hier noch eine ergänzende Frage:

Wenn das Script wie oben Kommas und Tabstops in Spalten konvertiert hat, könnte dann auch nach einer Prüfung nur ein bestimmter Teil dieser Daten kopiert werden?

Es geht um folgendes.
Die Textdateien, die geöffnet werden haben alle eines gemeinsam.
Sie besitzen irgendwo in Spalte 1 ein Keywort "BEGIN_DATA" und am Schluß "END_DATA".
Nun sollen aus den Spalten innerhalb dieser Grenzen jeweils nur die letzten drei Spalten kopiert werden.
Wäre auch das sogar noch möglich?

Ein Tipp dazu wäre der Oberknaller.
Danke Dir bzw. Euch schon jetzt ...
Chrisnelly

Hi Chrisnelly,

hier der geänderte Code. Ich gehe mal davon aus, dass Du die letzten 3 Zeilen aus Spalte A ab dem Wort END_DATA haben möchtest und nicht die letzten 3 Spalten, sprich dass es sich um einen Schreibfehler handelt.

Sub Daten_einlesen()
Dim dat$
dat = Application.GetOpenFilename
   Application.ScreenUpdating = False
    If Dir(dat) <> "" Then
    Workbooks.OpenText Filename:=dat
        Workbooks.OpenText Filename:= _
        dat, Origin:= _
        xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1))
    Else
    Exit Sub
    End If
 Dateiname = ActiveWorkbook.Name
Blattname = ActiveSheet.Name
GefundeneZeile = Workbooks(Dateiname).Worksheets(Blattname).Cells.Find("END_DATA").Row
Workbooks(Dateiname).Worksheets(Blattname). _
Range("A" & GefundeneZeile - 3 & ":A" & GefundeneZeile - 1).Copy _
Workbooks("Mappe1.xls").Worksheets("Tabelle1").Range("A1")
ActiveWindow.Close (False)
End Sub

Wie Du den kopierst usw. ist ja in dem ersten Beitrag ausführlich beschrieben.

Wenn es aber kein Schreibfehler in Deinem 2. Posting war, müsstest Du Dich noch mal melden und genauer den Aufbau der einzulesenden Datei beschreiben.

MfG,
coros
_{Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du ein}
^{Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.}

Hallo coros,
danke Dir für Deine Unterstützung,
Leider hab ich mich nicht vertippt :-(

Die Tabellen sehen nach der Konvertierung folgendermassen aus:

Es gibt zunächst einige Zeilen, in denen unbrauchbares Zeug steht - die Anzahl variiert stark.
Dann folgt z.B. in Zeile 10 (Spalte 1) das Wort "BEGIN_DATA"
und dann weiter unten z.B. in Zeile 300 (Spalte1) das Wort "END_DATA"

Die Werte dazwischen (also von oben nach unten gesehen) sind relevant, allerdings nur die Werte der drei letzten Spalten dieser Zeilen (also weiter rechts).

Ich hoffe, das ich es einigermassen verständlich erklären konnte
Vielen lieben Dank schon mal.

Chrisnelly

Hi Chrisnelly,

ist der Aufbau der Datei, die eingelesen wird, immer gleich? Also sind immer die selbe Anzahl an Spalten beschrieben? Wenn ja, dann schreib mal bitte, welche Spalten (Spaltenbezeichnung) ausgelesen werden sollen.

MfG,
coros
^{Jeder macht was er will, keiner macht was er soll, aber alle machen mit.}

Hallo coros,
die Anzahl der Spalten variert meistens zwischen 10 und 20, wobei leider immer nur die letzten drei von Bedeutung sind.
Das ist das Problem, oder?

Danke Dir für's Grübeln ...
Viele Grüße
Chrisnelly

Hallo, Ihr Spezialisten!

Helft mir doch auch mal, bitte!

Habe den Auftrag, sog. Lehrereinsatzstundenplanung einer Schule zu digitalisieren:

Folgende Parameter werden von mir vorgegeben:

1. Lehrername
2. Wöchentl. Soll-Stundenzahl der einzelnen Lehrer
3. Klassenname
4. Wöchentl. Soll-Stundenzahl der Klassen
5. Fächer
6. Wöchentl. Soll-Stundenzahl der Fächer

Es soll nun durch Eingabe der entsprechenden
aktuellen Planungszahlen die Ist-Stundenzahl aller
Punke automatisch angezeigt werden.

Bsp. (gebe ich manuell ein):

1. Lehrername: Bergmann
2. Wöchentl. Soll-Stundenzahl: 25
3. Klasse: 1a
4. Wöchentl. Soll-Stundenzahl der Klassen: 38
5. Fächer: Deutsch
6. Wöchentl. Soll-Stundenzahl des Faches: 4

Benötigt wird die ständige Übersicht über fehlende Ist-Lehrerstunden, Ist-Fächerstunden usw. Am Beispiel hier:

1. Lehrername: Bergmann
2. Wöchentl. Ist-Stundenzahl: 21!
3. Klassen: 1a
4. Wöchentl. Ist-Stundenzahl der Klasse: 34!
5. Fächer: Deutsch
6. Wöchentl. Ist-Stundenzahl der Fächer: 0!

Habe mit Excel experimentiert aber die Übersicht
geht völlig flöten. Muss wohl mit Visual Basic
programmieren? Oder hat jemand eine andere Lösung?
Hoffentlich versteht mein Problem überhaupt jemand!

Liebe Grüße! Valentina!

So Chrisnelly,

hier nun der geänderte Code. Er sollte funktionieren. Bei Problemen oder Anderungswünschen melde Dich noch mal.

Sub Daten_einlesen()
Dim dat$, LetzteSpalte As Integer, Bereich, BereichAnfang, BereichEnde
dat = Application.GetOpenFilename
   Application.ScreenUpdating = False
    If Dir(dat) <> "" Then
    Workbooks.OpenText Filename:=dat
        Workbooks.OpenText Filename:= _
        dat, Origin:= _
        xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1))
    Else
    Exit Sub
    End If
 Dateiname = ActiveWorkbook.Name
Blattname = ActiveSheet.Name
LetzteSpalte = Workbooks(Dateiname).Worksheets(Blattname). _
Cells(1, Columns.Count).End(xlToLeft).Column

GefundeneZeileOben = Workbooks(Dateiname).Worksheets(Blattname). _
Cells.Find("BEGIN_DATA").Row
GefundeneZeileUnten = Workbooks(Dateiname).Worksheets(Blattname). _
Cells.Find("END_DATA").Row

BereichAnfang = Cells(GefundeneZeileOben, LetzteSpalte).Address
BereichEnde = Cells(GefundeneZeileUnten, LetzteSpalte).Address
Bereich = BereichAnfang & ":" & BereichEnde

Workbooks(Dateiname).Worksheets(Blattname). _
Range(Bereich).Copy _
Workbooks("Mappe1.xls").Worksheets("Tabelle1").Range("A1")
ActiveWindow.Close (False)
End Sub

An Valentina: Bitte benutze nicht einen Thread, in dem schon ein anderer User seine Frage gestellt hat, sondern eröffnen einen eigenen neuen Thread. Denn wenn Du in einem bereits bestehenden Thread Deine Fragen stellst, besteht die Möglichkeit, dass Du keine Antwort bekommst, weil keiner Deine Frage ließt.


MfG,
coros
_{Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du ein}
^{Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.}

Hallo coros,
ich habe das Script ersetzt und Mappe1.xls und Tabelle1 wie zuvor mit dem tatsächlichen Namen ersetzt.
Was passiert ist folgendes:

Es werden nur Zellen der ersten Spalte kopiert, die jedoch oberhalb des Bereiches zwischen "BEGIN_DATA" und "END_DATA" liegen.

Aber: Irgendwie funktioniert Dein Script mit einer kleinen Einschränkung trotzdem sehr gut. Ich ignorierte nähmlich, dass es weiter oben, außerhalb des zu kopierenden Bereiches die Worte "BEGIN_DATA_FORMAT" und "END_DATA_FORMAT" in der ersten Spalte gibt.
Genau dazwischen hat das Script korrekt kopiert, und zwar nur die eine Spalte, da hier nur eine Spalte existiert.

Also ist es offenbar entscheidend, dass es nur für die Wörter ohne "..._FORMAT" funktionieren soll.

Hast Du auch dafür eine Lösung parat?
Darf ich raten?
Na klar.

Ich freue mich auf Deine Antwort ...
Chrisnelly

Hi Chrisnelly,

irgendwie verstehe ich im Moment nicht so ganz Dein Problem. Bevor ich hier weiter im trüben rumfische, kannst Du mir mal solch eine Datei, die Du auslesen willst schicken? Dann kann ich mal an einer reelen Datei sehen wo das Problem liegt. Im Moment habe ich hier eine testdatei erstellt und da funktioniert das makro hervorragend.

Schick die Datei an meine E-Mail:coros@onlinehome.de

Beschreib in der Mail noch mal kurz, welcher Bereich dann ausgelesen weren soll.


MfG,
coros
^{Jeder macht was er will, keiner macht was er soll, aber alle machen mit.}

Hi Chrisnelly,

Du hast Post. Schau mal in Deinem Postfach nach.

MfG,
coros
^{Jeder macht was er will, keiner macht was er soll, aber alle machen mit.}

• Excel Dialog aktualisieren
• Nerviges Problem beim Öffnen der Festplatte
• Auswahl-Box (VBA)