Excel - MS Query Generalised file locations - is hard wiring avoidable?

Video is here

Google this problem and one hits vast numbers of solutions, all involving the use of that wonderful concept, " Turing Complete" tools. Clearly it is perfectly possible using a tool such as VBA or indeed any of the vast array of languages.

The first step is always to know the correct language to search for

Using the Current Directory?

or

Using the Default File Location?

or

Generalising the Connection String (of course it is!)

The MS Query Wizard uses a default connection string to establish a connection to your Excel file.  However, this default will likely not work when sharing the file with others. For example, their data source and the path to its location will often be different, so edit the connection string and remove the data source name.


My video shows every step of the following and more, so can be viewed in conjunction with this text.

By an accountant - how's that relevant?
For me it's very relevant, because it means I can share my work with clients.

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, Mac users see section 4 here
PC users have ODBC pre-installed, if not see the Mac users link.


VIEW POINT

From the point of view of an ordinary "point and click" user (i.e. me), which means programming is not required.

EXPERIENCE REQUIRED?

None really, says me - having used spreadsheets since 1984.
If you have played with Excel's MS Query a bit then you should be fine.

UNDER THE HOOD

My previous article writes about this, so here is that excerpt here

CONSTRAINTS

Permitted: MS Excel.
Permitted: Point & click only.
Verboten: VBA or any other language; this is about us Users setting settings.

REQUIREMENT

STOP MS Query from insisting that wherever my MS-Query Excel file goes it MUST always be saved in the same directory as it was when it was first created.

WHAT'S SPECIAL ABOUT THIS?

This will be no mean feat as the received wisdom is that it cannot be done - except with VBA etc. Try Googling it.

If we are willing to accept this challenge, it will mean that SQL Query based Excel files can be shared anywhere in the world, without needing VBA to fix the path name. VBA from un trusted sources is a problem, this solves that by removing it. VBA in the hands of non programmers is also a problem, this will solve that too.

Doubtless we may find a constraint or so, but that will be acceptable as long as the constraint is manageable by a User and without the User having to fully re-create the SQL Query process. Straightforward point, one time only "click and type or delete" will be acceptable as a result.

STRATEGY

Good question!

My venture here is to explore if there is a way to influence the Excel Connection Properties Definitions (two property boxes that contain strings) into not being hard wired with path names.

Step 1 - or so I thought.

What follows here derives from the "Under the Hood" section above.

Where it gives the path e.g. C:\Documents\ what happens if I delete \Documents\ ? My idea (i.e. strategy)  is that back in the 1980's when all we had was DOS, and now I think of it  MS Query was born not much later, that where the "current directory" was to be used it would be indicated by typing x: where x = drive (such as C D E X Z etc). So for drive C it would be " C: "
Any program reading C: would read this as C:\currentdirectory . To prove this go to a DOS (cmd)  prompt, cd to some directory on C drive. the go to D: drive, then go back to C: and the directory will be the same one from whence you departed. The system "remembers". It has a memory. Later versions of Excel have the INFO("directory") formula which discloses this system INFOrmation. I think latterly Excel has developed its own Current Directory as distinct from the System, but that is "ok" as long as we can read it, or rather, as long as Excel can.

I had thought I could find this as a SET variable (old system variables), but not so easy. In any case perhaps it matters not as it is a statement. Which is to say it is immutable, the current directory just is, it cannot be something else, ever.Because if it is, then that is what it is, not the other.

As such I suppose it doesn't really need a variable name we can set. It would be nice to know what it is though.

In the old days "prompt $P$G " was not preset we had to configure it so we could see our current directory as part of the command prompt. These days one can see  it there in the system variables using the SET command.

In those days of yore when the current path (directory plus parents) the way we could see what it was, was by typing CD (or CHDIR) without any parameter at the prompt. Up pops the path. Come to think of it that indicates the $P AND THE $G store information. I recall very well that one could make the prompt look like all sorts of things. Now it comes to me that the $G means "the greater than sign" i.e. " > ". Clearly the $P means the PATH! I had forgotten. Granted it is in fact not the system Path. But it is a path, the one for the current directory. If MS Query and ODBC can read this variable we are home and dry. However, I doubt it is that simple.

TUTORIAL

Open a New Excel file.

Copy/enter some test data in to one sheet,

Name your sheets so they are clear to work with in this process.

As such we have now in the same single file one input sheet with data and an output sheet to receive the result.

SAVE THE FILE !  The reason it is important (critical) to save the file is that we need a copy to be there when we look for it in later dialogues. (i.e. a copy needs to be on the hard disk.)

Excel 2010 Menus click as follows:

Data
From Other Sources
From Microsoft Query FEAR NOT! We will at no time depart from the Excel user interface.

then in

Choose Data Source dialogue:

Excel files
OK

Select Workbook dialogue*:

Select
yourfile.xlsx
OK

Query Wizard - Choose Columns

The sheets within the yourfile.xlsx file you saved and then selected above are listed.
Select the sheet that contains the test input data and the two headings named therein.

Click " > " which transfers all (both) the column headings in to the right hand list box.

Click Next

Query Wizard - Filter Data

We are not using a filter, but this is where you could enter selection criteria - which can be made user friendly, but that is for another article.

Click Next

Query Wizard - Sort Order

Click Next (no sorting)

Return Data to Microsoft Excel (pre-selected)

Click Finish (but it does NOT finish here, keep going)


to

Import Data dialogue:

Select target for report, choose A1 (for sake of example)
Click "ok"

Now edit the directory name attached to the file name you have saved as.

Click Properties

Connection Properties dialogue

Click Definition tab and you will see the two boxes containing the text that can be edited.

In the second box you will see something like:

FROM ` C:\Documents\YOURFILENAME.xlsx`.`Peuple$` `Peuple$`

Here is the trick: delete the directory, in the above example C:\Documents\

So it now reads:

FROM ` YOURFILENAME.xlsx`.`Peuple$` `Peuple$`

Click "ok"

An error message about ODBC will come up,

Click "ok"

A continue or not message will come up

Click "Cancel"

Click "Close"

Save your file

THAT'S IT!

Your Excel Query file is now portable to other users, so long as they save it to their default file location as shown in File - Options - Save - Default File Location.  This is the standard general location where Excel will save files unless told otherwise.

My 18 minute video here   shows every step of the above and more, so would best be viewed in conjunction with this text. In fact this video will save you a lot of time if you are trying to understand how this works and what the issues are.

While it is simple enough to say "delete the directory name" and indeed that is all there is to it, in reality the process is clearly not one I think was intended to see the light of day in the hands of Users

Here is the original Microsoft text shown here so as to illustrate the environment MS expected this information to be used in, it is clearly 100% Developer, not User.



The magic expression is:

Edit the connection string and remove the data source name


Nowhere in Google searches does that show up and one can see why, it is buried deeply inside developer land.

Error trap

I have experienced in testing that you don't necessarily have to change the Connection String DefaultDir, but if the directory that exists there doesn't exist on a users' hard drive, you may get an error. As such I suggest setting the distributable file to a default directory that is largely certain to exist, such as C:\


Good luck!

Anthony