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 Powershell Excel mehrere Tabellenspalten finden

Mitglied: Marabunta

Marabunta (Level 1) - Jetzt verbinden

06.11.2014, aktualisiert 11.11.2014, 1862 Aufrufe, 7 Kommentare

Hallo,

ich habe eine Funktion geschrieben die mir die Zeile und Spalte einer Seriennummer findet.
Zusätzlich sollen aber noch weitere Zeilen und Spaltennummern in Variablen gespeichert werden. (Also z.B. von "IP-Adresse", "MAC-Adresse")
Mit einer anderen Funktion werden dann die Werte jeweils aktualisiert. Wie bekomme ich es hin, dass ich alles abspeichere und später verwenden kann ohne das es gleich wieder überschrieben wird?

Function WhoAmI{
$Global:Range = $Worksheet.Range("A1:ZZ50000").EntireColumn
$Global:Search = $Range.find($Seriennummer)
$Global:Zeile=$Search.row
$Global:Spalte=$Search.Column

if($Seriennummer -ne $null)
{
if($Zeile -ne $null)
{
if($Spalte -ne $null)
{
Write-Host "Suche : $Seriennummer"
Write-Host "Zeile : $Zeile"
Write-Host "Spalte: $Spalte"
UpdateZelle
}
else{
Write-Host "Spalte: $Spalte , ist NULL."
}
}
else{
Write-Host "Zeile: $Zeile , ist NULL."
}
}
else{
Write-Host "Seriennummer: $Seriennummer , ist NULL."
}

}
Mitglied: colinardo
06.11.2014, aktualisiert um 10:35 Uhr
Hallo Marabunta,
mach mal etwas deutlicher was du willst. Ich habe das mal so interpretiert:
01.
function WhoAmi(){ 
02.
    #Seriennummer suchen 
03.
    $result = $Worksheet.UsedRange.Find($seriennummer) 
04.
    # wurde sie gefunden 
05.
    if ($result -ne $null){ 
06.
        # füge die Informationen als Objekt an das Script-Array $script:data an 
07.
        $script:data += New-Object PSObject -Property @{"Seriennummer"=$seriennummer;"Row"=$result.Row;"Column"=$result.Column} 
08.
09.
10.
 
11.
# erstelle ein Array in dem die Infos gespeichert werden 
12.
$script:data = @() 
13.
# rufe die Funktion auf 
14.
WhoAmi 
15.
#gebe testweise das Objekt aus 
16.
$script:data
Zu Variablen-Scopes lese auch diese Seite:
http://technet.microsoft.com/de-de/library/hh847849.aspx

Grüße Uwe
Bitte warten ..
Mitglied: Marabunta
06.11.2014 um 16:32 Uhr
Alles in einem Array funktioniert nicht wie ich es mir vorstelle(meine ich).

Bsp.:
Seriennummer=A222

Tabelle:
A1 = "Seriennummer"
B1 = "IP-Adresse"
C1 = "MAC-Adresse"

    1. Seriennummern
    A2 = "C422"
    A3 = "A222"

    Das Skript muss jetzt die Spalte Seriennummer finden:
    "A"
    dann die Zeile seiner Seriennummer:
    "3"

    dann Spalte IP-Adresse:
    "B"
    und in seine Zeile dann die IP eintragen:
    "B3"

    dasselbe dann für die MAC-Adresse etc.

    Was ich bisher hinbekommen habe ist Seriennummer finden und "Spalte + 1" rechnen
    und nacheinander IP-Adresse und MAC-Adresse stupide eintragen.

    Wie bekomme ich also die Verbindungen hin die Spalte und Zeile in der ein Wert soll richtig zu bestimmen?
Bitte warten ..
Mitglied: colinardo
06.11.2014, aktualisiert um 17:05 Uhr
Alles in einem Array funktioniert nicht wie ich es mir vorstelle(meine ich).
wieso nicht ? geht alles wenn man weis wie

ganz einfach, in meinem Beispiel enthält $result den "Range" der gefundenen Zelle der Seriennummer. Jetzt kannst du mit $result.Offset(0,1) die jeweilige Zelle rechts daneben ansprechen (IP) oder mit $result.Offset(0,2) noch eine Zelle weiter für die MAC.

Ich würde schon gleich beim Suchen nach der Seriennummer die dazugehörigen Werte in die Tabelle schreiben, so entfällt das zwischenspeichern und du sparst wertvolle Scriptzeit, da ja die Powershell-Automation von Office Performancetechnisch sowieso nicht gerade berauschend ist.

Performancetechnisch noch besser wäre es den kompletten Range in einem Rutsch in ein Array zu laden, die Daten im Array zu suchen und zu verändern und dann das Array wieder in einem Rutsch zurückzuschreiben.
Wenn du möchtest, mache ich dir dazu morgen mal ein Beispiel.

Ich empfehle dir auch die Excel VBA-Referenz die Methoden und Eigenschaften sind dort nämlich fast exakt gleich anzuwenden wie in Powershell, da es sich ja um das selbe Objektmodell handelt. Hilft ungemein

Grüße Uwe
Bitte warten ..
Mitglied: Marabunta
06.11.2014 um 18:36 Uhr
Ich hab mit dem Com-Objekt von Excel jetzt das erste Mal zu tun.
Wenn du mir ein Beispiel zur Orientierung machen kannst, bringt mich bestimmt ein gutes Stück in Richtung Ziel.
Alles im Array zu speichern und darin zu verändert klingt spannend, habe ich bisher nie so gemacht bzw. kann ich bis dato nicht.
Bitte warten ..
Mitglied: colinardo
06.11.2014, aktualisiert 07.11.2014
Zitat von Marabunta:
Ich hab mit dem Com-Objekt von Excel jetzt das erste Mal zu tun.
Wenn du mir ein Beispiel zur Orientierung machen kannst, bringt mich bestimmt ein gutes Stück in Richtung Ziel.
Alles im Array zu speichern und darin zu verändert klingt spannend, habe ich bisher nie so gemacht bzw. kann ich bis dato
nicht.

OK, ich gehe jetzt einfach mal von folgendem Aufbau der Excel-Datei aus

ABC
1SeriennummerIPMAC
2123
3456
4789

Beide Codebeispiele machen das selbe, aber auf zwei unterschiedliche Arten. Kommentare findest du im Code.

Beispiel 1: Herkömmliche Art mit Range.Find
01.
# Excel COM-Ojekt erzeugen 
02.
$objExcel = New-Object -ComObject Excel.Application 
03.
$objExcel.Visible = $true 
04.
# Workbook öffnen 
05.
$wb = $objExcel.Workbooks.Open("C:\Mappe1.xlsx") 
06.
# Tabelle 1 wählen 
07.
$ws = $wb.Worksheets.Item(1) 
08.
# Range in dem nach der Seriennummer gesucht wird 
09.
$rngSearch = $ws.Range("A:A") 
10.
 
11.
# Objekt mit Beispieldaten erzeugen 
12.
$data = @() 
13.
$data += New-Object PSObject -Property @{"SN"="123";"IP"="192.168.1.20";"MAC"="44:44:44:44:44:44"} 
14.
$data += New-Object PSObject -Property @{"SN"="456";"IP"="192.168.1.100";"MAC"="55:55:55:55:55:55"} 
15.
$data += New-Object PSObject -Property @{"SN"="789";"IP"="192.168.1.200";"MAC"="66:66:66:66:66:66"} 
16.
# ------- 
17.
 
18.
# für jeden Datensatz 
19.
foreach($set in $data){ 
20.
    # suche Seriennummer 
21.
    $result = $rngSearch.Find($set.SN) 
22.
    # wurde sie gefunden schreibe die zugehörige IP und MAC in die Spalten daneben 
23.
    if ($result -ne $null){ 
24.
        $result.Offset(0,1).Value2 = $set.IP 
25.
        $result.Offset(0,2).Value2 = $set.MAC 
26.
27.
}
Beispiel 2: Zuordnung der Daten in einem Array in Powershell und zurückschreiben in die Tabelle als Array
01.
# Excel COM-Ojekt erzeugen 
02.
$objExcel = New-Object -ComObject Excel.Application 
03.
$objExcel.Visible = $true 
04.
# Workbook öffnen 
05.
$wb = $objExcel.Workbooks.Open("C:\Mappe1.xlsx") 
06.
# Tabelle 1 wählen 
07.
$ws = $wb.Worksheets.Item(1) 
08.
 
09.
# Alle Daten des Sheets in ein 2-Dimensionales Array laden 
10.
$array = $ws.UsedRange.Value2 
11.
 
12.
# Objekt mit Beispieldaten erzeugen 
13.
$data = @() 
14.
$data += New-Object PSObject -Property @{"SN"="123";"IP"="192.168.1.20";"MAC"="44:44:44:44:44:44"} 
15.
$data += New-Object PSObject -Property @{"SN"="456";"IP"="192.168.1.100";"MAC"="55:55:55:55:55:55"} 
16.
$data += New-Object PSObject -Property @{"SN"="789";"IP"="192.168.1.200";"MAC"="66:66:66:66:66:66"} 
17.
# ------- 
18.
 
19.
# für jeden Datensatz in den Beispieldaten ... 
20.
foreach($set in $data){ 
21.
    # filtere Array nach Seriennummer und schreibe zugehörige IP-Adresse und MAC Adresse an der richtigen Stelle in das Array 
22.
    2..($array.GetUpperBound(0)) | ?{$array[$_,1] -eq $set.SN} | %{$array[$_,2] = $set.IP; $array[$_,3] = $set.MAC } 
23.
24.
 
25.
#Schreibe geändertes Array wieder als ganzes zurück in die Tabelle 
26.
$ws.UsedRange.Value2 = $array
Hier noch einige Erläuterungen für den Code im zweiten Beispiel.

2..($array.GetUpperBound(0))Das macht nichts anderes als eine for-Schleife für alle Zeilen zu erzeugen. Die Überschriften lassen wir dabei aus
?{$array[$_,1] -eq $set.SN}Das ist die kurze Variante des Where-Object(?{}) und filtert die Zeile welche die Seriennummer in Spalte 1 enthält. Das $_ enthält die Zeilennummer.
%{$array[$_,2] = $set.IP; $array[$_,3] = $set.MAC }Zum Schluss wird mit der Abkürzung des Foreach-Objektes (%{}) in Spalte 2 die IP und Spalte 3 des Arrays die MAC Adresse eingetragen.

So, hoffe das gibt dir etwas Auftrieb in deinem Vorhaben.

Grüße Uwe
Bitte warten ..
Mitglied: Marabunta
07.11.2014 um 15:21 Uhr
beide Varianten scheitern an der selben Stelle wie mein Skript.
Die Spalte wird nicht richtig identifiziert sondern "nur" hochgezählt.
Wenn ich MAC in eine Spalte daneben setze wird es in die "FalscheSpalte" eingetragen.

Seriennummer ## IP FalscheSpalte MAC
123 192.168.1.20 44:44:44:44:44:44
456 192.168.1.100 55:55:55:55:55:55
789 192.168.1.200 66:66:66:66:66:66

Irgendwie muss es funktionieren die Spalte zum Inhalt zu identifizieren. =/
Bitte warten ..
Mitglied: colinardo
LÖSUNG 07.11.2014, aktualisiert 11.11.2014
Zitat von Marabunta:

beide Varianten scheitern an der selben Stelle wie mein Skript.
also, hier laufen beide einwandfrei, oder wir reden hier aneinander vorbei. Sind die Spalten für IP und MAC nicht immer an der selben stelle sondern immer in anderen Spalten zu finden, d.h. variabel ? Wäre von Vorteil für uns wenn du uns deinen genauen Tabellenaufbau mal näher bringst. Merci.
Dann musst du diese Spalten erst in den Überschriften suchen, nach dem gleichen Schema:
01.
$rngMac = $ws.Range("1:1").Find('MAC') 
02.
$rngIP = $ws.Range("1:1").Find('IP') 
03.
$colMAC = $rngMac.Column 
04.
$colIP = $rngIP.Column
Somit hast du die richtigen Spalten für deine Daten ermittelt.

D.h das erste Script sähe dann so aus:
01.
# Excel COM-Ojekt erzeugen 
02.
$objExcel = New-Object -ComObject Excel.Application 
03.
$objExcel.Visible = $true 
04.
# Workbook öffnen 
05.
$wb = $objExcel.Workbooks.Open("C:\Mappe1.xlsx") 
06.
# Tabelle 1 wählen 
07.
$ws = $wb.Worksheets.Item(1) 
08.
# Range in dem nach der Seriennummer gesucht wird 
09.
$rngSearch = $ws.Range("A:A") 
10.
 
11.
# Spaltennamen in der erste Zeile identifizieren 
12.
$rngMac = $ws.Range("1:1").Find('MAC') 
13.
$rngIP = $ws.Range("1:1").Find('IP') 
14.
$colMAC = $rngMac.Column 
15.
$colIP = $rngIP.Column 
16.
 
17.
# Objekt mit Beispieldaten erzeugen 
18.
$data = @() 
19.
$data += New-Object PSObject -Property @{"SN"="123";"IP"="192.168.1.20";"MAC"="44:44:44:44:44:44"} 
20.
$data += New-Object PSObject -Property @{"SN"="456";"IP"="192.168.1.100";"MAC"="55:55:55:55:55:55"} 
21.
$data += New-Object PSObject -Property @{"SN"="789";"IP"="192.168.1.200";"MAC"="66:66:66:66:66:66"} 
22.
# ------- 
23.
 
24.
# für jeden Datensatz 
25.
foreach($set in $data){ 
26.
    # suche Seriennummer 
27.
    $result = $rngSearch.Find($set.SN) 
28.
    # wurde sie gefunden schreibe die zugehörige IP und MAC in die Spalten daneben 
29.
    if ($result -ne $null){ 
30.
        $result.Offset(0,$colIP - 1).Value2 = $set.IP 
31.
        $result.Offset(0,$colMac -1).Value2 = $set.MAC 
32.
33.
}
Bitte warten ..
Ähnliche Inhalte
Batch & Shell

Powershell Skript Ändern - Mehrere Hotfix IDs finden

Frage von pixel0815Batch & Shell1 Kommentar

Moin Moin lässt sich folgendes Skript so erweitern das man mehrere Patch IDs suchen kann? Habe das Skript im ...

Batch & Shell

Powershell für excel

gelöst Frage von Crank69Batch & Shell9 Kommentare

Hallo ich benötige hilfe bei Powershell leider bin ich noch neu im Programmieren und würde mich freuen wenn ihr ...

Batch & Shell

Powershell excel

gelöst Frage von Crank69Batch & Shell6 Kommentare

Hallo Leute, ich habe in meinem Script leider 3 Probleme und hoffe das ihr mir helfen könnt. würde ich ...

Batch & Shell

PowerShell Wert in Array finden

gelöst Frage von derhoeppiBatch & Shell7 Kommentare

Hallo, in meinem Projekt lese ich einen Eintrag aus einer Combobox aus. Dieser wird in eine Variable / Objekt ...

Neue Wissensbeiträge
Windows 10

USB Maus und Tastatur versagen Dienst unter Windows 10

Erfahrungsbericht von hardykopff vor 7 StundenWindows 103 Kommentare

Da steht man ziemlich dumm da, wenn der PC sich wegen fehlender USB Tastatur und Maus nicht bedienen lässt. ...

Administrator.de Feedback
Update der Seite: Alles zentriert
Information von Frank vor 10 StundenAdministrator.de Feedback10 Kommentare

Hallo User, die größte Änderung von Release 5.8 ist das Zentrieren der Webseite (auf großen Bildschirmen) und ein "Welcome"-Teaser ...

Humor (lol)

WhatsApp-Nachrichten endlich auch per Bluetooth versendbar

Information von BassFishFox vor 1 TagHumor (lol)4 Kommentare

Genau darauf habe ich gewartet! ;-) Der beliebte Messaging-Dienst WhatsApp erhält eine praktische neue Funktion: Ab dem nächsten Update ...

Google Android

Googles "Android Enterprise Recommended" für Unternehmen

Information von kgborn vor 1 TagGoogle Android3 Kommentare

Hier eine Information, die für Administratoren und Verantwortliche in Unternehmen, die für die Beschaffung und das Rollout von Android-Geräten ...

Heiß diskutierte Inhalte
Server-Hardware
Welche Rolle spielt Design bei Enterprise IT Hardware?
Frage von ApolloXServer-Hardware17 Kommentare

Ich arbeite für einen internationalen Elektronikhersteller in der Forschung und meine Aufgabe ist es, Feedback von Nutzern in Hinsicht ...

Windows Netzwerk
WSUS4 und Windows 10 Updates automatisch installieren
Frage von sammy65Windows Netzwerk15 Kommentare

Hallo miteinander, ich habe mit einen neuen WSUS Server aufgesetzt Server 2016 darauf einen aktuellen WSUS. Grund, wir stellen ...

Speicherkarten
Vergessliche USB-Sticks?
Frage von hanheikSpeicherkarten14 Kommentare

Ich habe in den letzten Tagen 500 USB-Sticks mit Bilddateien bespielt. Obwohl ich die Dateien mit größter Sorgfalt kopiert ...

Switche und Hubs
Cisco SG350X-48 AdminIP in anderes VLAN
Frage von lcer00Switche und Hubs14 Kommentare

Hallo zusammen, ich habe ein Problem mir einem Cisco SG350X-48 bei der Erstinstallation wurde eine IP 192.168.0.254 (Default VLAN ...