• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Powershell / Excel set Data and import to Excel - Could anybody assist to complete?

Dear experts,

below you see the part of my code so set and import different custom attributes to variable company shorts
1. I want to write this informations to 4 columns called "CA4" for Cust4 (Column Q - 17), "CA8" for cust8 (18)
"CA10" for cust10 (19) and "CA14" for cust14 (20)
2. Can i use "Default" for all shortcut not explizit listed?

pls see also comments:

$comp = $actRow.Item(3).Value2
       $cust4, $cust10 , $cust14 = 'N', $comp.SubString(0,3)
   
    switch -wildcard ($comp)
    {
      'LST*'       { cust4 = 'F'; cust8 = 'LST'; cust10 = 'LST' }
      'GRIN*'   {                   cust8 = 'LS'; cust10 = 'LS' }
     default   {   cust4 = 'K'; cust8 = 'Z'; cust10 = 'LSBU' }       ;Can i use "default" for all other not listet?
   

  ; or should every default listet as below?


    }
    if ('CXC', 'DXC', 'EMC', 'KUH', 'DER', 'CVT', 'KCC', 'CCS', 'RTT', 'REQ', 'TZU', 'RZU', 'KPAUS' -notcontains $comp)
    {
      $cust10 = 'LF'
      $cust4 = 'D'
      $cust14 = 'DE'}
       
    }
    $actRow.Item(16).Value2 = $cust4
    $actRow.Item(17).Value2 = $cust10
    $actRow.Item(18).Value2 = $cust14
}

$WorkBook.Save()
$objExcel.Quit()

$comp = $actRow.Item(3).Value2
       $cust4, $cust10 , $cust14 = 'N', $comp.SubString(0,3)
   
    switch -wildcard ($comp)
    {
      'LST*'       { cust4 = 'F'; cust8 = 'LST'; cust10 = 'LST' }
      'GRIN*'   {                   cust8 = 'LS'; cust10 = 'LS' }
     default   {   cust4 = 'K'; cust8 = 'Z'; cust10 = 'LSBU' }       ;Can i use "default" for all other not listet?
   

  ; or should every default listet as below?


    }
    if ('CXC', 'DXC', 'EMC', 'KUH', 'DER', 'CVT', 'KCC', 'CCS', 'RTT', 'REQ', 'TZU', 'RZU', 'KPAUS' -notcontains $comp)
    {
      $cust10 = 'LF'
      $cust4 = 'D'
      $cust14 = 'DE'}
       
    }
    $actRow.Item(16).Value2 = $cust4
    $actRow.Item(17).Value2 = $cust10
    $actRow.Item(18).Value2 = $cust14
}

$WorkBook.Save()
$objExcel.Quit()

Open in new window

0
Mandy_
Asked:
Mandy_
  • 5
1 Solution
 
aikimarkCommented:
If you know the customer number, you can use an OFFSET property of the range, assuming that you have a range pointing to CA1.
0
 
Mandy_Author Commented:
I've requested that this question be deleted for the following reason:

I solved that by myself
0
 
Mandy_Author Commented:
I'm using Excel vlookup function from data sheet with all attributes
0
Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

 
Mandy_Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for Mandy_'s comment #a40319628

for the following reason:

I'm using Excel vlookup function from data sheet with all attributes
0
 
Mandy_Author Commented:
vlookup instead of complicate powershell coding
Mandy-vlookup.xlsx
0
 
Mandy_Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now