Powershell Excel Spalte durchsuchen und Wert in andere Spalte kopieren

Hallo liebe Community,

schonmal vielen herzlichen Dank für die Hilfe.
Dies ist mein erster Beitrag hier, daher entschuldigt bitte schonmal jetzt Fehler.

Ich erstelle aktuell ein Powershell Skript zur automatisierten Bearbeitung von Excel Listen, damit diese in unsere Buchhaltungssoftware eingepflegt werden kann.

Ich möchte z.B. Spalte A durchsuchen, jeder Eintrag der nicht mit "30000" beginnt soll ausgeschnitten werden und in Spalte B kopiert werden.
Zudem soll in diesem Fall in Spalte C ein Wert z.B. 123 eingetragen werden.

Parallel müsste in Spalte D eine Ordernummer eingetragen werden, welche Anhand von dem Wert in nun Spalte B aus einer dritten Excel ausgelesen werden soll

Beispiel der Excel mit Order Nummer:
A1
Gesellschaft z.B. 40000

B1
Ordernummer z.B. 1234

Danke schonmal im voraus, es ist sicher nicht leicht zu durchblicken, was ich genau Vorhabe.
Bei Fragen immer gerne melden.

Beste Grüße,
Jonas

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 667314

Url: https://administrator.de/forum/powershell-excel-spalte-durchsuchen-und-wert-in-andere-spalte-kopieren-667314.html

Ausgedruckt am: 22.12.2024 um 15:12 Uhr

10 Kommentare

Neuester Kommentar

Hallo Jonas,

Zitat von @JJSS96:
Danke schonmal im voraus, es ist sicher nicht leicht zu durchblicken, was ich genau Vorhabe.
Bei Fragen immer gerne melden.

so ganz verstehe ich tatsächlich nicht, was du vorhast. Vielleicht formulierst du dein Ziel etwas genauer. Ein konkretes Beispiel einer Zeile vorher/nachher könnte auch helfen. Dein "Beispiel" heißt:
Wenn [Mappe1.xlsx]Tabelle1!$B$1 == 40000 dann setze [Mappe1.xlsx]Tabelle1!$D$1 = 1234? (Weil in Mappe2 die 40000 der 1234 entsprechend zugeordnet ist?)
Warum soll die Excel-Zell-Manipulation mit einem ps-Script gemacht werden? Wie sehen deine bisherigen Skript-Versuche aus?
Frag ggf. die Suchmaschine deines Vertrauens z.B. nach "powershell excel get/set cell value"
/pp

Zitat von @JJSS96:

Hallo liebe Community,

Tach lieber Jonas,

...
Ich erstelle aktuell ein Powershell Skript zur automatisierten Bearbeitung von Excel Listen, damit diese in unsere Buchhaltungssoftware eingepflegt werden kann.
...

Zeig mal

Beste Grüße,
Jonas

Gruß
C.C.

Mit Powershell habe ich gewisse Erfahrungen und finde mich einigermaßen gut zurecht. Alle Themen in Excel VBA Abzubilden fällt mir deutlich schwieriger.

Mhh ich habe leider noch keinen Ansatz zu der von mir gestellten Frage, da ich kaum passenden Stoff im Internet finde.

Theoretisch so:
1. Such in Spalte A, wenn Wert nicht mit 3000 beginnt, trag den Wert "123" in Spalte B ein.
2. Such in Spalte A, wenn Wert nicht mit 3000 beginnt, Schau in anderer Tabelle nach passendem Eintrag und kopiere diesen in Spalte D.
3. Such in Spalte A, wenn Wert nicht mit 3000 beginnt, schneide den Wert aus und trage diesen bei Spalte B ein.

Ich hoffe ich konnte es so bisschen besser erklären :D
Danke für deine Unterstützung.

Guten Tag C.C.
das wird wahrscheinlich nicht viel bringen, da in dem aktuell PS-Skript nur andere Themen behandelt werden.
Ich habe leider noch keinen Ansatz für die Lösung meiner Fragestellung.

Eventuell ist meine Frage in meiner vorherigen Antwort besser beschrieben.

Danke!

Kein Problem,
Lösungsansätze gibt es hier genug

Servus Jonas, willkommen auf Administrator.de!
Hier solltest du alles finden was du dazu brauchst (s. Kommentare)

# interop assembly laden
Add-Type -A Microsoft.Office.Interop.Excel

# Excel Objekt erstellen
$objExcel = New-Object -Com Excel.Application -Property @{
    # Instanz unsichtbar starten
    Visible = $false
    # Keine Dialoge zeigen
    DisplayAlerts = $false
}

# Zu bearbeitende Mappe öffnen
$wb = $objExcel.Workbooks.Open('D:\data\data.xlsx')  
# Mappe mit Orderdaten öffnen
$wbOrders = $objExcel.Workbooks.Open('D:data\orderbook.xlsx')  

