asked on

Input text into excel using random generation to 10,000 cells

I am working on creating an excel workbook that creates 10 worksheets and randomly add's numbers to a group of 10,000 cells. I am stuck on getting the random part to each cell. Individually it is working, but as a group it is not. Any help would be greatly appreciated!

Here is the code I have so far:

$Excel01 = New-Object -ComObject Excel.Application
$Excel01.Visible = $True
$Excel01.SheetsInNewWorkbook = 10
$Workbook01 = $Excel01.Workbooks.Add()
$Worksheet01 = $Workbook01.Sheets.Item(1)
$Worksheet01.Cells.Item(1,1) = "This Works!"
$Worksheet01.Activate()

Open in new window

ASKER CERTIFIED SOLUTION

Steven Harris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tomtom9898

ASKER

Let me try yours real quick, but I think this would not do 10,000 cells of random data. If it helps, I want to Take the letters "PS" and add a random number to it between 1 and 10,000 so I have 10,000 different codes all Starting with PSXXXX

Steven Harris

In one sheet, or across all sheets? I may have been on the wrong track :(

Just for grins - This will loop through all sheets and then put an X in a specific range (A1:E10) for each sheet.

$Excel01 = New-Object -ComObject Excel.Application
$Excel01.Visible = $True
$Excel01.SheetsInNewWorkbook = 10
$Workbook01 = $Excel01.Workbooks.Add()

$Worksheets = $Workbook01.WorkSheets.Item($WorkSheetNum)
foreach($worksheet in $Workbook01.Worksheets){
for ($i = 1; $i -lt 11; $i++){
    $Worksheet.Cells.Item($i,1) = "X"
    $Worksheet.Cells.Item($i,2) = "X"
    $Worksheet.Cells.Item($i,3) = "X"
    $Worksheet.Cells.Item($i,4) = "X"
    $Worksheet.Cells.Item($i,5) = "X"}}

Open in new window

I am sure we can use the same logic to add the differential numbers as well.

c_kedar

Use Get-Random cmdlet to generate random number in PowerShell.

help Get-Random

NAME
    Get-Random
SYNOPSIS
    Gets a random number, or selects objects randomly from a collection.
SYNTAX
    Get-Random [-InputObject] <Object[]> [-Count <int>] [-SetSeed <int>] [<CommonParameters>]

Open in new window

Use them as you want.

It is not clear where you want to put random numbers in certain cells in each sheet or you want to put them in random cells spread across these sheets.

But once you have random numbers you can use them as per need.

Let me know if you need more help.

tomtom9898

ASKER

This worked, thanks so much for the help!