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

powershell shell/excel populating numbers

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
techdrive
Asked:
techdrive
  • 2
1 Solution
 
aikimarkCommented:
You should be able to format the numeric value with leading zeroes
0
 
techdriveAuthor Commented:
I do not understand what you are saying. Can you provide an example?
0
 
aikimarkCommented:
Test this:
$row.Cells.Item(1,2).value2 = $DEPT+ ([math]::floor($i++/$NUMBER)+1).ToString("000")

Open in new window

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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