Office: VB Excel Makro - Ausgabe in neues Tabellenblatt

Guten Tag Spezialisten,

ich muss aus datensätzen zufallszahlen aussuchen lassen.
die anzahl der auszuwählenden hängt davon ab wieviel datensätze angeliefert werden bzw. ob ein hohes oder niedriges risiko besteht


es sollten 3 sachen geändert werden:

1.das makro sollte als erstes ein pop-up generieren mit der frage ob es sich um ein hohes oder ein niedriges risiko handelt.(anstatt buttons)
das makro soll von jeder belibigen excel-liste aus gestartet werden können

2.die ausgewählten datensätze soll in eine neue tabelle kopiert werden.

3.es kann durchaus sein dass ein excel tabelle mit mehreren spalten angeliefert wird. dann sollten alle spalten pro datensatz berücksichtigt werden ( bei personendaten z.b. nicht nur der name sondern auch nachname, adresse etc.)

Code:

Option Explicit

Private Sub cmdUnwichtig_Click()
   prcCopySome False
End Sub

Private Sub cmdWichtig_Click()
   prcCopySome True
End Sub

Private Sub prcCopySome(ByVal bolWichtig As Boolean)
   Dim arListe As Variant  ' Array mit allen Werten aus Spalte A
   Dim lngZeilen As Long   ' Anzahl dieser Werte
   Dim lngKopieren As Long ' Anzahl zu kopierender Werte
   Dim i As Long           ' Zähler
   Dim lngZufall As Long   ' eine Zufallszahl zwischen 1 und lngZeilen
   
   With Worksheets("Tabelle3")
      lngZeilen = IIf(Len(.Cells(.Rows.Count, 1)), .Rows.Count, .Cells(.Rows.Count, 1).End(xlUp).Row)
      arListe = .Cells(1, 1).Resize(lngZeilen)
      .Cells(1, 2).Resize(lngZeilen).ClearContents ' Spalte B löschen
     
      Select Case lngZeilen
         Case Is <= 1: lngKopieren = 1
         Case Is <= 4: lngKopieren = 2
         Case Is <= 12: lngKopieren = IIf(bolWichtig, 3, 2)
         Case Is <= 52: lngKopieren = IIf(bolWichtig, 8, 5)
         Case Is <= 220: lngKopieren = IIf(bolWichtig, 25, 15)
         Case Else: lngKopieren = IIf(bolWichtig, 45, 25)
      End Select
     
      For i = 1 To lngKopieren
         lngZufall = Int((lngZeilen) * Rnd + 1)
         .Cells(i, 2).Value = arListe(lngZufall, 1)
         arListe(lngZufall, 1) = arListe(lngZeilen, 1)
         lngZeilen = lngZeilen - 1
      Next
   End With
End Sub

es wäre super wenn mir jemand helfen könnte.

vielen dank im voraus!

Clyde [/quote]

 

Hmmm... Und wie wird dann das Makro an sich ausgelöst? Bleibt neben einem Hotkey eigentlich auch wieder nur ein Button... ;-) Das heißt, Du drückst einen Button, um eine MessageBox zu öffnen, in der sich 2 Buttons befinden, von denen dann einer gedrückt werden muß. Und dummerweise: Die MessageBox kriegt man so pauschal erstmal nur mit Buttons wie "Ja", "Nein,", "Abbruch" usw. bestückt. Alles andere ist schon etwas reichlich mehr Aufwand.

Es kann nur von einer Excel-Mappe aus gestartet werden, in dem es sich auch bereits befindet...

Code:

2.die ausgewählten datensätze soll in eine neue tabelle kopiert werden.

Grob:

Code:

anzahl2=0
for y1=1 to anzahl1 'Anzahl der Zeilen in der Liste
    if (xxx) then 'Kriterium, ob kopiert werden soll
        anzahl2=anzahl2+1
        worksheets("sht1").rows(y1).copy destination:=worksheets("sht2").rows(anzahl2)
    endif
next y1

Je nach gusto mag ein

Code:

worksheets.add

mit umbenamsung per

Code:

worksheets(n).name="sht2"

oder auch nur ein

Code:

worksheets("sht2").cells.clear

davor gestellt werden.

Und wenn das nicht nur auf einem zusätzlichen Blatt der Mappe geschehen soll, wäre am Ende der Routine noch ein

Code:

worksheets("sht2").copy

möglich, um eine neue Mappe mit nur diesem Blatt zu erstellen.

Was berücksichtigt wird, ist im o.a. Beispiel Sache des "(xxx)" in der IF-Zeile. Der xxx-Teil kann dabei beliebig simpel oder kompliziert gestaltet werden. Aber was genau da passieren / abgefragt / berücksichtigt werden soll, weißt in erster Linie erstmal nur du. ;-)

Gruß, Michael

 

danke für deine schnelle antwort!

also es ist so dass ich eine excel-liste angeliefert bekomme.
dann die liste üffnen und dann das makro ausfürhen?
ist das nicht möglich?

eine neue mappe muss nicht erstellt werden, nur die betreffenden werte in die nächste tabelle kopiert werden.

also was du als XXX bezeichnst ist vorher immer unbekannt. gibt es nicht die möglichkeit immer alle kompletten datensätze zu kopieren?
mancmel besteht die ursprungs-excel liste nur aus personen mit vor- und nachnamen, manchmal mehr infos. in diesem fall sollten auch alle infos in die neue tabelle kopiert werden...

es wäre übrigens super wenn du die änderungen gleich im code berücksichtigst. ich bin nämlich wirklich ein anfänger und wüßte nichtmal wo ich was reinkopieren soll :-)

 

Dann gehe ich mal davon aus, daß Dein Makro ja nicht bereits in dieser Mappe enthalten ist. Folglich kann es aus dieser Mappe auch nicht gestartet werden. Dir bleibt dann nur eine Möglichkeit:

Mappe 1: "Deine" Mappe, in der sich das Makro befindet. Diese Mappe besteht im Wesentlichen aus einem Blatt, z.B. "Auswertung". Auf diesem Blatt befinden sich einige Parameter und ein Button. Mit den Parametern legst Du fest, wo sich die Datei befindet, die Du geliefert bekommst. Sinnvollerweise sind die Angaben aufgeteilt nach "Laufwerk", "Pfad", "Dateiname" und "Blattname".

Mappe 2: Die "gelieferte" Mappe mit den Daten, die ausgewertet werden sollen. Aus dieser Mappe werden alle oder nur ausgewählte Datensätze in die 1. Mappe übertragen.

Hier unterscheidet sich wieder "push" und "pull". Ich denke, Du stellst Dir die ganze Zeit "push" vor: Aus der einen Mappe werden per Makro die Datensätze in eine andere Mappe geschoben. Das geht, mangels Makro in der Quell-Datei, nicht. Also wirst du "pull" brauchen: Die eine Mappe zieht sich die Daten aus einer anderen Mappe heran, um sie dann weiter zu verarbeiten.

Also soll jede neu angelieferte Datei auf einem neuen Blatt landen? Wie oft kriegst Du die Datei denn?

Das "(xxx)" ist nur die Bedingung, ob ein Datensatz überhaupt kopiert werden soll. Sinnvollerweise kopiert man immer die ganze Zeile, egal, ob einzelne Felder gefüllt sind, oder nicht. Es ging mehr darum, ob ein Datensatz abhängig von einer bestimmten Bedingung überhaupt "in die Ziehung" kommt. Z.B. möchtest Du vielleicht nur alle Datensätze haben, in denen der Vorname mit "M" anfängt oder so...

Das erledigt sich von alleine, wenn grundsätzlich die gesamte Zeile kopiert wird. Felder, die leer sind, sind es nach dem Kopieren auch weiterhin.

Naja, einen kompletten Code werde ich Dir kaum präsentieren können. Es sind mehr so Code-Schnipsel, teils im Pseudo-Code, die mehr so den Weg zeigen sollen. Aber der grobe Ablauf, so wie ich es z.Z. verstehe, müßte sein:

Mappe 1, Blatt 1a "Auswertung", Button 1:
- Auslesen der Parameter für Laufwerk, Pfad, Datei- und Blattname
- Öffnen der Mappe 2, aktivieren des Blatts 2a
- Erstellen von Blatt 1b "Import"
- Schleife über alle Datensätze in Blatt 2a
- - Kopieren des Datensatzes in Blatt 1b
- Schließen von Mappe 2
- Ggf. Umbenennen von Blatt 1b z.B. mit Datums-Info

Das könnte, so aus'm Stegreif, vielleicht so funktionieren:

Code:

dim w1 as workbook
dim s1 as worksheet
dim w2 as workbook
dim s2 as worksheet
dim d as string
dim p as string
dim f as string
dim s as string
dim z as string
dim y1 as long
dim n2 as long
dim y2 as long

'Parameter auslesen
d=trim$(s1.range("impd").value)
p=trim$(s1.range("impp").value)
f=trim$(s1.range("impf").value)
s=trim$(s1.range("imps").value)
z="Import"

set w1=thisworkbook

'2. Mappe öffnen
workbooks.open d & f & f, , True
set w2=activeworkbook
worksheets(s).activate
set s2=activesheet

'Blatt für Import erstellen
w1.activate
worksheets.add
set s1=activesheet
s1.name = z
s1.move after:=worksheets(worksheets.count)

'alle Zeilen im Quell-Blatt durchlaufen
y1=0
n2=s2.specialcells(xllastcell).row
for y2=1 to n2
    'if true then
        y1=y1+1
        s2.rows(y2).copy destination:=s1.rows(y1)
    'endif
next y2

'2. Mappe wieder schließen
w2.close savechanges:=false
w1.activate
s1.activate

'Hier ggf. Import-Blatt umbenennen
's1.name=...

msgbox "fertig"

Alles jetzt "highly untested", aber so in der Art könnte es im Click-Ereignis des Buttons aussehen.

Wichtig noch: Die Felder mit den Parametern müssen Namen erhalten. Im Source werden die Parameter aus benamsten Feldern (z.B. "impd" für "Import Drive"), nicht direkt aus den Zellbezügen (z.B. "B2"), ausgelesen. Ich würde etwas in der Art auf dem Blatt "Auswertung" vorschlagen:
A1: "Import"
A2: "Laufwerk:" - B2: "C:" (impd)
A3: "Pfad:" - B3: "\Import\" (impp)
A4: "Datei:" - B4: "Neue_Daten.xls" (impf)
A5: "Blatt" - "B5: "Datenbank" (imps)
Formatierung:
Spalte A Fettschrift
Zeile 1 Fettschrift
Zeile 1 Schriftgröße 18 (Rest auf 12)
Zelle A1 unterstrichen
Spalte B Schriftfarbe "blau" (Rest standard "schwarz")
Jetzt kannst du den Button (Schriftgröße 10) prima in Zelle B1 platzieren. Die Zellen B2 bis B5 kriegen über "Einfügen - Name - Definieren..." die entsprechenden Namen verpaßt.

In dem ganzen Kram da oben sind jetzt auch Null Sicherheits-Abfragen. Wenn etwas schief geht, resultiert das unweigerlich in einem Laufzeit-Fehler. Z.B. wenn man das 2. Mal das Makro ausführt, fährt es gegen die Wand, weil das Blatt "Import" dann schon existiert und nicht erneut angelegt werden kann (vorher umbenennen oder löschen). Existiert die Import-Datei nicht, kracht's ebenfalls. Und etliche kleinere Fehler-Situationen können ebenfalls eintreten. Aber das wäre dann Finetuning.

Gruß, Michael

 

Hallo Michael!

Danke für deine Hilfe.

Die Logik bzw den Aufbau hast du richtig verstanden! so sollte es im idealfall aussehen.
was ich bei deinen ausführungen aber nicht vertsanden habe ist die sache mit den parametern.
wird durch sie bestimmt wo die zu importierende datei liegt? wieso 5 parameter. (ich kenne nur so einen durchsuchen...-button wo man anschlißend den pfad auswählen kann)
wie kreiert man die?

zu deinen fragen:
Ich bekomme in unregelmässigen abständen listen mit verschieden vielen einträgen.
je nach anzahl der einträge (zeilen) sollen welche zufällig ausgewählt und in einer neuen liste ausgegeben werden.

ausserdem hängt die anzahl der auszuwählenden einträge davon ab ob die liste wichtig oder weniger wichtig ist. (hohes bzw. niedriges risiko)
dies kann/soll per command button ausgewählt werden.
die regeln dafür sind so:


anzahl der einträge-> weniger wichtig -> wichtig
0-1 einträge -> wenn weniger wichitg 1 auswählen -> wenn wichtig 1 auswählen
2bis 4 -> 2 -> 2
bis 12 -> 2 -> 3
bis 52 -> 5 -> 8
bis 220 -> 15 -> 25
mehr als 220 -> 25 -> 45

