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: