Excel save file - 2007

[bamako1]

Hi there,

I have the following code which does some formatting of an excel file and then save the file with the same name.

The code runs fine on Microsoft Excel 2003 but when I run the same code on a PC with Microsoft Excel 2007 installed I get the following error message. Any ideas?

“Windows Script host
Line: 59
Char: 17
Error: Document not saved
Code: 800A03EC
Source: Microsoft Office Excel”
----------


Dim sfile
Set sfile = WScript.Arguments
Dim objXL
Set objXL = CreateObject("Excel.Application")
With objXL
.Workbooks.Open (sfile(0))
'Wrap text for the heading(row4)
.Application.Rows("4:1500").Select
With .Application.Selection
.WrapText = True
End With
'Adjust row height
.Application.Rows("3:1500").EntireRow.AutoFit
.Application.Rows("5:1500").Select
'Adjust column width
.Application.Cells.Select
.Application.Cells.EntireColumn.AutoFit
'Fix column width
.Application.Columns("B:B").Select
.Application.Selection.ColumnWidth = 40.00
.Application.Columns("V:V").Select
.Application.Selection.ColumnWidth = 40.00
'FreezePane
.Application.Range("B5").Select
.Application.ActiveWindow.FreezePanes = True
'Add Border
.Application.Range("3:1500").BORDERS.Color = RGB(0, 0, 0)

'Bold font for non-zero values
const xlCellValue = 1
const xlGreater = 5
.Application.Range("AG5:AL1500").Select
.Application.Range("AL1500").Activate
.Application.Selection.FormatConditions.Delete
.Application.Selection.FormatConditions.Add xlCellValue, xlGreater, "0"
With .Application.Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
'Auto filter
.Application.Rows("4:4").Select
.Application.Selection.AutoFilter
.Application.Selection.AutoFilter
.Application.Selection.AutoFilter
.Application.Range("B5").Select
'Save and Close
.Workbooks(1).Save
.Workbooks(1).Close (False)
.Application.Quit
End With
Set objXL = Nothing

[lynnnow]

Just a wild guess, did you check for any missing libraries? Not versed with 2007, but just a suggestion.

[bamako1]

Hmm... not sure how to go about checking the missing libraries. Please elaborate your response!

I am running the above VB script from command prompt (OS = Windows Server 2003).

C:\test> Start format.vbs "C:\test\myfile.xls"

[lynnnow]

In Excel 2003, you need to go to Tools > References to check if there is a "MISSING" in any of the checked libraries in the dialog box. In 2007 there might be a similar option, which you need to check. I don't have Office 2007 here to check out the options so someone who does have it can help you out.

[Bob Phillips]

It's the same in Excel 2007. Just to be clear, it is in the VBIDE, not Excel, that you check.

[bamako1]

There are no "MISSING" tag for any of the libraries in the dialog box.

[mdmackillop]

If the file is in the Root directory C:\ it may not save. I know you cannot create one there using code.

BTW, Try to avoid Selection in your code. It speed things up
eg

.Workbooks.Open (sfile(0))
'Wrap text for the heading(row4)
.Application.Rows("4:1500").WrapText = True
'Adjust row height
.Application.Rows("3:1500").EntireRow.AutoFit
'Adjust column width
.Application.Cells.EntireColumn.AutoFit
'Fix column width
.Application.Range("B:B,F:F").ColumnWidth = 40#
'FreezePane
.Application.Range("B5").Select '<== needed here
.Application.ActiveWindow.FreezePanes = True
'Add Border
.Application.Range("3:1500").Borders.Color = RGB(0, 0, 0)