ist eine liste z.b. wichtig und hat 77 einträge, soll das makro 25 zufällig erzeugte einträge auswählen und in einer neuen liste ausgeben.

die wichtigsten verbesserungspunkte bei meiner alten datei wären:
1.ausgabe in neues tabellenblatt (anstatt in zeile 2)
2.ausgabe der ganzen zeile (anstatt nur der 1.)

hier kannt du sie einsehen:
http://www.sendspace.com/file/ao4e5s


das mit dem import wäre das i-tüpfelchen, aber nicht sooooo dringend.
wenn die oberen 2 bedingungen funktionieren würden wäre ich schon glücklich :lol:

mir wäre wohl am meisten geholfen wenn du den code entsprechend abändern könntest, da ich fürs "zusammenbasteln" momentan eher zu wenig ahnung (und zeit) habe

 

jein... Die Parameter sagen dem Makro, wo die Datei liegt. Je nachdem, wo Du sie ablegst, müßtest du also die Parameter anpassen.

Sinnvollerweise legst du die Datei immer an der gleichen Stelle unter dem gleichen Namen ab, dann brauchst Du dort gar nichts mnehr ändern.

Weil die Lage der Daten durch die Summe dieser Einzel-Parameter genau definiert werden kann.

Zuerst das Laufwerk, was in den meisten Fällen irgendwas mit C: sein wird. Hat man Netzwerk-Laufwerke verbunden, kann es auch gerne mal ein N: oder so werden. Und arbeitet man über's Netzwerk ohne Laufwerksbuchstaben, wäre es der Servername mit freigegebener Resource: \\server\freigabe$

Dann kommt auf dem obigen Laufwerk der Pfad, in dem sich die Datei befindet. Im einfachsten Fall, wenn die Datei direkt im Stammverzeichnis (z.B. einer CD) liegt, ist das ein einfacher \ alleine. Ansonsten fängt der Pfad mit einem \ an, listet durch \ getrennt die einzelnen Verzeichnisse hintereinander auf und endet dann auch wieder mit einem \, so wie z.B. in \Dokumente und Einstellungen\Michael\Eigene Dateien\.

Der Dateiname bezeichnet die Datei selbst, so wie sie im Verzeichnis liegt. Das könnte also sowas wie Neue_Daten.xls umfassen. (Genau genommen sind das sogar noch 2 zusammengefaßte Teile, nämlich der eigentliche Dateiname und die Dateierweiterung, getrennt durch den letzten . in der Angabe).

Und da eine Excel-Tabelle ja aus mehreren Tabellenblättern bestehen kann, muß man dem Makro auch noch sagen, auf welchem Blatt sich die Daten befinden, die es verarbeiten soll.

Dieser "Pfad" umfaßt meist die ersten 3 Teile schon zusammengefaßt, also Laufwerk, Pfad und Datei zusammen. Aber selbst dann würde immer noch die Angabe des Tabellenblatts fehlen.

Das ließe sich alles auch direkt im Sourcecode fest einstellen, aber ich finde eine Änderungsmöglichkeit für den User sinnvoller. Solange sich nichts ändert, muß da ja auch keiner dran rumdrehen.

nix Buttons, direkt die Werte zwischen den " in die Zellen tippen.

Ah, ok, jetzt macht ein Teil, den ich nur kurz überflogen hatte, aus dem Source ganz oben auch schon wieder fast Sinn.

Das mit dem "zufällig auswählen" ist aber nicht ganz so trivial, wie es auf den ersten Blick scheint. Da wäre nämlich auch darauf zu achten, daß keine Zeile doppelt ausgewählt wird. Das ist aber kein wirkliches Problem, da die 2. Mappe nach dem Import ja "ohne Speichern" geschlossen wird, kann man dort ja in der Zwischenzeit auch etwas herumpfuschen und importierte Datensätze markieren, damit die nicht doppelt genommen werden.

Ich kann's mir mal ansehen, aber in fremde Codes einarbeiten ist meist aufwendiger, als gleich alles neu aufzusetzen. Außerdem ist so'n Forum ja eher als "Hilfe zur Selbsthilfe" und weniger als "Auftragsabwicklung" gedacht. ;-) Aber ich guck zumindest mal rein...

Gruß, Michael

 

Versuch's mal mit dem Anhang, ob's so paßt...

Gruß, Michael

 

das ist absolute klasse!
vielen vielen dank!

das ist mir eine große hilfe!
evtl melde ich mich nochmal...