Solved

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

Posted on 2014-09-11
8
194 Views
Last Modified: 2014-09-15
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
Comment
Question by:Mandy_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
8 Comments
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40318040
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
 
LVL 2

Author Comment

by:Mandy_
ID: 40319616
I've requested that this question be deleted for the following reason:

I solved that by myself
0
 
LVL 2

Author Comment

by:Mandy_
ID: 40319628
I'm using Excel vlookup function from data sheet with all attributes
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 2

Author Comment

by:Mandy_
ID: 40323551
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
 
LVL 2

Author Comment

by:Mandy_
ID: 40319649
vlookup instead of complicate powershell coding
Mandy-vlookup.xlsx
0
 
LVL 2

Author Closing Comment

by:Mandy_
ID: 40323552
thanks
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
Previously, on our Nano Server Deployment series, we've created a new nano server image and deployed it on a physical server in part 2. Now we will go through configuration.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question