Document Not Saved Error in Excel.

Question

This is my code snippet for comparing 2 excel files. I call this function from my QTP scripts. I get this error quite often which causes my test script to fail. Any pointers will be appreciated.

Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Visible = False
objExcel.DisplayAlerts = False
Set objWorkbook1= objExcel.Workbooks.Open(excelFile1)
Set objWorkbook2= objExcel.Workbooks.Open(excelFile2)

Set objWorksheet1= objWorkbook1.Worksheets(1)
Set objWorksheet2= objWorkbook2.Worksheets(1)

<Code that compares the 2 files & marks the cell in red where there is a mismatch)
objWorkbook2.Save

ERROR MESSAGE:
Document not saved.
Function file: C:\Program Files\Mercury Interactive\QuickTest Professional\Tests\ReusableFunctions.qfl
Line (33): "objWorkbook2.Save".

Answer 1

There're several articles in Microsoft knowledge base that discuss possible errors while saving Excel files:

• How to troubleshoot errors when you save Excel files
• You receive an error message when you try to save a file in Excel
• Error Message: Document Not Saved

For instance, errors can be caused by insufficient drive space or permissions, lost network connection (if you are saving to a network drive), antivirus conflict, embedded objects and so on. Your case is most probably one of these.

Anyway, woudn't it be better to create a new workbook with marked mismatches rather than change an existing one?

Answer 2

Hi Sowmya,

Since you put only a fragment of your code I'm not sure whether you put both statements. There should be 2 calls:

objWorkbook2.Save
objWorkbook2.Close

You can also use the following code isolated in a function.

Origin: http://automation-beyond.com/2009/05/25/excel-vbscript/

Public Function ExcelWorksheetCompare(ByVal sWorkbook1, ByVal sWorksheet1, ByVal sWorkbook2, ByVal sWorksheet2, ByVal objParameter)
Dim boolRC, boolSheetExists
Dim FSO, XLHandle
Dim XLBook1, XLBook2, XLSheet1, XLSheet2
Dim Iter, objCell

‘Verify both files exist
Set FSO = CreateObject(”Scripting.FileSystemObject”)
boolRC = FSO.FileExists(sWorkbook1)
If Not boolRC Then
ExcelWorksheetCompare = FALSE
Exit Function
End If
boolRC = FSO.FileExists(sWorkbook2)
If Not boolRC Then
ExcelWorksheetCompare = FALSE
Exit Function
End If
Set FSO = Nothing

Set XLHandle = CreateObject(”Excel.Application”)
XLHandle.DisplayAlerts = False

‘Open workbook1
Set XLBook1 = XLHandle.WorkBooks.Open(sWorkbook1)

‘Verify sheet exists (1)
If isNumeric(sWorksheet1) Then
sWorksheet1 = CInt(sWorksheet1)
If (sWorksheet1 > 0) AND (sWorksheet1 <= XLBook1.Worksheets.Count) Then
Set XLSheet1 = XLBook1.Worksheets(sWorksheet1)
boolSheetExists = TRUE
Else
boolSheetExists = FALSE
End If
Else
boolSheetExists = FALSE
For Iter = 1To XLBook1.Worksheets.Count
If XLBook1.Worksheets(Iter).Name = sWorksheet1 Then
Set XLSheet1 = XLBook1.Worksheets(Iter)
boolSheetExists = TRUE
End If
Next
End If

If Not boolSheetExists Then
XLBook1.Close
XLHandle.Quit
Set XLBook1 = Nothing
Set XLHandle = Nothing

ExcelWorksheetCompare = FALSE
Exit Function
End If

‘Open workbook2
Set XLBook2 = XLHandle.WorkBooks.Open(sWorkbook2)

‘Verify sheet exists (2)
If isNumeric(sWorksheet2) Then
sWorksheet2 = CInt(sWorksheet2)
If (sWorksheet2 > 0) AND (sWorksheet2 <= XLBook2.Worksheets.Count) Then
Set XLSheet2 = XLBook2.Worksheets(sWorksheet2)
boolSheetExists = TRUE
Else
boolSheetExists = FALSE
End If
Else
boolSheetExists = FALSE
For Iter = 1To XLBook2.Worksheets.Count
If XLBook2.Worksheets(Iter).Name = sWorksheet2 Then
Set XLSheet2 = XLBook2.Worksheets(Iter)
boolSheetExists = TRUE
End If
Next
End If

If Not boolSheetExists Then
XLBook1.Close
XLBook2.Close
XLHandle.Quit
Set XLSheet1 = Nothing
Set XLBook1 = Nothing
Set XLBook2 = Nothing
Set XLHandle = Nothing

ExcelWorksheetCompare = FALSE
Exit Function
End If

‘Mark range

‘Compare and mark mismatches red
For Each objCell In XLSheet2.UsedRange
If objCell.Value <> XLSheet1.Range(objCell.Address).Value Then
objCell.Interior.ColorIndex = 3
Else
objCell.Interior.ColorIndex = 0
End If
Next

‘Save and close

XLBook1.Close

XLBook2.Save
XLBook2.Close

XLHandle.Quit

Set XLSheet1 = Nothing
Set XLSheet2 = Nothing
Set XLBook1 = Nothing
Set XLBook2 = Nothing
Set XLHandle = Nothing

ExcelWorksheetCompare = TRUE

End Function

Thank you,
Albert Gareev
http://automation-beyond.com/

Answer 3

Thank you Albert/Helen! I took the suggestion to storing the mismatched cells in to another excel file & that worked perfectly.