Excel (.xls file) - 4 sheets not possible?

▲
0
▼

need your help again!

This Script doesn't work. It works for the first 3 Sheets, but doesn't work for the last one. If I switch the itemnumber (eg. 3->4 and 4->3) the new 3 works and the new 4 does not. Is this some sort of bug? Or am I missing some commandlet to increase the "maximum sheet number"?

$Path     = "C:\test.xls"
#Excelvar:
    $Row                 = [int] 2
    $Excel               = New-Object -ComObject Excel.Application
    $Excel.Visible       = $true
    $Excel.DisplayAlerts = $false
                #Sheets:
                $ADUsers     = "Active Directory Users"
                $Groups      = "Create Groups"
                $UsertoGroup = "User to groups"
                $DNS         = "DNS"
 #$Worksheet = $Workbook.Sheets.Add()
    $checkxls = test-path -pathtype Any $Path
       if ($checkxls -eq $false) {  
            $wb = $Excel.Workbooks.Add()

            $ws1 = $wb.Worksheets.Item(1)
            $ws1.Name = $ADUsers
            $ws1.activate()
            $ws2 = $wb.Worksheets.Item(2)
            $ws2.Name = $Groups
            $ws2.activate()
            $ws3 = $wb.Worksheets.Item(3)
            $ws3.Name = $UserToGroup
            $ws3.activate()
            $ws4 = $wb.Worksheets.Item(4)
            $ws4.Name = $DNS
            $ws4.activate()

            $wb.SaveAs($Path)
            $wb.Close()
            $Excel.Quit()

Errorcode:

"Invalid Index. (Exception by HRESULT: 0x8002000B (DISP_E_BADINDEX))"

Thx for help in advance.

extra information: using powershell 3.0 using excel 2010

excel
powershell
asked on Stack Overflow Oct 31, 2013 by XXInvidiaXX • edited Oct 31, 2013 by XXInvidiaXX

2 Answers

▲
2
▼

I think it's because you're refering to a different workbook

this line

  $wb = $Excel.Workbooks.Add()

implies you're working with a new workbook.

try adding 

  $wb.Worksheets.Add()

after the workbook is created, and see if that works.

$Path     = "C:\test.xls"
#Excelvar:
$Row                 = [int] 2
$Excel               = New-Object -ComObject Excel.Application
$Excel.Visible       = $true
$Excel.DisplayAlerts = $false
            #Sheets:
            $ADUsers     = "Active Directory Users"
            $Groups      = "Create Groups"
            $UsertoGroup = "User to groups"
            $DNS         = "DNS"
 #$Worksheet = $Workbook.Sheets.Add()
 $checkxls = test-path -pathtype Any $Path
   if ($checkxls -eq $false) {  
        $wb = $Excel.Workbooks.Add()

             $wb.Worksheets.add()

        $ws1 = $wb.Worksheets.Item(1)
        $ws1.Name = $ADUsers
        $ws1.activate()
        $ws2 = $wb.Worksheets.Item(2)
        $ws2.Name = $Groups
        $ws2.activate()
        $ws3 = $wb.Worksheets.Item(3)
        $ws3.Name = $UserToGroup
        $ws3.activate()
        $ws4 = $wb.Worksheets.Item(4)
        $ws4.Name = $DNS
        $ws4.activate()

        $wb.SaveAs($Path)
        $wb.Close()
        $Excel.Quit()
answered on Stack Overflow Oct 31, 2013 by Sam • edited Oct 31, 2013 by Sam
▲
0
▼

Tried adding Sheet4 on excel and it is code is reading Sheet4 just fine

#Declare the file path and sheet name
$file = "C:\Documents\Folder\ExcelFile.xlsx" 
$sheetName = "Sheet1" 

#Create an instance of Excel.Application and Open Excel file
$objExcel = New-Object -ComObject Excel.Application
$workbook = $objExcel.Workbooks.Open($file) 
$sheetCount = $workbook.Worksheets.Count
$sheet = $workbook.Worksheets.Item($sheetName)
$sheet4 = $workbook.Worksheets.Item("Sheet4")
Write-Host $sheetCount #sheet count is 4
$objExcel.Visible=$false 

#Count max row
$rowMax = ($sheet.UsedRange.Rows).count 
#Declare the starting positions
$rowName,$colName = 1,1
$rowAge,$colAge = 1,2 
$rowCity,$colCity = 1,3 

#loop to get values and store it 
for ($i=1; $i -le $rowMax-1; $i++)
{ 
$name = $sheet.Cells.Item($rowName+$i,$colName).text 
$age = $sheet.Cells.Item($rowAge+$i,$colAge).text 
$city = $sheet.Cells.Item($rowCity+$i,$colCity).text 

Write-Host ("My Name is: "+$name)
Write-Host ("My Age is: "+$age)
Write-Host ("I live in: "+$city)
}

#used $rowMax from Sheet1, you can declare a separate for Sheet4
for ($i=1; $i -le $rowMax-1; $i++) 
{ 
$name = $sheet4.Cells.Item($rowName+$i,$colName).text 
$age = $sheet4.Cells.Item($rowAge+$i,$colAge).text 
$city = $sheet4.Cells.Item($rowCity+$i,$colCity).text 

Write-Host ("My Name is: "+$name)
Write-Host ("My Age is: "+$age)
Write-Host ("I live in: "+$city)
}
#close excel file
$objExcel.quit()

Pardon my example, just a noob script :)

answered on Stack Overflow Aug 4, 2014 by Mekalikot

User contributions licensed under CC BY-SA 3.0