Powershell will not save excel file

All replies

• 

  

  0

  Sign in to vote

  have you tried

  $objWorkbook = $Excel.workbooks.OpenText($file,437,1,1,1,$True,$True,$False,$False,$True,$False)

  $objWorkbook.SaveAs("c:\test.xls")
  $objWorkbook.Close ; [void]$excel.Quit()
  

  Wednesday, June 5, 2013 5:37 PM
• 

  

  0

  Sign in to vote

  That does not work. It prompts me to save changes to my $file.  I'm completely lost on this.....................

  In addition, after making the following changes:

  $date = Get-Date -Format "yyyyMMdd"

  $path = "c:\savexlsx_($date).xlsx"

  
  $objWorkbook.SaveAs("$path")
  $objWorkbook.Close ; [void]$excel.Quit()

   It generates the following error:

  You cannot call a method on a null-valued expression.
  At C:\WorkingScripts\WorldFolderSizeExcleReport.ps1:181 char:20
  + $objWorkbook.SaveAs <<<< ("$path")
      + CategoryInfo          : InvalidOperation: (SaveAs:String) [], RuntimeException
      + FullyQualifiedErrorId : InvokeMethodOnNull
   

  
  
  
  

  • Edited by Ted_D Wednesday, June 5, 2013 6:12 PM

  Wednesday, June 5, 2013 6:05 PM
• 

  

  0

  Sign in to vote

  It may help to describe exactly what happens when you try and save the Excel file in your original code.

  Does Excel prompt you when the save happens? Is there an error? What is the error?

  You can turn off the Excel GUI prompts with this:

  $Excel.DisplayAlerts=$False

  in the code, before the save command.

  ---

  Inspired by Carlsberg.

  Wednesday, June 5, 2013 6:23 PM
• 

  

  0

  Sign in to vote

  I am able to save as manually when I work directly in Excel.  BTW, this is MSOffice 2010 if it makes a difference.

  A also disabled the GUI prompts with no positive results.

  My apologies, I failed to mention the error is the same as my original code.

  In my original code, I get the same error:

  You cannot call a method on a null-valued expression.
  At C:\WorkingScripts\WorldFolderSizeExcleReport.ps1:181 char:20
  + $objWorkbook.SaveAs <<<< ("$path")
      + CategoryInfo          : InvalidOperation: (SaveAs:String) [], RuntimeException
      + FullyQualifiedErrorId : InvokeMethodOnNull

  Wednesday, June 5, 2013 6:35 PM
• 

  

  0

  Sign in to vote

  The error indicates that the variable $path does not have any value.

  Try hardcoding the value in: $objWorkbook.SaveAs("c:\whatever.xlsx")

  Does it work? Now test what value $path has:

  $path

  Does it have the expected value?

  ---

  Inspired by Carlsberg.

  Wednesday, June 5, 2013 6:42 PM
• 

  

  0

  Sign in to vote

  I don't see any $objWorkbook object in your original code? Anyway, did you try: $Excel.Workbooks.ActiveWorkbook.SaveAs("myfile.xls").

  And, maybe you have to set the additional FileFormat property, because it was formerly a txt file.

  wizend

  
  

  • Edited by Wizend Wednesday, June 5, 2013 6:52 PM

  Wednesday, June 5, 2013 6:43 PM
• 

  

  0

  Sign in to vote

  perhaps this would supress your prompt

  $excel.DisplayAlerts = $False
  $objWorkbook.SaveAs($path, 1)
  

  Wednesday, June 5, 2013 7:14 PM
• 

  

  0

  Sign in to vote

  None of the suggestions have proved fruitful.  I did notice the workbook is named foldersizeParsed.txt with the worksheet named foldersizeParsed and appears its not not really creating a new workbook and importing the data but simply opening the txt file in Excel..  I need to step back a bit a get a different perspective.  This is the final part of an automated report that is being requested and I would rather not do this manually every month if I dont have to. ;-)

  Perhaps I can call the used cells and copy them to a new workbook? 

  Thanks for all the suggestions so far,

  Ted

  Wednesday, June 5, 2013 7:31 PM
• 

  

  0

  Sign in to vote

  whats in your c:\foldersizeParsed.txt file

  i want to run a test

  Wednesday, June 5, 2013 7:38 PM
