Solved

powershell shell/excel populating numbers

Posted on 2016-08-30
4
21 Views
Last Modified: 2016-09-20
I have some code below with excel that runs but need help editing to  populate certain info. For example this updates another column with information in sequential order This part "$DEPT+ ([math]::floor($i++/$NUMBER)+1)"

I would like to be able to hard code this as for example: "Miami" and let this increment from Miami001 to a certain number like Miami300. If its possible The last thing is if I want do is exclude certain numbers "which I know might be very hard" For example if I ran the script and it starts at Miami001 to Miami300 and want to prevent Miami29 or Miami57 would this be able to exclude. Please let me know if anyone can help, thanks

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null
$NUMBER = [Microsoft.VisualBasic.Interaction]::InputBox("Please enter a number per batch?")

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null
$DIR = [Microsoft.VisualBasic.Interaction]::InputBox("Please enter the following. The complete path, file and the extension which should be an CSV")

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null
$DEPT = [Microsoft.VisualBasic.Interaction]::InputBox("Please enter a unique name of the batches")


$excel = New-Object -ComObject excel.application
$excel.visible=$true
$wb = $excel.WorkBooks.open("$DIR")
$ws = $wb.Worksheets.Item(1)
$row = $ws.Range("2:2")
$i = 0
while ($row.Cells.Item(1,1).value2)
{
  $row.Cells.Item(1,2).value2 = $DEPT+ ([math]::floor($i++/$NUMBER)+1)
  $row = $row.Offset(1,0)
}
$wb.Save()
$excel.Quit()
Remove-Variable excel, wb, ws, rowcode]

Open in new window

0
Comment
Question by:techdrive
  • 2
4 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 41777921
You should be able to format the numeric value with leading zeroes
0
 

Author Comment

by:techdrive
ID: 41778883
I do not understand what you are saying. Can you provide an example?
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points (awarded by participants)
ID: 41778898
Test this:
$row.Cells.Item(1,2).value2 = $DEPT+ ([math]::floor($i++/$NUMBER)+1).ToString("000")

Open in new window

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Are you one of those front-line IT Service Desk staff fielding calls, replying to emails, all-the-while working to resolve end-user technological nightmares? I am! That's why I have put together this brief overview of tools and techniques I use in o…
This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now