How to Import Excel spreadsheet into PowerShell

In most of the cases, it will be more than sufficient to use very well-known PowerShell command – Import-Csv.

But… there is always a “but”… For every manipulation with every Excel (.XLS, .XLSX) spreadsheet, first you will have to export its content to .CSV. This is minimal requirement. After it, every manipulation with the original file, will cause you to export the file again and again. Finally, what would you do with the Unicode characters? While exporting to .CSV, these characters will be probably lost.

Bottom line, the function presented below – Import-Excel – will help you to load the native Excel spreadsheet directly to array of PowerShell objects. This function, loaded into PowerShell, could be used pretty straight-forward with the syntax described:

Import-Excel [-FileName]:Path [-WorksheetName:"Name"] [-DisplayProgress:$true|$false]
-FileName – path to the Excel file. Since the code uses Excel Com-object, any format supported by local installation of Excel is compatible
-WorksheetName – name of the spreadsheet to load. If not name declared, the first one will be loaded
-DisplayProgress – switch $true or $false to display or not the progress of the load process

The function returns array of objects, where each property name, represented by first row of the Excel spreadsheet.

Finally, the code with few examples right after:

function Import-Excel
{
  param (
    [string]$FileName,
    [string]$WorksheetName,
    [bool]$DisplayProgress = $true
  )

  if ($FileName -eq "") {
    throw "Please provide path to the Excel file"
    Exit
  }

  if (-not (Test-Path $FileName)) {
    throw "Path '$FileName' does not exist."
    exit
  }

  $FileName = Resolve-Path $FileName
  $excel = New-Object -com "Excel.Application"
  $excel.Visible = $false
  $workbook = $excel.workbooks.open($FileName)

  if (-not $WorksheetName) {
    Write-Warning "Defaulting to the first worksheet in workbook."
    $sheet = $workbook.ActiveSheet
  } else {
    $sheet = $workbook.Sheets.Item($WorksheetName)
  }
  
  if (-not $sheet)
  {
    throw "Unable to open worksheet $WorksheetName"
    exit
  }
  
  $sheetName = $sheet.Name
  $columns = $sheet.UsedRange.Columns.Count
  $lines = $sheet.UsedRange.Rows.Count
  
  Write-Warning "Worksheet $sheetName contains $columns columns and $lines lines of data"
  
  $fields = @()
  
  for ($column = 1; $column -le $columns; $column ++) {
    $fieldName = $sheet.Cells.Item.Invoke(1, $column).Value2
    if ($fieldName -eq $null) {
      $fieldName = "Column" + $column.ToString()
    }
    $fields += $fieldName
  }
  
  $line = 2
  
  
  for ($line = 2; $line -le $lines; $line ++) {
    $values = New-Object object[] $columns
    for ($column = 1; $column -le $columns; $column++) {
      $values[$column - 1] = $sheet.Cells.Item.Invoke($line, $column).Value2
    }  
  
    $row = New-Object psobject
    $fields | foreach-object -begin {$i = 0} -process {
      $row | Add-Member -MemberType noteproperty -Name $fields[$i] -Value $values[$i]; $i++
    }
    $row
    $percents = [math]::round((($line/$lines) * 100), 0)
    if ($DisplayProgress) {
      Write-Progress -Activity:"Importing from Excel file $FileName" -Status:"Imported $line of total $lines lines ($percents%)" -PercentComplete:$percents
    }
  }
  $workbook.Close()
  $excel.Quit()
}

Usage examples:

[PS] C:\>$reportLines = Import-Excel C:\Document\my-report.xls
– will import the content of the first spreadsheet from the file, found at “C:\Document\my-report.xls” into $reportLines variable

[PS] C:\>$users = Import-Excel C:\Document\users.xlsx -WorksheetName:"HQ"
– will import the content of the first “HQ” spreadsheet from the “C:\Document\users.xlsx” file into $users variable

I hope this will be helpful. Enjoy the code! Any comments are more than welcome.

Advertisements

