Cannot prevent Excel warning message on workbook open

[plungerman]

Nice to belong to such a group. I have been scanning your pages for a long time now.

I have an Excel workbook that is linked to an Access table. The table link is set to refresh when the workbook opens.
This works fine once I used;

ActiveWorkbook.Connections("tableReports_2").OLEDBConnection.Refresh

The worksheet is protected so that some columns cannot be changed as they are locked.
Problem is that when the workbook opens it insists on warning the user with a message about Protection.
NO warning when this runs from a command button, ONLY when the file is opening.
The code is as follows;

Public Sub Workbook_Open()

ActiveSheet.Unprotect

'Refresh code ' plus all the Do events and time delay you can imagine.

ActiveSheet.Protect ' Same as stated in other threads.

Because the file is opening somthing is telling the refresh to dialog the user.
We cannot have this, we want to refresh the link and re-protect the sheet.

I await your reply and comments on improving my threading format.

 

[Andrzejek]

Did you try:

    Application.DisplayAlerts = False
    [green]
    '... do you stuff here
    [/green]
Application.DisplayAlerts = True

Have fun.

---- Andy

 

[plungerman]

Thanks. This was the first thing to try.
Does nothing.
Sorry I left it out of the example.

It looks like the action of the file opening is adding its own layer of checking and testing.
I cannot trap the line of code since it only happens differently at Open and the breakpoints don't stay after the file is closed, even though I leave Excel up.

Still puzzling.

 

[SkipVought]

If your query is to a text file, there is a prompt property for the querytable that can be assigned FALSE.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[plungerman]

Thanks, but the link is to a table in Access that gets re-queried by yet another piece of VB. The vb then opens the Excel where the data is to be viewed in the spreadsheet.

Having asked the question and made other clarifications, it occurred to me to try another thing.

Because I am now (since two days ago) relying on the OLEDB.Refresh to refresh the query I thought that I might be able to turn off the setting of Right-Click
< External Data Properties/Connection Properties/Refresh Control/ Last Refreshed: Refresh Data When Opening File >

This was the clever means by which I was refreshing the data before I started to Protect it. But this auto-refresh is not able (clearly) to manage the Protect states. Now that I can refresh it through proper VBA I have turned off the auto-refresh and get no extra warnings with the form opening. Such a fuss.