Excel (.xls文件)-4张不可能？

Question

再需要你的帮助！

这个剧本不管用。它适用于前3张，但不适用于最后一张。如果我换个项目号(如。3->4和4->3)新3起作用，新4不起作用。这是什么虫子吗？还是我遗漏了一些命令来增加“最大页数”？

$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()

错误代码：

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

提前寻求帮助。

额外信息:使用powershell 3.0使用excel 2010

Answer 1

我想是因为你指的是另一本工作簿

这条线

  $wb = $Excel.Workbooks.Add()

意味着你在使用一本新的工作簿。

试着添加

  $wb.Worksheets.Add()

在创建工作簿之后，看看它是否有效。

$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()

Answer 2

尝试在excel上添加Sheet4，它的代码正在读取Sheet4。

#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()

请原谅我的例子，只是一个菜鸟脚本:)