Updating Excel from Access and then Access from Excel. 1

[DBSSP]

Hi people! I need to know how I can get Excel to update from an Access table, without making a macro run. I tried using VBA to call the macro but it would just cause Excel to freeze and then you would have to use task manager to shut it down. Any ideas?

Jay

 

[xlbo]

Just link the spreadsheet as a database table - updates both ways - ie change Access and excel will show the change. Change excel and access will show the change

HTH
Geoff

 

[DBSSP]

I think I tried that but excel still had to be opened manually. I need this to be automated. The spreadsheet is also in a format to be printed so my boss can see the intermediate tally results.
Keep the ideas coming in though! I'm trying my best on my end and if i reach a solution before anybody else finds one, I will be sure to post it. Thanks again!

Jay

 

[bryanbayfield]

Did you try adding Excel and Access libraries to your VBA environments? You can then automate the process of updating by accessing the Access model from Excel and vice versa. I can see no good reason why this would make your software freeze if done properly. The process would also be automated.

SuperBry!

 

[DBSSP]

I did those things, I'm pretty sure it has something to do with the macro security in Excel. That is something that is the company standard. So I wonder if there is code to change that and then change it back when it's finished......any ideas?

Jay

 

[xlbo]

Can't change macro security settings from code - the whole point of the security settings is to stop that happening thru virii etc. however - macro security should not cause excel to freeze
Will come back with some excel accessing code from Access when I have a little time
Geoff

 

[DBSSP]

Thank you! It is very much appreciated! I alsoi have a problem with the timing of the commands in a macro on Excel. It wants to save and close during the refresh, which of course causes problems of all sorts. Any light shed on that would be extremely useful as well!

jay

 

[xlbo]

Another question is how the data is currently linked - I assume you are using a pivot table based on the access table or Data/Get External Data (Query) as you are using the "refresh" command. So the real question would be how do I open excel and refresh the query yes ??

If this is the case, you have a VERY easy answer - queries and pivot tables can be set to refresh on document open. This may be a better way round for you as you then don't need to worry about controling it from Access....
If this is not the case and you currently do not have a link between the 2, please respond and I'll have a think about it. NB - if you havn't got it linked like this already, it may be the best way to go.
HTH
Geoff

 

[DBSSP]

I have it linked through a query. My only problem is the timing. The document will save and close immediatly causing the refresh to be interuptted.


jay

 

[xlbo]

Why bother to make it update through access - just make the query refresh on (excel wb) open - I presume that it doesn't matter if the data isn't the latest available if no-one is looking at it. Therefore, there is no reason to update the data until someone wants to look at it - hence using the wb_open event to trigger the update. May take a little while to open but would completely solve the problem
HTH
Geoff