Hello....
Here is my delima. I created a spreadsheet with data tabs that pul
data from an MsAccess database. I added coded to the Project Explore
/ 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 onl
executes 1 - 3 when the 'template' file is opened.

** I did not want to set the data tabs to refresh on open since user
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 int
Ms Access.

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

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

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

Access opens the Excel file and starts refreshing the data tabs bu
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 the
must be unprotected before they can be refreshed!

Again, if I run the Access queries then shut Access then opne the Exce
file...everything works fine. If I let Access invoke Excel, essentiall
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????

--
AndreLaplum
-----------------------------------------------------------------------
AndreLaplume's Profile: http://www.excelforum.com/member.php...fo&userid=2680
View this thread: http://www.excelforum.com/showthread.php?threadid=40057

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"
wrote in message
news:AndreLaplume.1ulfih_1125432325.9972@excelforu m-nospam.com...
|
| 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
|

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.....


--
AndreLaplume
------------------------------------------------------------------------
AndreLaplume's Profile: http://www.excelforum.com/member.php...o&userid=26805
View this thread: http://www.excelforum.com/showthread...hreadid=400573