• 

  

  0

  Sign in to vote

  whats in your c:\foldersizeParsed.txt file

  i want to run a test

  The first line in the txt file is blank.
       1,125.31   2296  TOTAL: \\xxx004\WORLD
         868.82   2264  TOTAL: \\xxx004\WORLD
       5,076.28   2523  TOTAL: \\xxx005\WORLD
       3,315.13   2272  TOTAL: \\xxx005\WORLD
      32,538.19  37912  TOTAL: \\xxx006ACPS\WORLD
         931.75    896  TOTAL: \\xxx006ACPS\WORLD
       5,078.68   1718  TOTAL: \\xxx007\WORLD
       4,542.57   1370  TOTAL: \\xxx007\WORLD
       1,701.00    243  TOTAL: \\xxx008\WORLD
       1,165.67     88  TOTAL: \\xxx008\WORLD
       1,700.29    241  TOTAL: \\xxx008QA\WORLD
       1,239.45     96  TOTAL: \\xxx008QA\WORLD
       1,239.45     95  TOTAL: \\xxx008QA\WORLD
       1,241.10     95  TOTAL: \\xxx008QA\WORLD
      11,808.04   6383  TOTAL: \\xxx009\WORLD
       6,687.51   1259  TOTAL: \\xxx009\WORLD
       2,317.63    950  TOTAL: \\xxx012\WORLD
       2,177.49    829  TOTAL: \\xxx012\WORLD
      15,237.20   7944  TOTAL: \\xxx017\WORLD
      12,737.27   3579  TOTAL: \\xxx017\WORLD
      32,206.46  18119  TOTAL: \\xxx018\WORLD
      20,551.44   2174  TOTAL: \\xxx018\WORLD
       2,233.66    520  TOTAL: \\xxx018ACPS\WORLD
           9.92     45  TOTAL: \\xxx018ACPS\WORLD
         926.18    775  TOTAL: \\xxx020\WORLD
         926.19    775  TOTAL: \\xxx020\WORLD
         343.99     73  TOTAL: \\xxx021ACPS\WORLD
         182.85     57  TOTAL: \\xxx021ACPS\WORLD
       2,492.60    228  TOTAL: \\xxx022\WORLD
       2,493.04    228  TOTAL: \\xxx022\WORLD
      90,601.05  21312  TOTAL: \\xxx023\WORLD
       8,045.84   3941  TOTAL: \\xxx023\WORLD
      24,698.08   7790  TOTAL: \\xxx026\WORLD
      12,371.32   1376  TOTAL: \\xxx026\WORLD
         719.51   1357  TOTAL: \\xxx027\WORLD
         329.04    807  TOTAL: \\xxx027\WORLD
       2,780.45   2176  TOTAL: \\xxx029\WORLD
       2,711.07   2159  TOTAL: \\xxx029\WORLD
         573.78    958  TOTAL: \\xxx030\WORLD
         319.73    176  TOTAL: \\xxx030\WORLD
           6.86      3  TOTAL: \\xxx031\WORLD
           6.86      3  TOTAL: \\xxx031\WORLD
       9,663.02  11718  TOTAL: \\xxx032\WORLD
       2,076.17   8339  TOTAL: \\xxx032\WORLD
       5,105.82   4433  TOTAL: \\xxx033\WORLD
       4,694.59   4151  TOTAL: \\xxx033\WORLD
       5,249.89   4759  TOTAL: \\xxx038ACPS\WORLD
       1,006.18   4564  TOTAL: \\xxx038ACPS\WORLD
       2,450.82   3812  TOTAL: \\xxx042\WORLD
       1,159.08   1962  TOTAL: \\xxx042\WORLD
         182.65     43  TOTAL: \\xxx044\WORLD
         182.77     43  TOTAL: \\xxx044\WORLD
       5,941.58   1796  TOTAL: \\xxx050\WORLD
       5,918.36   1766  TOTAL: \\xxx050\WORLD
       2,068.96    890  TOTAL: \\xxx068\WORLD
       1,284.56    353  TOTAL: \\xxx068\WORLD
         699.51     90  TOTAL: \\xxx071\WORLD
         699.54     90  TOTAL: \\xxx071\WORLD
       1,146.25    175  TOTAL: \\xxx1156\WORLD
       1,146.41    174  TOTAL: \\xxx1156\WORLD
          49.35     75  TOTAL: \\xxx1167\WORLD
          42.84     51  TOTAL: \\xxx1167\WORLD
       3,906.30    712  TOTAL: \\xxx1173\WORLD
       1,944.73    432  TOTAL: \\xxx1173\WORLD
      12,655.18  12707  TOTAL: \\xxx1452\WORLD
      12,633.97  12602  TOTAL: \\xxx1452\WORLD
         397.30    349  TOTAL: \\xxx1453\WORLD
          64.10     15  TOTAL: \\xxx1453\WORLD
         164.82     20  TOTAL: \\xxx157\WORLD
         164.82     19  TOTAL: \\xxx157\WORLD
          16.01      7  TOTAL: \\xxx1650\WORLD
           0.00      2  TOTAL: \\xxx1650\WORLD
      15,187.93     20  TOTAL: \\xxx1701\WORLD
      15,188.43     20  TOTAL: \\xxx1701\WORLD
      11,010.83   5173  TOTAL: \\xxx172\WORLD
       5,130.89   1987  TOTAL: \\xxx172\WORLD
         678.49    642  TOTAL: \\xxx172L2\WORLD
         484.64    531  TOTAL: \\xxx172L2\WORLD
       2,559.40   1013  TOTAL: \\xxx172MX\WORLD
       2,367.12    989  TOTAL: \\xxx172MX\WORLD
       1,246.26    569  TOTAL: \\xxx2501\WORLD
           0.37      9  TOTAL: \\xxx2501\WORLD

  Thursday, June 6, 2013 2:26 PM
