Excel Under the Hood - MS QUery Data-Connections-Properties-Definition

1
Why this subject?
The MS Query Wizard uses a default connection string to establish a connection to Excel files.  However, this default will likely not work when sharing the file with others and it causes never ending loss of credibility because the error messages are really quite frightening if you have no idea what they mean. It just looks like the system is broken.

2
Who is this for?
Those who need to share MS-Query based Excel spreadsheets and who previously quite probably did not do so because of this problem.
From what I have read about this the underlying problem has been that Microsoft viewed this MS Query as a tool they were providing to developers and not as a tool for ordinary mortals. As a result this key information was never disseminated. The developer community moved on long ago from this subject and so we have today's situation where no one really knows. Yes there are VBA fixes, but they are all workarounds, not solutions to the underlying problem, which is how to un-hardwire the file's directory name.

3
Where? Country:
This will work for anyone anywhere, but Mac users will need to install ODBC.

4
Is any special "tech" involved?

Yes, ODBC, which is built in for PC and an extra you have to acquire for Mac.







UNDER THE HOOD

- A quick look

If you want to move your files about between folders then it will help (but is NOT essential) to be familiar with editing folder and file names, so for example being able to change C:\Documents\ to read J:\desktop\ in three places and to edit your file's name in two places. These "dark places" where this is done are shown here:
The clicks are: Data-Connections-Properties-Definition
Look for where it says J:\DocumentsJ\TEST.xlsx in two places and J:\DocumentsJ in one.

Where it says "Command text:" you could in fact type in a full blown SQL Query if you know how. You might be an SQL guru, in which case have fun! For us lesser mortals we need make no entries or changes here at all and simply stick with "point & click" which is what creates this "command text" for us, including folders and filenames. What we are doing here is having a quick look under the hood/bonnet at the engine, so we know where it is, but need not touch it - not ever if we do not so wish because these processes are very quickly created from scratch on demand. Indeed having practice and becoming more adept is probably a good thing.


AN IDEA TO PLAY WITH

These settings are really just collections of text separated usually by semi-colons ;
If you copy the whole set into, say, notepad or even Excel, then chop it up so that each piece of text up to the next semicolon is on its own line, it can all begin to look a lot more clear. Once you have done that you can start to insert your own text to change these "variables" and then use the CONCATENATE function to glue them back together as your own self created dynamic strings. "String" just means a string of different pieces daisy chained together.