|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I have a spreadsheet that contains multiple worksheets. Some of the
worksheets query our Navision database and others then query the data
retieved from Navision eg. Sheet1 returns data from Navision then
Sheet2 queries Sheet1.
The problem I have is that if the spreasheet file is saved anywhere
other than the location that it was created in or with a different file
name then the queries that reference the data returned from Navision
always query the original file.
For example the original file is G:\myquery.xls and contains Sheet1 and
Sheet2 as detailed above. If the file is moved or saved as say
D:\yourquery.xls then the Sheet1 query from the Navision database works
fine but the Sheet2 query will still return data from G:\myquery.xls.
I know that I could change the file mapping in the sql statement in the
query on Sheet2 but this file is used by multiple users on a regular
basis and therefore any change needs to be automatic.
If anyone can make any recommendations as how I can tackle this it
would be greatly appreciated. If your solution is using VB then please
go easy with me as my knowledge is fairly basic in this area.
|
|
Rather than querying the worksheet I would use Excel functions to
extract the desired information from the worksheet.
Depending on the nature of the query you might be able to do it with
simple formulas accessing the columns of the downloaded query results.
Consider VLOOKUP, COUNTIF, SUMIF, SUMPRODUCT et al.
Bill Manville
MVP - Microsoft Excel, Oxford, England
|
|
I use the formulae that you suggest extensively in other parts of this
file but they do not suit my requirements here. I have created a pivot
table so that users can drill down and re-arrange data as required. The
pivot tables source data is derived from querying two worksheets. One
worksheet contains invoice data and the order credit note data. In
order to get a pivot table showing net sales I have to run the queries
in seperate worksheets and then combine the two to create the pivot
table source range. My problems as stated is that this works fine until
you change the file location. When changed the queries still address
the worksheets in the original location.
|
|
|