I have used the macro recorder to record a query. The following code shows where the default directory is. Unfortunately not all users of the spreadsheet will have access to the same directories (some only have access to a network drive) so I am trying to work out how to, either find and replace the code (that is use a macro to edit another macro) using the data from an input box (or data entered in a cell), or having the code read data entered into a cell and using that for the default directory. Probably having the macro read the data from a cell would cause less errors as it would be possible to check the syntax and path more easily as well as avoiding having to change the trust settings.

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=Excel Files;DBQ=C:\Corridor analysis\RouteComparisonTool.xlsb;DefaultDir=C:\Co rridor analysis;DriverId=1046;MaxBufferSize=2" _
), Array("048;PageTimeout=5;")), Destination:=Range("$A$1")).QueryTable

John