Import Data from Access Query with Calculated Fields

Hi,

I am trying to link my excel spreadsheet with an access query. The query has 3 calculated fields that has does dlookups that pulls information from various tables.

I have tried using the "Get External Data" access button but I get an "Data could not be retrieved from the database. Check the database or conact your database admin ......" error. Once I delete the calculated field, this method works so I am sure that it's the calculated fields causing the problem.

Would the issue be that one of the calculated fields uses DLookup and that is not an excel function?

I have also tried with VBA with the code below, everything imports correctly excecpt for the 3 calculated column which all remain empty.

HTML Code: 

Are you familiar with MS-Query? It's part of Excel and allows you to import data from any database for which you have a driver. There's a driver built in for text files, MS-Access and other Excel sheets among a couple other popular databases.

The best way to use MS-Query is to develop the query on the Access side of the house and use it as your data source.

This article will give you an idea about MS-Query: http://www.utteraccess.com/wiki/index.php/MS_Query.