Top-Themen

Aktuelle Themen (A bis Z)

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit

gelöst Excel nach bestimmten Schema durchlaufen und Daten kopieren

Mitglied: Pat.bat

Pat.bat (Level 1) - Jetzt verbinden

13.08.2019 um 10:45 Uhr, 194 Aufrufe, 15 Kommentare, 1 Danke

Hallo zusammen,

Ich habe eine Excel Datei, die wie ungefähr so aufgebaut ist:

excelauslesen - Klicke auf das Bild, um es zu vergrößern

Dabei stellt die Range zwischen Start und Ende immer einen Datensatz da, den ich so in ein neues Excel Dokument kopieren möchte.
Bei Start steht immer der Name der jeweiligen Einrichtung.
Daran soll erkannt werden, welche Datenblöcke mit ins neue Excel Dokument kopiert werden.

Diese rohe Excel Datei besteht aus einer "Liste" von Einrichtungsblöcken, wobei dich diese nun in jeweils eigene Dateien kopieren möchte.

Dabei ist die Datei so Aufgebaut, das am Start des Blocks der Name der Einrichung steht und am Ende des Blocks immer Die Summe der Kosten.
Danach folgen immer 7 Zeilen die unwichtig sind bevor der nächste Block startet.

Der Prozess wird aus einer VB.Net Application angestoßen. Wobei ich mir vorstellen kann, das die Verarbeitung der Daten in Excel mit VBA vorgenommen werden könnte (Einfacher als es in VB.Net zu schreiben???)

Unter anderem bin ich mir jetzt noch sicher wie man diese Aufgabe am besten angeht.

Ich könnte jetzt sagen (Pseudocode):

01.
Fange einen Loop ab Range B15 an (Start des ersten Blocks im Dokument) -> Schreibe den Text in eine Variable "NameEinrichtung"
02.
und Loope bis in Range B das Stichwort "Summe für" auftaucht und kopiere diesen Datensatz in ein neues Exceldokument bzw. eine neue Tabelle.
03.
Nimm dann Range wo Stichwort "Summe für" und addiere + 8 -> Start des neuen Block
04.
Wenn NameEinrichtung == B(Start des neuen Blocks) dann
05.
Loope Bis Range B = "Summe für" und kopiere Daten in Tabelle wo NameEinrichtung
06.
Else 
07.
NameEinrichtung = B (Start des neuen Blocks)
08.
erstelle Tablle NameEinrichtung
09.
Loope Bis Range B = "Summe für" und kopiere Daten in Tabelle wo NameEinrichtung
10.

11.
Wenn Ende der Excel erreicht ist, dann speicher alle Tabellen als eigene Excel Dateien ab.
Wäre das ein Weg den man gehen könnte, oder gibt es da effizientere Wege?

Kann man das genauso gut in VB.net machen oder macht sich sowas in VBA etwas komfortabler (obwohl sich beide ja ziemlich gleich sind)?

Weitere Infos: Die Excel Datei wird aus einem externen Programm heraus generiert. In meiner VB.Net Application würde ich diese generierte Datei auswählen und auf einen Button "Listen erstellen" drücken, der dann den Prozess ausführt.
Mitglied: coding
13.08.2019, aktualisiert um 12:44 Uhr
Wat für dich zum fertsch machen (Speicherung der erzeugten Mappen unter deinen gewünschten Dateinamen kannst du ja hoffentlich noch selbst ergänzen )
01.
Imports Microsoft.Office.Interop
02.
Public Class Form1
03.

04.
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
05.
        Dim xl As New Excel.Application, wb As Excel.Workbook, ws As Excel.Worksheet, result As Excel.Range, rngStart As Excel.Range, rngEnd As Excel.Range
06.
        xl.Visible = True
07.
        wb = xl.Workbooks.Open("D:\test.xlsx")
08.
        ws = wb.Sheets(1)
09.