# Sheet festlegen
$ws = $wb.Sheets.Item(1)

# belegten Bereich von "A2:A(n)" durchlaufen bei und nur Zellen zurückgeben die mit 30000 beginnen 
$rng = $ws.Range("A2:A" + $ws.Cells($ws.Rows.Count,"A").End([Microsoft.Office.Interop.Excel.XlDirection]::xlUp).Row) | ?{$_.Value() -notlike '30000*'}  
# für jede gefundene Zelle
$rng | %{
    # Zelle in Spalte B kopieren
    [void]$_.Copy($_.Offset(0,1))
    # festen Wert in Spalte C hinterlegen
    $_.Offset(0,2).Value = "123"  
    # Wert aus anderem Workbook ermitteln und in Spalte D schreiben
    $_.Offset(0,3).Value = $objExcel.WorksheetFunction.VLookup($_.Value(),$wbOrders.Sheets.Item(1).UsedRange,2,$false)
    # Wert in Spalte A löschen
    $_.Value = ""  
}

# Zu bearbeitendes Dokument schließen (mit speichern)
$wb.Close($true)
# Orderdaten-Dokument schließen (ohne speichern)
$wbOrders.Close($false)

# Excel schließen
$objExcel.DisplayAlerts = $true
$objExcel.Quit()
# Ressourcen freigeben
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($objExcel)

Grüße Uwe

Vielen Lieben Dank Uwe,

sowas habe ich gesucht :D
Ich baue es gleich mal ein.

Ich wünsche eine angenehme Woche!
Beste Grüße,
Jonas

Guten Tag,

ich hätte noch eine weitere Frage, habe es bei meiner Anfrage leider falsch formuliert :D

Bei diesem Befehl:

Wert aus anderem Workbook ermitteln und in Spalte D schreiben

$_.Offset(0,3).Value = $objExcel.WorksheetFunction.VLookup($_.Value(),$wbOrders.Sheets.Item(1).UsedRange,2,$false)

Soll er nur nach den ersten 4 Stellen suchen.
Beispiel:
Zelle: 2000546540

orderbook
A: werk: 2000 - B: ordernumber: 1234

In diesem Fall sollte er nach der 2000 im orderbook suchen und den Wert 1234 in Spalte D eintragen.

Bis auf den Fehler meinerseits funktioniert das Skript zu 100%, Sie sind ein Genie :D

Besten Dank nochmal!

Sie sind ein Genie :D

Nee, ich kenne mich halt mit VBA für Ofice-Anwendungen gut aus, was mit der Arbeit über das COM-Object Grundvoraussetzung für der Umsetzung in der Powershell ist, ist dann quasi fast das gleiche, bis auf ein paar Besonderheiten

# interop assembly laden
Add-Type -A Microsoft.Office.Interop.Excel

# Excel Objekt erstellen
$objExcel = New-Object -Com Excel.Application -Property @{
    Visible = $false
    DisplayAlerts = $false
}

# Zu bearbeitende Mappe öffnen
$wb = $objExcel.Workbooks.Open('D:\data\data.xlsx')  
# Mappe mit Orderdaten öffnen
$wbOrders = $objExcel.Workbooks.Open('D:data\orderbook.xlsx')  

# Sheet festlegen
$ws = $wb.Sheets.Item(1)

# belegten Bereich von "A2:A(n)" durchlaufen bei und nur Zellen zurückgeben die mit 30000 beginnen 
$rng = $ws.Range("A2:A" + $ws.Cells($ws.Rows.Count,"A").End([Microsoft.Office.Interop.Excel.XlDirection]::xlUp).Row) | ?{$_.Value() -notlike '30000*'}  
# für jede gefundene Zelle
$rng | %{
    # Zelle in Spalte B kopieren
    [void]$_.Copy($_.Offset(0,1))
    # festen Wert in Spalte C hinterlegen
    $_.Offset(0,2).Value = "123"  
    # Wert aus anderem Workbook ermitteln und in Spalte D schreiben
    $f = $wbOrders.Sheets.Item(1).Range("A:A").Find("$($_.Value().toString().Substring(0,4))*")  
    if ($f){
        $_.Offset(0,3).Value = $f.Offset(0,1).Value()
    }
    # Wert in Spalte A löschen
    $_.Value = ""  
}

# Zu bearbeitendes Dokument schließen (mit speichern)
$wb.Close($true)
# Orderdaten-Dokument schließen (ohne speichern)
$wbOrders.Close($false)

# Excel schließen
$objExcel.DisplayAlerts = $true
$objExcel.Quit()
# Ressourcen freigeben
[void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($objExcel)

Grüße Uwe