• 

  

  1

  Sign in to vote

  This worked for me:

  Add-Type -AssemblyName Microsoft.Office.Interop.Excel
  $xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
  $Excel = New-Object -comobject Excel.Application
  
  $Excel.Visible = $true
  
  ################
  $Excel.workbooks.OpenText($file,437,1,1,1,$True,$True,$False,$False,$True,$False)
  $Excel.ActiveWorkbook.SaveAs($env:tmp + "\myfile.xls", $xlFixedFormat)
  
  $Excel.Workbooks.Close()
  $Excel.Quit()

  Hope it helps,

  wizened

  
  
  

  • Edited by Wizend Thursday, June 6, 2013 4:20 PM
  • Proposed as answer by ImMax Thursday, June 6, 2013 4:52 PM
  • Marked as answer by Yan Li_ Tuesday, June 18, 2013 1:37 AM

  Thursday, June 6, 2013 4:11 PM
• 

  

  0

  Sign in to vote

  To save the file as xls do as I showed you before, and to get completely rid of any remaining chunk of Excel in your memory (you'll find them with a simple get-process -name Excel), add the following line at the end of your script:

  [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

  if you haven't done it already.

  wizend

  Thursday, June 6, 2013 4:46 PM
• 

  

  0

  Sign in to vote

  nice work, i was scratching my head

  Thursday, June 6, 2013 4:52 PM
• 

  

  0

  Sign in to vote

  I had at last more than half a dozen Excel processes in memory and no idea, why all my messing about with multiple Close() and Quit() and "...= $null" commands ended always in failure. This 'ReleaseComObject()' thing is barely documented.
  

  Thursday, June 6, 2013 5:40 PM
• 

  

  0

  Sign in to vote

  this worked for me!!!! thanks so much, what a nightmare!

  turned out as I was impersonating a build user, JOINing my PATH broke the Path for the workbook. heres my code, and yes I know this thread is all kinds of old, however, configuring office 2010 with powershell aint going anywhere for some time. hope this helps:

   ## Create & Format Excel Workbook as $cred (current user)
  
  write-host "Creating Test Workbook on $username's Desktop..." -ForegroundColor Cyan
  
  #open excel
  Add-Type -AssemblyName Microsoft.Office.Interop.Excel
  $xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
  $excel = New-Object -ComObject excel.application
  $excel.visible = $True
  
  write-host "Excel Opened" -ForegroundColor Green
  
  #add a default workbook
  $workbook = $excel.Workbooks.Add()
  write-host "Default Workbook Added" -ForegroundColor Green
  
  #remove worksheet 2 & 3 which are created automatically
  $workbook.Worksheets.Item(3).Delete()
  $workbook.Worksheets.Item(2).Delete()
  write-host "Superfluous Worksheets Removed" -ForegroundColor Green
  
  #give the remaining worksheet a name
  $uregwksht= $workbook.Worksheets.Item(1)
  $uregwksht.Name = 'Testing, Testing 123'
  write-host "Naming Worksheet:" $uregwksht.Name ... -ForegroundColor Cyan
  
  ## Saving & Closing the File on User's Desktop
  
  $outputpath = "C:\Users\[REDACTED]\desktop\ExcelTest.xlsx"
  
  write-host "Saving here:" $outputpath ... -ForegroundColor Cyan
  
  $excel.ActiveWorkbook.SaveAs($outputpath,$xlFixedFormat)
  
  write-host "Closing Workbook..." -ForegroundColor Cyan
  
  $excel.WorkBooks.close()
  
  write-host "Waiting 5 Secs for Excel.exe to close" -ForegroundColor Cyan
  
  Start-Sleep 5
  
  write-host "Closing Excel.exe" -ForegroundColor Cyan
  
  $excel.Quit() 
  
  Remove-Variable -Name excel
  
  ## Copy Workbook to network share
  
  write-host "Testing if Workbook exists here: $outputpath..." -ForegroundColor Cyan
  
  ## Test if workbook was successfully created, if "yes" then Delete, if "no" Inform the User / Throw Exception. 
     
      If (Test-Path $outputpath) {
  
  ## Delete Test File 
  	Remove-Item $outputpath -Force -Confirm:$false
      Write-Host "==> WorkBook Successfully Created, Formatted, Test-CSV Imported & saved to Trusted Network Location" -ForegroundColor Green
      Write-Host "==> .....Deleting test File" -ForegroundColor cyan
      }else{
  
      }
     } 

  
  

  Thursday, August 24, 2017 2:35 PM