Volatile Formula Issue Causing...
 

"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

Volatile Formula Issue Causing...
 

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" wrote:

"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

Volatile Formula Issue Causing...
 

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

"Bob Barnes" wrote:

during Access-to-Excel automation.



"Sheeloo" wrote:

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" wrote:

"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