30 thoughts on “How to Import Excel spreadsheet into PowerShell

  1. Like they say about Soccer, it is the beautiful game.
    Well your blogging, or at the very least, the page I just read is simply & kindly beauitful.

    1. Changed it to a module with the import complete sheet or read a line or column:

      Function Import-ExcelSheet {
      Param (
      [String]$FileName,
      [String]$WorksheetName,
      [Bool]$DisplayProgress = $true
      )

      If ($FileName -eq “”) {
      Throw “Please provide path to the Excel file”
      Exit
      }
      If (-not (Test-Path $FileName)) {
      throw “Path ‘$FileName’ does not exist.”
      exit
      }
      $FileName = Resolve-Path $FileName
      $Excel = New-Object -com “Excel.Application”
      $Excel.Visible = $false
      $WorkBook = $Excel.WorkBooks.open($FileName)
      If (-not $WorksheetName) {
      Write-Warning “Defaulting to the first worksheet in WorkBook.”
      $Sheet = $WorkBook.ActiveSheet
      }
      Else {
      $Sheet = $WorkBook.Sheets.Item($WorksheetName)
      }
      If (-not $Sheet) {
      Throw “Unable to open worksheet $WorksheetName”
      Exit
      }
      $SheetName = $Sheet.Name
      $Columns = $Sheet.UsedRange.Columns.Count
      $Lines = $Sheet.UsedRange.Rows.Count
      Write-Warning “Worksheet $sheetName contains $columns columns and $Lines lines of data”
      $Fields = @()
      For ($column = 1; $Column -le $columns; $Column ++) {
      $FieldName = $Sheet.Cells.Item.Invoke(1, $Column).Value2
      If ($fieldName -eq $null) {
      $FieldName = “Column” + $Column.ToString()
      }
      $Fields += $FieldName
      }
      $Line = 2
      For ($line = 2; $Line -le $Lines; $Line ++) {
      $Values = New-Object object[] $columns
      For ($column = 1; $Column -le $columns; $Column++) {
      $Values[$column – 1] = $Sheet.Cells.Item.Invoke($line, $Column).Value2
      }
      $Row = New-Object psobject
      $Fields | foreach-object -begin {$i = 0} -process {
      $Row | Add-Member -MemberType noteproperty -Name $Fields[$i] -Value $Values[$i]; $i++
      }
      $Row
      $Percents = [math]::round((($line/$lines) * 100), 0)
      If ($DisplayProgress) {
      Write-Progress -Activity:”Importing from Excel file $FileName” -Status:”Imported $Line of total $Lines lines ($percents%)” -PercentComplete:$Percents
      }
      }
      $percents
      $WorkBook.Close()
      $Excel.Quit()
      }
      Function Import-ExcelColumn {
      Param (
      [String]$FileName,
      [Int]$Column,
      [String]$WorksheetName,
      [Bool]$DisplayProgress = $true
      )

      If ($FileName -eq $Null) {
      Throw “Please provide path to the Excel file”
      Exit
      }
      If ($Column -eq $Null) {
      Throw “Please provide a Column number (A = 1, B = 2…)”
      Exit
      }
      If (-not (Test-Path $FileName)) {
      throw “Path ‘$FileName’ does not exist.”
      exit
      }
      $FileName = Resolve-Path $FileName
      $Excel = New-Object -com “Excel.Application”
      $Excel.Visible = $false
      $WorkBook = $Excel.WorkBooks.open($FileName)
      If (-not $WorksheetName) {
      Write-Warning “Defaulting to the first worksheet in WorkBook.”
      $Sheet = $WorkBook.ActiveSheet
      }
      Else {
      $Sheet = $WorkBook.Sheets.Item($WorksheetName)
      }
      If (-not $Sheet) {
      Throw “Unable to open worksheet $WorksheetName”
      Exit
      }
      $SheetName = $Sheet.Name
      $FieldName = $Sheet.Cells.Item.Invoke(1, $Column).Value2
      $Lines = $Sheet.UsedRange.Rows.Count
      Write-Warning “Worksheet $SheetName, the Column $FieldName contains $Lines lines of data”
      $Line = 2
      $Values = @()
      For ($line = 2; $Line -le $Lines; $Line ++) {
      $Values += $Sheet.Cells.Item.Invoke($line, $Column).Value2
      $Percents = [math]::round((($line/$lines) * 100), 0)
      If ($DisplayProgress) {
      Write-Progress -Activity:”Importing from Excel file $FileName” -Status:”Imported $Line of total $Lines lines ($percents%)” -PercentComplete:$Percents
      }
      }
      Return $Values
      $percents
      $WorkBook.Close()
      $Excel.Quit()
      }
      Function Import-ExcelLine {
      Param (
      [String]$FileName,
      [Int]$Line,
      [String]$WorksheetName,
      [Bool]$DisplayProgress = $true
      )

      If ($FileName -eq $Null) {
      Throw “Please provide path to the Excel file”
      Exit
      }
      If ($Line -eq $Null) {
      Throw “Please provide a Column number (A = 1, B = 2…)”
      Exit
      }
      If (-not (Test-Path $FileName)) {
      throw “Path ‘$FileName’ does not exist.”
      exit
      }
      $FileName = Resolve-Path $FileName
      $Excel = New-Object -com “Excel.Application”
      $Excel.Visible = $false
      $WorkBook = $Excel.WorkBooks.open($FileName)
      If (-not $WorksheetName) {
      Write-Warning “Defaulting to the first worksheet in WorkBook.”
      $Sheet = $WorkBook.ActiveSheet
      }
      Else {
      $Sheet = $WorkBook.Sheets.Item($WorksheetName)
      }
      If (-not $Sheet) {
      Throw “Unable to open worksheet $WorksheetName”
      Exit
      }
      $SheetName = $Sheet.Name
      $FieldName = $Sheet.Cells.Item.Invoke(1, $Column).Value2
      $Columns = $Sheet.UsedRange.Columns.Count
      Write-Warning “Worksheet $SheetName, the Row $Line contains $Columns Columns of data”
      $Values = @()
      For ($column = 1; $Column -le $columns; $Column++) {
      $Values += $Sheet.Cells.Item.Invoke($line, $Column).Value2
      $Percents = [math]::round((($Column/$Columns) * 100), 0)
      If ($DisplayProgress) {
      Write-Progress -Activity:”Importing from Excel file $FileName” -Status:”Imported $column of total $columns lines ($percents%)” -PercentComplete:$Percents
      }
      }
      Return $Values
      $percents
      $WorkBook.Close()
      $Excel.Quit()
      }


      1. Function Import-ExcelSheet {
        Param (
        [String]$FileName,
        [String]$WorksheetName,
        [Bool]$DisplayProgress = $true
        )

        If ($FileName -eq “”) {
        Throw “Please provide path to the Excel file”
        Exit
        }
        If (-not (Test-Path $FileName)) {
        throw “Path ‘$FileName’ does not exist.”
        exit
        }
        $FileName = Resolve-Path $FileName
        $Excel = New-Object -com “Excel.Application”
        $Excel.Visible = $false
        $WorkBook = $Excel.WorkBooks.open($FileName)
        If (-not $WorksheetName) {
        Write-Warning “Defaulting to the first worksheet in WorkBook.”
        $Sheet = $WorkBook.ActiveSheet
        }
        Else {
        $Sheet = $WorkBook.Sheets.Item($WorksheetName)
        }
        If (-not $Sheet) {
        Throw “Unable to open worksheet $WorksheetName”
        Exit
        }
        $SheetName = $Sheet.Name
        $Columns = $Sheet.UsedRange.Columns.Count
        $Lines = $Sheet.UsedRange.Rows.Count
        Write-Warning “Worksheet $sheetName contains $columns columns and $Lines lines of data”
        $Fields = @()
        For ($column = 1; $Column -le $columns; $Column ++) {
        $FieldName = $Sheet.Cells.Item.Invoke(1, $Column).Value2
        If ($fieldName -eq $null) {
        $FieldName = “Column” + $Column.ToString()
        }
        $Fields += $FieldName
        }
        $Line = 2
        For ($line = 2; $Line -le $Lines; $Line ++) {
        $Values = New-Object object[] $columns
        For ($column = 1; $Column -le $columns; $Column++) {
        $Values[$column – 1] = $Sheet.Cells.Item.Invoke($line, $Column).Value2
        }
        $Row = New-Object psobject
        $Fields | foreach-object -begin {$i = 0} -process {
        $Row | Add-Member -MemberType noteproperty -Name $Fields[$i] -Value $Values[$i]; $i++
        }
        $Row
        $Percents = [math]::round((($line/$lines) * 100), 0)
        If ($DisplayProgress) {
        Write-Progress -Activity:”Importing from Excel file $FileName” -Status:”Imported $Line of total $Lines lines ($percents%)” -PercentComplete:$Percents
        }
        }
        $percents
        $WorkBook.Close()
        $Excel.Quit()
        }
        Function Import-ExcelColumn {
        Param (
        [String]$FileName,
        [Int]$Column,
        [String]$WorksheetName,
        [Bool]$DisplayProgress = $true
        )

        If ($FileName -eq $Null) {
        Throw “Please provide path to the Excel file”
        Exit
        }
        If ($Column -eq $Null) {
        Throw “Please provide a Column number (A = 1, B = 2…)”
        Exit
        }
        If (-not (Test-Path $FileName)) {
        throw “Path ‘$FileName’ does not exist.”
        exit
        }
        $FileName = Resolve-Path $FileName
        $Excel = New-Object -com “Excel.Application”
        $Excel.Visible = $false
        $WorkBook = $Excel.WorkBooks.open($FileName)
        If (-not $WorksheetName) {
        Write-Warning “Defaulting to the first worksheet in WorkBook.”
        $Sheet = $WorkBook.ActiveSheet
        }
        Else {
        $Sheet = $WorkBook.Sheets.Item($WorksheetName)
        }
        If (-not $Sheet) {
        Throw “Unable to open worksheet $WorksheetName”
        Exit
        }
        $SheetName = $Sheet.Name
        $FieldName = $Sheet.Cells.Item.Invoke(1, $Column).Value2
        $Lines = $Sheet.UsedRange.Rows.Count
        Write-Warning “Worksheet $SheetName, the Column $FieldName contains $Lines lines of data”
        $Line = 2
        $Values = @()
        For ($line = 2; $Line -le $Lines; $Line ++) {
        $Values += $Sheet.Cells.Item.Invoke($line, $Column).Value2
        $Percents = [math]::round((($line/$lines) * 100), 0)
        If ($DisplayProgress) {
        Write-Progress -Activity:”Importing from Excel file $FileName” -Status:”Imported $Line of total $Lines lines ($percents%)” -PercentComplete:$Percents
        }
        }
        Return $Values
        $percents
        $WorkBook.Close()
        $Excel.Quit()
        }
        Function Import-ExcelLine {
        Param (
        [String]$FileName,
        [Int]$Line,
        [String]$WorksheetName,
        [Bool]$DisplayProgress = $true
        )

        If ($FileName -eq $Null) {
        Throw “Please provide path to the Excel file”
        Exit
        }
        If ($Line -eq $Null) {
        Throw “Please provide a Column number (A = 1, B = 2…)”
        Exit
        }
        If (-not (Test-Path $FileName)) {
        throw “Path ‘$FileName’ does not exist.”
        exit
        }
        $FileName = Resolve-Path $FileName
        $Excel = New-Object -com “Excel.Application”
        $Excel.Visible = $false
        $WorkBook = $Excel.WorkBooks.open($FileName)
        If (-not $WorksheetName) {
        Write-Warning “Defaulting to the first worksheet in WorkBook.”
        $Sheet = $WorkBook.ActiveSheet
        }
        Else {
        $Sheet = $WorkBook.Sheets.Item($WorksheetName)
        }
        If (-not $Sheet) {
        Throw “Unable to open worksheet $WorksheetName”
        Exit
        }
        $SheetName = $Sheet.Name
        $FieldName = $Sheet.Cells.Item.Invoke(1, $Column).Value2
        $Columns = $Sheet.UsedRange.Columns.Count
        Write-Warning “Worksheet $SheetName, the Row $Line contains $Columns Columns of data”
        $Values = @()
        For ($column = 1; $Column -le $columns; $Column++) {
        $Values += $Sheet.Cells.Item.Invoke($line, $Column).Value2
        $Percents = [math]::round((($Column/$Columns) * 100), 0)
        If ($DisplayProgress) {
        Write-Progress -Activity:”Importing from Excel file $FileName” -Status:”Imported $column of total $columns lines ($percents%)” -PercentComplete:$Percents
        }
        }
        Return $Values
        $percents
        $WorkBook.Close()
        $Excel.Quit()
        }

  2. Hi, I used your example for dynamically reading an excel spread sheet and it works nicely. But I do have a question on how to use the data that is stored in the $DataReader array. I know how to access it using dot notation but how do I access the data elements using an index? I would like to loop through the array use each of the data elements.

  3. I’m using this in conjunction with the AD cmdlets (piping it to New-ADUser, etc.), and it means I can have an entire AD setup in a single spreadsheet, with cells linking to each other, which makes management so much easier. Thanks very much 🙂

  4. Thanks, I’m using this to create Web Site Columns in SharePoint, will make it much easier to convince the client they need to convert their old document libraries to lists…

  5. So I have a CSV file that has 62K lines importing the data to the array takes almost 40 minutes. Is there a method or a process that i can use to make the process take 10 minutes?

  6. Hey, Thanks for your Code.
    I just found 2 thing that would improve the function, because i had some issues, that the Excel Process did not close and therefore the function did not return…
    I changed the last Lines to:

    #Closing Workbook with false (Don´t save changes)
    $workbook.Close($false)
    $excel.Quit()
    #Releasing COM Object
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

    Kind Regards
    Sven

    1. I would suggest to place the whole path into double-quotes (or to make sure the path does not include spaces). Should not be an issue in case the path is accessible.

      1. I found that I could not make this work when the Excel doc was located on a remote location. After a bit more testing it looks like it works as long as Excel is installed on that remote location. Might be the issue?

  7. I just came across this code and thinking about it before i get to use it, i realize that Excel needs to be installed for this to work, just like Paul B mentioned…

    1. I found a solution to the NumberFormat issue, this takes care of at-least, DateTime and Numbers:

      Below is the updated function:
      _____________________________
      Function Import-FromExcel
      {
      Param(
      [Parameter(Mandatory=$true)]
      [String]$WorkbookPath
      )

      # Constant values
      [int]$xlToRight = -4161; [int]$xlToLeft = -4159; [int]$xlUp = -4162; [int]$xlDown = -4121;

      If (($objExcel = New-Object -ComObject Excel.Application)) { Write-Verbose “Created Excel application object” }
      Else { Write-Host “Unable to create Excel object on this computer. Check if you have Excel installed.” -ForegroundColor Red; Return $Error[0].CategoryInfo.Category; }

      If(Test-Path -Path $WorkbookPath)
      {
      If(($objWorkbook = $objExcel.Workbooks.Open($WorkbookPath))) { Write-Verbose “Opened $WorkbookPath successfully” }
      Else { Write-Host “Unable to open $WorkbookPath.” -ForegroundColor Red; Return $Error[0].Exception.Message }
      }
      Else { Return “Workbook not found $WorkbookPath!” }

      $objSheet = $objWorkbook.Sheets.Item(1)
      $TotalColumnsAddress = ($objSheet.Cells(1,$objSheet.Columns.Count).EntireColumn.Address($false, $false)).ToString().Split(“:”)[0]
      $ColumnCount = $objSheet.Range(“$($TotalColumnsAddress)1”).End($xlToLeft).Column
      $RowCount = $objSheet.Range(“A$($objSheet.Rows.Count)”).End($xlUp).Row
      $LastColumnAddress = ($objSheet.Range(“$($TotalColumnsAddress)1”).End($xlToLeft).EntireColumn.Address($false,$false)).ToString().Split(“:”)[0]
      Write-Verbose “Total column address limit is $TotalColumnsAddress`n Whereas Last column in data is $LastColumnAddress”

      If ($ColumnCount -ge 1 -and $RowCount -ge 2) { Write-Verbose “Found $ColumnCount column and $RowCount rows of data in workbook.”}
      Else
      {
      Write-Host “No records found for processing. There should be at least one record/row apart from the header row in Workbook” -ForegroundColor Red
      Return “No records found in input workbook file”
      }

      Write-Verbose “Converting Excel data into an object.”
      $ColHeaders = @()
      $ColHeaders = $objSheet.Range(“A1:$($objSheet.Range(“$($TotalColumnsAddress)1″).End($xlToLeft).Address($false,$false))”).value2

      # Replace blank column headers with Column’n’
      Write-Verbose “Generating object headers from workbook”
      For($i=1; $i -le $ColHeaders.Count; $i++)
      {
      if($ColHeaders[1,$i] -eq “” -or $ColHeaders[1,$i] -eq $null)
      {
      Write-Verbose “Column $i was found empty hence assigning column name as Column$i”
      $ColHeaders[1,$i] = “Column$i”
      }
      }

      $objOutData = @()

      $DataRange = $objSheet.Range(“A2:$($LastColumnAddress)$RowCount”).Rows

      Write-Verbose “Started processing rows/records from workbook”
      For($curRow=2; $curRow -le $RowCount; $curRow++)
      {
      Write-Verbose “Working on row number $curRow.”
      $rowData = $DataRange.Rows | Where-Object { $_.Row -eq $curRow } | %{$_.Value2}
      # Blank object for properties
      $objRecord = New-Object -TypeName PSObject
      For($curColumn = 1; $curColumn -le $ColumnCount; $curColumn++)
      {
      # Adjusting the number format for column
      If($objSheet.Cells($curRow, $curColumn).NumberFormat -eq “General”)
      {
      $ValueData = $rowData[$curColumn-1]
      }
      Else
      {
      If(($objSheet.Cells($curRow, $curColumn).NumberFormat).ToString() -cmatch “d” -or
      ($objSheet.Cells($curRow, $curColumn).NumberFormat).ToString() -cmatch “M” -or
      ($objSheet.Cells($curRow, $curColumn).NumberFormat).ToString() -cmatch “yy”)
      {
      If(($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch “h:” -and
      ($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch “mm”)
      {
      Write-Verbose “Number format found: $($objSheet.Cells($curRow, $curColumn).NumberFormat)”
      $ValueData = [System.DateTime]::FromOADate($rowData[$curColumn-1]).ToString(‘ddd, dd-MMM-yyyy hh:mm:ss tt’)
      }
      Else
      {
      $ValueData = [System.DateTime]::FromOADate($rowData[$curColumn-1]).ToString(‘dd-MMM-yyyy’)
      }
      }
      ElseIf(($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch “h:” -or
      ($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch “mm” -or
      ($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch “ss”)
      {
      $ValueData = [System.DateTime]::FromOADate($rowData[$curColumn-1]).ToString(‘hh:mm:ss’)
      }
      ElseIf(($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch “0” -or
      ($objSheet.Cells($curRow, $curColumn).NumberFormat) -cmatch “0.0”)
      {
      $value = 0
      If([double]::TryParse($rowData[$curColumn-1], [ref]$value)) { $ValueData = $value }
      Else { $ValueData = $rowData[$curColumn-1] }
      }
      Else
      { $ValueData = $rowData[$curColumn-1] }
      }
      $objRecord | Add-Member -MemberType NoteProperty -Name $ColHeaders[1,$curColumn] -Value $ValueData -Force
      }
      $objOutData += $objRecord
      }

      # Quit Excel and release all resources.
      $objWorkbook.Close($false)
      $objExcel.Quit()
      Write-Verbose “Successfully processed and closed Excel application”

      # Supress errors temporarily
      $oldErrorActionPreference = $ErrorActionPreference
      $ErrorActionPreference = ‘SilentlyContinue’

      Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($RowCount) } while($comReleaser -gt -1) } Catch {}
      Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($ColumnCount) } while($comReleaser -gt -1) } Catch {}
      Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($DataRange) } while($comReleaser -gt -1) } Catch {}
      Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objSheet) } while($comReleaser -gt -1) } Catch {}
      Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objWorkbook) } while($comReleaser -gt -1) } Catch {}
      Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objExcel) } while($comReleaser -gt -1) } Catch {}
      Try { do { $comReleaser = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($objRecord) } while($comReleaser -gt -1) } Catch {}
      Write-Verbose “Released all the Com objects.”
      $ErrorActionPreference = $oldErrorActionPreference

      Return $objOutData
      }
      __________________________________________________

      1. Thank you very much for your extremely valuable input update and for your support! I have never encountered with the need to preserve formats, but this makes allot of sense!

  8. Hi Podisk – great code – Thanks for that!

    Have you successfully run this in a scheduled task? I am using Windows2008R2, my issue is that when I run it as a non interactive scheduled task calling import-excel seems to terminate the task with no errors.

    Thanks

    1. Thank you for your comment! Have not yet tried to run this on schedule as a non interactive task. I would assume this should not be an issue. Will give it a try.

  9. Hello, thanks a lot for that.
    I’d like to copy one row from my file A.xlsx and paste it to a new file : b.xlsx. Do you know how can I proceed ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s