Query DataSource Filename & File Path: ?HowTo Update Query Automatically when either one Changes?


Board Regular
Jul 13, 2009
Windows XP Home / Professional
Excel 2003 / 2002
VBA 6.5.1024
MS Query 11.8164.8172

(concise version):

I have several QueryTables in an Excel Workbook.
The Query for each was created from the User Interface, and with Microsoft Query, (not VBA so far).
During development, I am changing the filename of the DataSource each time I save it (ie about every 15 to 30 mins!).
Is there an easy way to ensure that the Filename and Path contained in the Query automatically update if / when they change?
I'd prefer to do so without getting into VBA if possible. But can do if required. The rest of the project is full of it.

More detail to follow.

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)


Board Regular
Jul 13, 2009
More Detail Re: Query DataSource Filename & File Path: ?HowTo Update Query Automatically when either one Changes?

The further detail is as follows:

It may sound mad, but the DataSource file and the Query ResultRange / Destination file are the same Excel workbook!

I recently discovered that Database Queries would give me much of what I was previously doing programatically with much greater ease, far less code, and also much faster. So I am now trying to convert to this approach.

If you've read any of my other posts, you may be aware of My Previous Approach...

On the DataEntry worksheet, the user may apply or remove various filters, which triggers an AdvancedFilter xlFilterInPlace operation on the Records they see.

The Visible Records are then copied to an Intermediary Table on another Worksheet (previously I used AdvancedFilter xlFilterCopy but now I use something along these lines:
[FONT=Courier New]Range("tblSourceTable").SpecialCells(xlCellTypeVisible).Copy
Range("tblIntermediaryTable").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False[/FONT]
There are another 5 Destination tables are on this second worksheet, with identical dimensions to, and containing the same data as the Intermediary Table but sorted in 5 different ways (variations in both Field order, ie left-to-right sort; and Records, ie top-to-bottom).

The data duplication and Sort operations take too long in VBA (about 5-10 seconds). With the Queries, a few lines of simple code Refresh the data, and the desired Field and Record sort orders are maintained.

My aim...

I use the Tables as RowSources for ComboBoxes.
Formulae analyze the table contents and adjust the adjust the ColumnWidths and overall Width of the control. Each one also states the TextColumn and BoundColumn for that control.

Previously, I used complex Dynamic Named Ranges to avoid the blank lines at the bottom of the tables.
But with the queries, the ResultRange automatically restricts itself to the Records returned.

Incidentally, early on, I used Array Variables instead of tables, but found the Sort and Filter operations very slow in code compare to the tables.

Back to the Question:

In the long run, I hope that the File Path will be constant. If not, then I'm sure I could at least ensure that a group of folders /subfolders maintain the same relation to each other.

I am less certain of maintaining a single constant File Name though. I had planned on giving each person an Excel file with their name in the filename, and possibly a reference to the date, or even the date of saving the file.

In case you are wondering why not do it in MS Access, we only have one Access license, so the majority of the UI will be Excel. I will be using a simple Access Database (on a networked computer) for another aspect of the project, but for that operation only two calls to the Access Database would be required (one on file open, one on close).

The thing I am trying to do currently would be done about once a minute, by each user. It needs to be responsive. Each user needs their own copy of these Tables / Query ResultRanges, which as I say are currently in a single Excel workbook.

If someone thinks the solution lies in using Access, then I'll give it a shot.

Also if VBA is required, then I'll do that.

I have to say though, that I've no training in Access, SQL or MS Query. MS Query seems pretty minimal though so I'm sure Ill pick it up.

I've taught myself a great deal about Excel and have a reasonable handle on VBA.

Sorry for the length of this post. Writing is always such a long task compared to talking, because you never know what info the reader is going to require. With talking, you can just ask for the missing info.

One last thing, I posted this in the Excel / VBA forum because up to now that's all I've required. Please let me know if I should also post a link to it in the Access forum, or ask a modearator to move it. Ideally, I'm looking for an Excel / VBA solution though. Cheers!

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics