VBA to update cells with data from Microsoft Access table

**drewship** · 06-30-2009, 12:45 PM

Hello all,
I have a spreadsheet and an Access database that contain the same items and prices. The database and spreadsheet are on a server shared drive. I would like the spreadsheet to automatically update its list of items and prices from the Access database whenever the spreadsheet is opened. Right now, we update the item and price list on both the spreadsheet and the database but I want to just update the database and have the spreadsheet pull the updates from the database.

Thanks in advance!!
Andrew

**T-J** · 06-30-2009, 03:57 PM

This can be done without VBA.

Data > Import External Data > New Database Query, choose MS Access Database and go through the Query wizard choosing tables and columns.
Right-click the returned data and tick the option 'Refresh data on file open' in Data Range Properties. Et voilà!