Click here to Skip to main content
15,920,438 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
DefaultDir=C:\SOMEDIRECTORY;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;

Above is the connection string used for Excel to get external data that is used in a pivot table.

In VB I have no trouble setting the directory to ActiveWorkbook.Path so that when I do queries (from VB) or write files, they are found and put in the right place. However when I do the query on my filtered data for the pivot, I cannot find how to have the DefaultDir ("SOMEDIRETORY" above) be changed to whatever directory where the workbook is located. It seems like it should be simple. I'm willing to use a connection file, but it seems like the same problem would still exist. It really mucks up the works because as soon as I put the application on another computer, it can't find the data!

What I have tried:

Using ActiveWorkbook.Path in the connection string (doesn't recognize it).
Took out the DefaultDir - it just puts back the default dir for Excel.
Updated 20-Jun-16 18:49pm

1 solution

I'm proposing answer to my own question again. I can use a dqy file for the query to get the data to be used by the pivots. Since it's a text file, that means I can write the current path into it for DBQ and DefaultDir. I am going to try this, and if it works, then I will accept it as the answer.

THIS DOES NOT WORK. Excel internally saves whatever query was used, but does not look at it dynamically when run the next time.

I am forced to use old version of Excel. I believe in 2010 and later it probably is solvable. The workaround is to tell users they have to install to a given directory. Meanwhile if anyone has a better answer, please put it here!
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900