Access extracts into Excel - is there a way to automatically update? 2

[Hasit]

I have extracted a load of data from tables within an Access database using ODBC through Excel. I have extracted probably 245 columns by about 400 rows worth of data. this total is made up from several individual extracts.

The data in the Access database continually updates.

The only way I can refresh the data in Excel is to do a DATA->REFRESH DATA, which is a kind of pain in the rear because the cursor has to be placed on the individual data extracts before the data can be refreshed.

Is there a way to automatically update these data extracts when you open the spreadsheet file?

Alternatively, not being a VBA guru, I am happy to set up a macro to allow the several different data extracts to be refreshed through a button. (I have tried recording a macro, but the process includes cursor movements, which don't get recorded, so after the first data extract is refreshed, the subsequent extracts don't get refreshed).

Any advice from anyone on how I can achieve this? Should the data get refreshed automatically anyway on a file open?

(Note that I have checked Microsoft's kb and I cannot find anything to suggest that this is not possible, or should not happen anyway).

 

[Dreamboat]

I'm going to go look around, Hasit. Meanwhile, can you name the data ranges that you're extracting and then do a Go To the named range so you don't have to use your mouse?

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[Dreamboat]

Between the timer code here:

http://www.cpearson.com/excel/ontime.htm

and the Data-Get External Data-Query Database, choose Access database, and then YOUR database...

can you work it out that way, Hasit?

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[rcollman]

Did you check out the External Data Range Properties? You can set it to refresh upon open or tell it how many minutes. I've only used the refresh on open. In the data range, this would be tool > get external data >External Data Range Properties.

Or

I recorded a marco and got this:

Sheets("Data2&quot.Select
Range("A3&quot.Select
Selection.QueryTable.refresh BackgroundQuery:=False

I attached it to a command button that I can push.

Chris Chris

 

[Dreamboat]

There it is, Chris!

Hasit, when just before you're done doing the get external data bit, it asks what cell to paste the query in. Hit the Options button at that point and tell it how often you want to update.

Cool beans.

I don't see any reason you can't have multiple queries doing this into the same worksheet/book.

http://www.PiersonTech.com

dreamboat@nni.com
Brainbench MVP for Microsoft Word

 

[rcollman]

Dreamboat,
Last week I was in Excel refining a project, making the command button for the person who could not remember to data >refresh. Decided I didn't like the refresh upon open but rediscovered the minute by minute option. My user is a picky person and likes to CYA. So I knew she would want to keep a copy of the file from which she printed her report to the head office.

You saw my VB!!!!!!! The world is shaking.
Right now I am trying to figure out how to take a report which is generated by a form with 4 frames and select cases, and send it via email to the customer. Grrr. I about ready to say get it in preview mode and click on File >send as an attachment and have outlook open. This might work since they have many forms but only 6 customers. But after it sent it to Outlook, I wanted it to insert the date into the database of notification. Oh well. And this too shall come to be revealed.

Well I must toddle off to bed so I can collect my Princeton boy at the train station 4 hours away.

Chris
Chris

 

[Hasit]

Thanks all. Dreamboat, I have named some of the ranges (the clients requirement was not to tie names to the ranges as they may wish to delete some of them....*sigh*)

But the Options (I could have sworn I looked into that but couldn't see the refresh upon open option - wood and trees spring to mind methinks).

I'll use the cvode to fall back on.

I'm feeling all festive and christmassy, so a star for you both!