Volatile Formula Issue Causing...

[Bob Barnes]

"Do you want to save the changes you made to…vbYesNoCancel"
during Access-to-Excel automation.

I'd read that can be prevented by...
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

However, I'm using FollowHyperLink w/ the option
to open the Excel File...

In using..
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Saved = True
End Sub

...it doesn't allow the FollowHyperlink in code to open
the file..a "Gotcha"...how can I avoid the
"..want to save the changes you made.." MsgBox
and STILL open the file w/ FollowHyperlink???

TIA - Bob

 

[Sheeloo]

Which workbook you want to close without saving? The one containing the macro
or the one which is opened through the hyperlink?

Thisworkbook refers to the workbook containing the macro...
Use ActiveWorkbook to refer to the workbook opened via the macro

Workbook_BeforeClose will be fired when the workbook with the macro is closed.

 

[Bob Barnes]

Within Access, I have..
Set objSavXLWb = objXLApp.ActiveWorkbook 'which is the "template"
objXLApp.ActiveWorkbook.SaveAs filename:=IPFile, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

"IPFile" is a string to save the selected Month & Year..IE.."ABC_Jan09.xls".
The FollowHyperlink opend the "IPFile" filename.

I need to save the "IPFile", and then, w/ a different reference to the
"ABC.xls" ("template"), close that.

... Workbook_BeforeClose AVOIDS the,,,
"Do you want to save the changes you made to…vbYesNoCancel"
but also prevents opening the "IPFile" (where the Volatile will run again).

Is that clear..if not, please let me know.
Thank you - Bob