Author Posts

January 29, 2014 at 7:19 am

Hi
I am using Powershell scripts to run Excel 2013 on a Windows 2012 server and save the spreadsheets as 2003 .xls workbooks.

If I use the $xlNormal = 51, then the spreadsheets save correctly as .xlsx workbooks which I cannot use in the next stage.
I have tried $xlNormal = 43 where an exception "SaveAs method of Workbook class failed"
With $xlNormal = 29 Excel does not create any output file and hangs until I do a stop-process
With $xlNormal = 56 I see a temporary file with no type being created with size 1K and Excel hangs until I do a stop-process

I have to create the earlier version of .xls file as these are read by a system that uses Excel 2003 using complex macros.
What am I doing wrong and how do I save with the old formats.

I have included the working parts of the script.

regards

David

January 29, 2014 at 7:33 am

Try setting $objExcel.DisplayAlerts to $false instead of $true. That might be causing the behavior you're observing.

For reference, here are the valid values of the XlFileFormat type:

PS C:\Users\dwyatt> Get-EnumValues ([Microsoft.Office.Interop.Excel.XlFileFormat]) | ft -AutoSize

Name                          Value Hex       
----                          ----- ---       
xlSYLK                            2 0x00000002
xlWKS                             4 0x00000004
xlWK1                             5 0x00000005
xlCSV                             6 0x00000006
xlDBF2                            7 0x00000007
xlDBF3                            8 0x00000008
xlDIF                             9 0x00000009
xlDBF4                           11 0x0000000B
xlWJ2WD1                         14 0x0000000E
xlWK3                            15 0x0000000F
xlExcel2                         16 0x00000010
xlTemplate                       17 0x00000011
xlTemplate8                      17 0x00000011
xlAddIn                          18 0x00000012
xlAddIn8                         18 0x00000012
xlTextMac                        19 0x00000013
xlTextWindows                    20 0x00000014
xlTextMSDOS                      21 0x00000015
xlCSVMac                         22 0x00000016
xlCSVWindows                     23 0x00000017
xlCSVMSDOS                       24 0x00000018
xlIntlMacro                      25 0x00000019
xlIntlAddIn                      26 0x0000001A
xlExcel2FarEast                  27 0x0000001B
xlWorks2FarEast                  28 0x0000001C
xlExcel3                         29 0x0000001D
xlWK1FMT                         30 0x0000001E
xlWK1ALL                         31 0x0000001F
xlWK3FM3                         32 0x00000020
xlExcel4                         33 0x00000021
xlWQ1                            34 0x00000022
xlExcel4Workbook                 35 0x00000023
xlTextPrinter                    36 0x00000024
xlWK4                            38 0x00000026
xlExcel7                         39 0x00000027
xlExcel5                         39 0x00000027
xlWJ3                            40 0x00000028
xlWJ3FJ3                         41 0x00000029
xlUnicodeText                    42 0x0000002A
xlExcel9795                      43 0x0000002B
xlHtml                           44 0x0000002C
xlWebArchive                     45 0x0000002D
xlXMLSpreadsheet                 46 0x0000002E
xlExcel12                        50 0x00000032
xlOpenXMLWorkbook                51 0x00000033
xlWorkbookDefault                51 0x00000033
xlOpenXMLWorkbookMacroEnabled    52 0x00000034
xlOpenXMLTemplateMacroEnabled    53 0x00000035
xlOpenXMLTemplate                54 0x00000036
xlOpenXMLAddIn                   55 0x00000037
xlExcel8                         56 0x00000038
xlOpenDocumentSpreadsheet        60 0x0000003C
xlCurrentPlatformText         -4158 0xFFFFEFC2
xlWorkbookNormal              -4143 0xFFFFEFD1

January 29, 2014 at 7:53 am

Hi Dave

Thanks. That appears to have sorted the problem.

Regards

David