Change excel connection string default dir to current workbook directory?

Question

0.00/5 (No votes)

See more:

C#

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.

Posted 17-Jun-16 4:58am

Member 12405198

Updated 20-Jun-16 18:49pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Member 12405198 · Accepted Answer · 2016-06-20T18:49:00

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!