Martin,
Sorry it has taken me so long to respond - I had DNS problems with www.excelforum.com!
It is a bit of work to achieve but once it is set up users seem to like it.
You need to have the data on a sheet on its own (say, Sheet1) in a file (say, Example.xls) that has been saved to the filesystem. Open Example.xls and create / select a blank sheet that you want to have the QueryTable results on. On that sheet type "Account No" in B1 and "abcd" in B2 in preparation for the very first result set. Select cell A4. Select Tools | Macro | Visual Basic Editor and make sure the Immediate window is visible and completely clear of text. Copy the code below into the first line of the Immediate window and press enter. An "Enter Parameter Value" dialog should appear. Select both check boxes and select cell B2 as the source.
An "Enter Parameter Value" dialog should appear. Select both check boxes and select cell B2 as the source. Close the Visual Basic Editor. If you enter
"aaba", "abcd" or "acaa" in cell B2 you should see the appropriate results.
One of the problem issues is the file location. If you hard code the directory path then the file won't work in any other location. A way around this is to enter DBQ=.\Example.xls in the connection string. It means that it will look for the Example.xls in whatever directory is the current directory for Excel (whatever directory shows when you do a File | Open). The current directory can be set manually (by using File | Open to open the file) or by Auto_Open visual basic - I opted for manual here to avoid creating any modules for code.
It's maybe even more useful if the querytable is in a separate workbook altogether. Then the source data file can be overwritten regularly and the QueryTable returns the updated data...
I hope you'll be able to follow my ramblings here. Please don't hesitate to ask any further questions.
Ian
Bookmarks