10.
        With ws.Range("A15:A" & ws.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
11.
            result = .Find("Fallname", , Excel.XlFindLookIn.xlValues)
12.
            If Not result Is Nothing Then
13.
                Dim firstAddress As String = result.Address
14.

15.
                Do
16.
                    rngStart = result.Offset(-1, 0)
17.
                    rngEnd = result
18.
                    While Not rngEnd.Text.Contains("Summe")
19.
                        rngEnd = rngEnd.Offset(1, 0)
20.
                    End While
21.

22.
                    If Not rngEnd Is Nothing Then
23.
                        Dim newWB As Excel.Workbook = xl.Workbooks.Add()
24.
                        ws.Range(rngStart, rngEnd.Offset(0, ws.UsedRange.Columns.Count)).Copy()
25.
                        With newWB.Sheets(1).Range("A1")
26.
                            .PasteSpecial(Excel.XlPasteType.xlPasteColumnWidths)
27.
                            .PasteSpecial(Excel.XlPasteType.xlPasteAll)
28.
                        End With
29.

30.
                    End If
31.
                    result = .FindNext(result)
32.
                Loop While Not result Is Nothing And result.Address <> firstAddress
33.
            End If
34.
        End With
35.
    End Sub
36.
End Class
Bitte warten ..
Mitglied: Pat.bat
13.08.2019 um 15:22 Uhr
Wow super, das ist schonmal eine große Hilfe.

Da die Zelle B15 mehr enthält als nur den Namen der Einrichtung (unter anderem auch die Adresse), habe ich definition von result so abgeändert:

01.
result = .Find("ANKER Sozialarbeit Gemeinnützige GmbH", , Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart)
wobei der xlPart Teil eigentlich ja wie "Text.Contains" funktionieren sollte. Allerdings findet er bei mir nichts.

Die Zelle sieht unter anderem so aus:

01.
ANKER Sozialarbeit Gemeinnützige GmbH
02.
Muster Str. 77 b
03.
19000 Musterhausen
Funktioniert das dort anders mit xlPart oder warum findet er nicht den Eintrag in der Zelle?
Bitte warten ..
Mitglied: coding
13.08.2019, aktualisiert um 15:34 Uhr
Weil im Beispiel-Code oben nur Spalte A ab Zeile 15 - Ende durchsucht wird
Bitte warten ..
Mitglied: Pat.bat
13.08.2019 um 15:53 Uhr
Zitat von coding:

Weil im Beispiel-Code oben nur Spalte A ab Zeile 15 - Ende durchsucht wird

Sorry, hatte vergessen den Code darüber zu posten:

01.
            With ws.Range("B15:B" & ws.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
02.
                result = .Find("ANKER Sozialarbeit Gemeinnützige GmbH", , Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart)
So hab ich es stehen und er findet nichts.
Bitte warten ..
Mitglied: coding
13.08.2019, aktualisiert um 15:57 Uhr
Die Zelle ist verbunden oder passe mal den MatchCase an, kommt der Inhalt aus einer Formel?
Bitte warten ..
Mitglied: Pat.bat
13.08.2019, aktualisiert um 16:00 Uhr
Zitat von coding:

Die Zelle ist verbunden oder passe mal den MatchCase an, kommt der Inhalt aus einer Formel?

genau die Zelle ist verbunden und hat einen Textumbruch drin.

Der Inhalt steht as Value drin, also nicht per Formel
Bitte warten ..
Mitglied: coding
13.08.2019, aktualisiert um 16:11 Uhr
Zitat von Pat.bat:
genau die Zelle ist verbunden und hat einen Textumbruch drin.
Da hast du deinen Grund, darin findet der nichts wenn man den Suchbereich nicht auf alle Spalten der Verbundenen ausweitet. Deswegen habe ich nach der eindeutigen Spaltenbeschriftung "Fallnummer" gesucht und dann die Zelle eins nach oben geschoben , eine Adresse oder Name kann sich ja ändern.
Du kannst auch einen anderen festen Bezug nehmen und dann einen anderen Offset davon definieren.
Bitte warten ..
Mitglied: Pat.bat
13.08.2019, aktualisiert um 16:17 Uhr
Zitat von coding:

Zitat von Pat.bat:
genau die Zelle ist verbunden und hat einen Textumbruch drin.
Da hast du deinen Grund, darin findet der nichts wenn man den Suchbereich nicht auf alle Spalten der Verbundenen ausweitet. Deswegen habe ich nach der eindeutigen Spaltenbeschriftung "Fallnummer" gesucht und dann die Zelle eins nach oben geschoben , eine Adresse oder Name kann sich ja ändern.


Mit Fallname findet er trotzdem nichts.

Aber wenn ich nach Fallname suche und sage er soll eine Zelle hoch rutschen und ab da an bis Summe alles kopieren, woher weiss er dann den Einrichtungsnamen und kann damit eine neue Tabelle erstellen?

Zum Hintergrund:

Eine Einrichtung hat einen Namen, wie hier zb. "ANKER Sozialarbeit Gemeinnützige GmbH".
Eine Einrichtung kann aber mehrere Standorte haben. D.h. alle Blöcke mit dem Namen der Einrichtung unabhängig von der Adresse sollen in eine neue Tabelle kopiert werden.

Daher meine Überlegung, die Zelle B15 mit einer Art Contains zu überprüfen. Der Name der jeweiligen Einrichtung ist immer gleich, aber die Adresse kann unterschiedlich sein.

Nachtrag:

Achso jetzt weiß ich was du bezwecken wolltest. Er zieht sich also die Zelle über Fallname und nimmt diese als Anhaltspunkt weitere Blöcke zu finden, die mit dem selben Wert anfangen.

Nur gibt es wie gesagt, 2 Probleme. Auch mit Fallname findet er nichts und der Name ist gleich, nur die Adresse ist unterschiedlich.
Bitte warten ..
Mitglied: coding
13.08.2019, aktualisiert um 16:19 Uhr
Zitat von Pat.bat:
Mit Fallname findet er trotzdem nichts.
Dann ist die wohl auch verbunden. Funktioniert hier testweise einwandfrei, ohne Quelldaten vorliegen zu haben kann man ja leider nur raten.
Aber wenn ich nach Fallname suche und sage er soll eine Zelle hoch rutschen und ab da an bis Summe alles kopieren, woher weiss er dann den Einrichtungsnamen und kann damit eine neue Tabelle erstellen?
Mit entsprechendem Offset kein Problem
Wenn hier so viel mit Zellverbunden gearbeitet wird wirst du wohl doch auf eine Schleife ausweichen müssen außer du findest noch ein Merkmal jedes Datensatzes in dieser oder einer anderen Spalte anhand dessen man über einen Offset die Anfangszelle bestimmen kann.
Bitte warten ..
Mitglied: Pat.bat
13.08.2019 um 16:29 Uhr
Zitat von coding:

Zitat von Pat.bat:
Mit Fallname findet er trotzdem nichts.
Dann ist die wohl auch verbunden. Funktioniert hier testweise einwandfrei, ohne Quelldaten vorliegen zu haben kann man ja leider nur raten.
Aber wenn ich nach Fallname suche und sage er soll eine Zelle hoch rutschen und ab da an bis Summe alles kopieren, woher weiss er dann den Einrichtungsnamen und kann damit eine neue Tabelle erstellen?
Mit entsprechendem Offset kein Problem
Wenn hier so viel mit Zellverbunden gearbeitet wird wirst du wohl doch auf eine Schleife ausweichen müssen außer du findest noch ein Merkmal jedes Datensatzes in dieser oder einer anderen Spalte anhand dessen man über einen Offset die Anfangszelle bestimmen kann.

es gibt da noch Spalte N wo das Wort betrag steht. Die Zelle ist nicht verbunden, aber als Textumbruch eingestellt.

Dort habe ich den Code dann wie folgt abgeändert, aber scheint in eine Endlosschleife zu laufen:

01.
With ws.Range("N16:N" & ws.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
02.
                result = .Find("Betrag", , Excel.XlFindLookIn.xlValues)
03.
                If Not result Is Nothing Then
04.
                    Dim firstAddress As String = result.Address
05.

06.
                    Do
07.
                        rngStart = result.Offset(-1, -7)
08.
                        rngEnd = result
09.
                        While Not rngEnd.Text.Contains("Summe")
10.
                            rngEnd = rngEnd.Offset(1, 0)
11.
                        End While
Der Name der Einrichtung befindet sich in Zelle B15 (Verbunden und Textumbruch)
Bitte warten ..
Mitglied: coding
LÖSUNG 13.08.2019, aktualisiert um 17:05 Uhr
Geht so auch nicht.
Wie gesagt mach's mit einfacher Schleife über eine Spalte oder löse den Verbund der Zellen temporär im Code für die Suche. Ohne das Quell-Sheet leider immer noch Glaskugel.

01.
Dim xl As New Excel.Application, wb As Excel.Workbook, ws As Excel.Worksheet, rngStart As Excel.Range, rngEnd As Excel.Range
02.
Dim searchEnd As Boolean = False
03.
xl.Visible = True
04.
wb = xl.Workbooks.Open("D:\Testexport.xlsx")
05.
ws = wb.Sheets(1)
06.

07.
For Each cell As Excel.Range In ws.Range("B15:B" & ws.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
08.
    If searchEnd Then
09.
        If cell.Text.Contains("Summe") Then
10.
            searchEnd = False
11.
            rngEnd = cell
12.

13.
            Dim newWB As Excel.Workbook = xl.Workbooks.Add()
14.
            ws.Range(rngStart, rngEnd.Offset(0, ws.UsedRange.Columns.Count)).Copy()
15.
            With newWB.Sheets(1).Range("A1")
16.
                .PasteSpecial(Excel.XlPasteType.xlPasteColumnWidths)
17.
                .PasteSpecial(Excel.XlPasteType.xlPasteAll)
18.
            End With
19.
        End If
20.
    Else
21.
        If cell.Value = "Fallname" Then
22.
            searchEnd = True
23.
            rngStart = cell.Offset(-1, 0)
24.
        End If
25.
    End If 
26.
Next
Bitte warten ..
Mitglied: Pat.bat
13.08.2019 um 16:59 Uhr
Hallo,

ich habe die Datei mal soweit es geht anonymisiert

Testexcel.xlsx

Mit folgendem Stück code bekomme ich zumindest nun die richtigen Zellen:

01.
            With ws.Range("N15:N" & ws.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
02.
                result = .Find("Betrag", , Excel.XlFindLookIn.xlValues)
03.
                If Not result Is Nothing Then
04.
                    Dim firstAddress As String = result.Address
05.

06.
                    Do
07.
                        rngStart = result.Offset(-1, -12)
08.
                        MessageBox.Show(rngStart.Text.ToString)
09.
                        rngEnd = result
10.
                        While Not rngEnd.Text.Contains("Summe")
11.
                            rngEnd = rngEnd.Offset(1, -12)
12.
                            MessageBox.Show(rngEnd.Text.ToString)
13.
                        End While
Allerdings gibts eine Exception nach dem ersten While durchlauf:

exc - Klicke auf das Bild, um es zu vergrößern
Bitte warten ..
Mitglied: coding
13.08.2019, aktualisiert um 17:07 Uhr
Hatte den angepassten Code für die Sub nachträglich im letzten Kommentar gepostet, damit funktioniert es hier im Test mit deiner bereitgestellten Datei (Danke!).
Bitte warten ..
Mitglied: Pat.bat
14.08.2019 um 15:16 Uhr
Zitat von coding:

Hatte den angepassten Code für die Sub nachträglich im letzten Kommentar gepostet, damit funktioniert es hier im Test mit deiner bereitgestellten Datei (Danke!).

Danke, mit folgenden Änderungen funktioniert es nun perfekt in der VB App:

01.
Dim xl As New Excel.Application, wb As Excel.Workbook, ws As Excel.Worksheet, rngStart As Excel.Range, rngEnd As Excel.Range
02.
            Dim searchEnd As Boolean = False
03.
            xl.Visible = False
04.
            xl.DisplayAlerts = False
05.
            wb = xl.Workbooks.Open(Application.StartupPath & "\export.xlsx")
06.
            ws = wb.Sheets(1)
07.

08.
            For Each cell As Excel.Range In ws.Range("B15:B" & ws.UsedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
09.
                If searchEnd Then
10.
                    If cell.Text.Contains("Summe") Then
11.
                        searchEnd = False
12.
                        rngEnd = cell
13.

14.
                        Dim newWB As Excel.Workbook = xl.Workbooks.Add()
15.
                        ws.Range(rngStart, rngEnd.Offset(0, ws.UsedRange.Columns.Count)).Copy()
16.
                        With newWB.Sheets(1).Range("A1")
17.
                            .PasteSpecial(Excel.XlPasteType.xlPasteColumnWidths)
18.
                            .PasteSpecial(Excel.XlPasteType.xlPasteAll)
19.
                        End With
20.

21.
                        Dim FileName As String = cell.Text.ToString.Substring(10)
22.
                        FileName = FileName.Replace(Chr(34), "") ' Doppeltes Anführungszeichen aus Dateiname löschen, da dies nicht zulässig ist            
23.
                        Dim SavePath As String = Application.StartupPath & "\Zahllisten\" & FileName & ".xlsx"
24.
                        newWB.SaveAs(SavePath, FileFormat:=51) ' 51 = xlsx, 57 = PDF
25.
                        newWB.Close()
26.

27.
                    End If
28.
                Else
29.
                    If cell.Value = "Fallname" Then
30.
                        searchEnd = True
31.
                        rngStart = cell.Offset(-1, 0)
32.
                    End If
33.
                End If
34.
            Next
35.

36.
            xl.Quit()
Jetzt hab ich noch die Aufgabe bekommen, das ganze in PowerShell zu schreiben, damit dieses Stück Code auch als Teil eines Batches ausgeführt werden kann (Windows Aufgabenplanung). Puh, jetzt muss ich erstmal schauen wie man sowas in PowerShell macht.

Trotzdem vielen Dank für die super Hilfe hier
Bitte warten ..
Mitglied: coding
14.08.2019, aktualisiert um 15:20 Uhr
Joa, nützt nur keinem was wenn er nicht den gleichen Aufbau hat ...
wie man sowas in PowerShell macht.
Genau so .
In Powershell kannst du ebenfalls auf das COM-Objekt zugreifen, oder gleich den ganzen VB.Net Code über Add-Type ausführen.
Bitte warten ..
Ähnliche Inhalte
Microsoft Office
Word bestimmt Sprache selbst
gelöst Frage von honeybeeMicrosoft Office1 Kommentar

Hallo, habe in Word 2016 ein nerviges Problem: Auf meinem Computer (Windows 10) sind zwei Sprachen installiert: Deutsch und ...

CSS

CSS backgroundchangeFadeInOut im zweiten Durchlauf weniger Bilder

Frage von FalkITCSS4 Kommentare

Moin, nachdem ich heute morgen schon einen Thread (LINK) aufgemacht habe, in dem mir geraden wurde anstelle von javascript ...

Mac OS X

Safari öffnet bestimmt Seite nicht

Frage von tomi93Mac OS X2 Kommentare

Hallo , ich habe an einem iMac bei uns im Unternehmen das Problem das Safari dort eine bestimmte Seite ...

Microsoft

Über VPN nur bestimmt große Daten übertragen

gelöst Frage von Peter007Microsoft5 Kommentare

Hallo zusammen, gibt es eine Möglichkeit bei Außendienstmitarbeitern die über VPN angebunden sind, die übertragbare Datenmenge zum Fileserver über ...

Neue Wissensbeiträge
Humor (lol)
Zuviel Speicher ist ungesund. :-)
Tipp von Lochkartenstanzer vor 6 StundenHumor (lol)8 Kommentare

Moin Kollegen, Heute hatte ich ein ungewöhnliches Aha-Erlebnis: Über das Wochenende habe ich einen einen 6 Jahre alten Bare-Metal ...

Windows Update

KB4517297 verfügbar, behebt Fehler in VB6 VBA VBScript

Information von sabines vor 8 StundenWindows Update

Das Update behebt mögliche Fehler in VB6, VBA und VBScript, die durch das Update KB4512486 vom August entstanden sind. ...

Viren und Trojaner

Staatstrojaner soll auch per Einbruch installiert werden können

Information von transocean vor 1 TagViren und Trojaner3 Kommentare

Moin, Bundesinnenminister Horst Seehofer will dem Verfassungsschutz Wohnungseinbrüche erlauben, um den geplanten Staatstrojaner zu installieren. Gruß Uwe

Windows 7
Win7 Update scheitert KB4512506
Information von infowars vor 2 TagenWindows 7

Falls jemand auch das Problem hat mit dem: Monatliches Sicherheitsqualitätsrollup für Windows 7 für x64-basierte-Systeme (KB4512506) Das scheint mit ...

Heiß diskutierte Inhalte
Switche und Hubs
Glasfaser-Anschluss Telekom muss verteilt werden
Frage von cansoniSwitche und Hubs34 Kommentare

Vorweg: Bin nur Anwender und kein Experte Die Situation: Der Vermieter stellt einen Glasfaseranschluss in der Wohnung bereit. Wir ...

Hyper-V
Bei Neuaufbau auf Core-Server setzen?
gelöst Frage von dertowaHyper-V32 Kommentare

Hallo zusammen, ich habe vor einigen Monaten die Verantwortung für eine EDV-Landschaft übernommen die seit Jahren von einem Dienstleister ...

Hyper-V
VMs von Hyper-V auf externer Festplatte
gelöst Frage von SnowbirdHyper-V18 Kommentare

Hallo, ich möchte gerne von VirtualBox auf Hyper-V umsteigen und würde auch gerne weiterhin meine VMs auf der externen ...

Festplatten, SSD, Raid
SSDs durch Lagerung ohne Strom nach 6 Monaten defekt?!?
gelöst Frage von GlobetrotterFestplatten, SSD, Raid18 Kommentare

Moin Gemeinde Ich hatte gerade nen Trauerspiel Habe hier etliche NAS-Geräte herumfahren welche ich mal auf die Seite gelegt ...