RefreshAll / Protect Help Required

[AndreLaplume]

Hello....
Here is my delima. I created a spreadsheet with data tabs that pull data from an MsAccess database. I added coded to the Project Explorer / This WOrkBook / Open area such that when the workbook opens:

1) All data tabs are refreshed
2) All data tabs are protected
3) The file is saved under a new name.


*There is an IF THE ELSE condition so that the On-Open codes only executes 1 - 3 when the 'template' file is opened.

** I did not want to set the data tabs to refresh on open since users are not secured to connect to the Access dbs.

The good news is this all works fine!!!

The bad news is I really want to add code to open the Excel file into Ms Access.

So, in access I created a macro. It has a bunch of OpenQuery statements that build the tables the Ecxel data tabs need. It then issues a RunnApp with a command line:

Excel "G:\DBS\MyDbs\TEMPLATE.xls"

So, Access should runs some queries then open the Excel template which should, on-open refresh the tables, protect them and save the file.

Access opens the Excel file and starts refreshing the data tabs but before it has finished refreshing then the protect command is executed, one oir more of the tabs are protected and you get a message saying they must be unprotected before they can be refreshed!

Again, if I run the Access queries then shut Access then opne the Excel file...everything works fine. If I let Access invoke Excel, essentially is does not wait for the data tabs to refresh before protecting them.

Here is a snoppit of how I refresh and protect:




'Refresh All Linked Data
ActiveWorkbook.RefreshAll

'Protect Sheets
For I = 1 To 8
Sheets("Doc" & I & "Data").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="Protect"
Next I


ANY IDEAS?????

[Jim Rech]

I'm unfamiliar with what you're doing but I'll ask the obvious question -
how can Excel know when the refresh is complete?

--
Jim
"AndreLaplume" <[email protected]>
wrote in message
news:[email protected]...
|
| Hello....
| Here is my delima. I created a spreadsheet with data tabs that pull
| data from an MsAccess database. I added coded to the Project Explorer
| / This WOrkBook / Open area such that when the workbook opens:
|
| 1) All data tabs are refreshed
| 2) All data tabs are protected
| 3) The file is saved under a new name.
|
|
| *There is an IF THE ELSE condition so that the On-Open codes only
| executes 1 - 3 when the 'template' file is opened.
|
| ** I did not want to set the data tabs to refresh on open since users
| are not secured to connect to the Access dbs.
|
| The good news is this all works fine!!!
|
| The bad news is I really want to add code to open the Excel file into
| Ms Access.
|
| So, in access I created a macro. It has a bunch of OpenQuery
| statements that build the tables the Ecxel data tabs need. It then
| issues a RunnApp with a command line:
|
| Excel "G:\DBS\MyDbs\TEMPLATE.xls"
|
| So, Access should runs some queries then open the Excel template which
| should, on-open refresh the tables, protect them and save the file.
|
| Access opens the Excel file and starts refreshing the data tabs but
| before it has finished refreshing then the protect command is executed,
| one oir more of the tabs are protected and you get a message saying they
| must be unprotected before they can be refreshed!
|
| Again, if I run the Access queries then shut Access then opne the Excel
| file...everything works fine. If I let Access invoke Excel, essentially
| is does not wait for the data tabs to refresh before protecting them.
|
| Here is a snoppit of how I refresh and protect:
|
|
|
|
| 'Refresh All Linked Data
| ActiveWorkbook.RefreshAll
|
| 'Protect Sheets
| For I = 1 To 8
| Sheets("Doc" & I & "Data").Protect DrawingObjects:=True,
| Contents:=True, Scenarios:=True, Password:="Protect"
| Next I
|
|
| ANY IDEAS?????
|
|
| --
| AndreLaplume
| ------------------------------------------------------------------------
| AndreLaplume's Profile:
http://www.excelforum.com/member.php...o&userid=26805
| View this thread: http://www.excelforum.com/showthread...hreadid=400573
|

[AndreLaplume]

The Vba that runs when the spreadsheet opens says to:

1) Refresh all data
2) Protect the sheets
3) Save the file

When 'I' open an Excel sheet, I do not know how Excel knows to 'wait' for the refresh before issuing the Protect command. One thing appears certain, it does NOT know to wait when MsAccess invokes the Excel sheet.....