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:
Code:
[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!
