Workbook.close throws an error 5 - vba

[channs]

Hi

I'm having some trouble with the following. I run a macro which opens a file makes some changes and then closes the file saving the changes.
After the close excel throws an error 5.
can anyone throw some light on why this is happening.

Using Excel 2002


Sub testwb()
Dim strBOLogFile As String
Dim wbHistory As Workbook

lngToday = 40751

strBOLogFile = "file.xls"
g_pswdBOLog = "pw"
lret = 1

If lret = 1 Then
'Open Bid Offer History Log

Set wbHistory = Workbooks.Open(strBOLogFile, , , , , g_pswdBOLog, True)
Set wsHistory = wbHistory.Worksheets("BidOfferHistory")
'Update BO History record
i = 1
Do Until wsHistory.Range("BOHistory").Offset(i, 0) = "" Or wsHistory.Range("BOHistory").Offset(i, 0) = lngToday
i = i + 1
Loop
i = 1500

If wsHistory.Range("BOHistory").Offset(i, 0) = lngToday And Not SPH_QUIET_MODE Then
lngRet = MsgBox("History already exists for " & CDate(lngToday) & vbCrLf & "Do you want to overwrite?", vbYesNo, "History Exists")
Else
lngRet = vbYes
End If

If lngRet = vbYes Then
With wsHistory
.Range("BOHistory").Offset(i, 0).Value = lngToday
.Range("BOHistory").Offset(i, 0).NumberFormat = "dd-MMM-yy"
.Range("BOHistory").Offset(i, 1).Value = dblBidOffer * 0.01 'Bid offer on standard trades (£)
.Range("BOHistory").Offset(i, 2).Value = dblQuoteBO * 0.01 'Bid offer on special quoted prices (£)
End With
End If

If Not Err.Number = 0 Then
strErrorMsg = "Error " & Err.Number & vbCrLf & _
"Source " & IIf(Err.Source = g_VBAProjectName, strFnName, Err.Source) & vbCrLf & _
"Message " & Err.Description

MsgBox strErrorMsg 'Display error if debug is on
Err.Clear
End If
wbHistory.Save

' NO ERROR AT THIS STAGE, WB SAVES OK

If Not Err.Number = 0 Then
strErrorMsg = "Error " & Err.Number & vbCrLf & _
"Source " & IIf(Err.Source = g_VBAProjectName, strFnName, Err.Source) & vbCrLf & _
"Message " & Err.Description

MsgBox strErrorMsg 'Display error if debug is on
Err.Clear
End If

wsHistory.Range("A1").Select
Workbooks(wbHistory.Name).Close SaveChanges:=False

' ERROR THROWN HERE AFTER CLOSE

If Not Err.Number = 0 Then
strErrorMsg = "Error " & Err.Number & vbCrLf & _
"Source " & IIf(Err.Source = g_VBAProjectName, strFnName, Err.Source) & vbCrLf & _
"Message " & Err.Description

MsgBox strErrorMsg 'Display error if debug is on
Err.Clear
End If

End If
End Sub

 

[Ruddles]

Wecome to the board!

"Invalid procedure call". Has this code ever worked, or has it never worked? What's the value of wbHistory.Name at that point?

Try:-

wbHistory.Close SaveChanges:=False

Incidentally, when posting code, it's advisable to place it between CODE tags - the # icon in the advanced editor toolbar. This preserves indenting and applied a fixed-width font, both of which make code easier to understand and this in turn encourages more responses.

 

[channs]

Thanks Ruddles

The code does work on other machines (not mine!). wbHistory.name is the workbook name. The same problem also happens if I use

wbHistory.Close False

The workbook does close and save but increments Err.Number by 1.0 too.

Thanks for the advice, will use the # next time

S

 

[Ruddles]

What's the actual value of wbHistory.Name at that point?

If you're not seeing a MsgBox immediately before the .Close, that suggests Err.Number is zero at that point, and if you're seeing a MsgBox immediately after the .Close, it can only be that command that's raising the error.

What do you mean when you say it "increments Err.Number by 1.0"?

How are you stopping VBA going into break mode when an error is raised?

 

[channs]

Cracked It! It was nothing to do with the VBA but an add-in conflict.
Please ignore this thread now and consider it a nothing thread.

Ruddles, thanks for you help anyway!

S

 

[Ruddles]

All's well that ends well!