Excel connection string DBQ & DefualtDir from Cell

Asked Feb 13 '18 at 13:48

Active Feb 13 '18 at 13:48

Viewed 1,103 times

I have a connection string:

DSN=Excel Files;
DBQ=C:\Users\matt\Desktop\myfile.xlsm;
DefaultDir=C:\Users\matt\Desktop;
DriverId=1046;
MaxBufferSize=2048;
PageTimeout=5;
ReadOnly=0;

However I want this to pick up different users paths if the file is on their machine.

I have tried the following but I am getting the error:

Cannot update. Database or object is read-only

Attempted Code:

DSN=Excel Files;
DBQ=ThisWorkbook.Worksheets("Control").Range("J1").Value;
DefaultDir=ThisWorkbook.Worksheets("Control").Range("J2").Value;
DriverId=1046;
MaxBufferSize=2048;
PageTimeout=5;
ReadOnly=0;

J1:

C:\Users\matt\Desktop\myfile.xlsm

J2:

C:\Users\matt\Desktop

asked Feb 13 '18 at 13:48

Matt

14,906
27
99
149

Just an idea - change `.Value` to `.Value2` in the attempted code. You never know. – Vityata Feb 13 '18 at 13:51
Btw, does it work, when you try it hardcoded with the connection string? – Vityata Feb 13 '18 at 13:56
@Vityata change both or just one of them? – Matt Feb 13 '18 at 14:02
Yes the top one works when i specify dir and file name – Matt Feb 13 '18 at 14:02
Try with both of them. And if it does not work, write `MsgBox ThisWorkbook.Worksheets("Control").Range("J1")` somewhere in the code. It should give you the correct input in the cells. If not, most probably you are referring to a wrong Worksheet. – Vityata Feb 13 '18 at 14:03
@Vityata No tried both and one of each. – Matt Feb 13 '18 at 14:05
Have you tried with the MsgBox? – Vityata Feb 13 '18 at 14:06
@Vityata What is the code for that – Matt Feb 13 '18 at 14:07
Now the **real** question - where do you put the connection string? – Vityata Feb 13 '18 at 14:08
1

@Vityata Data > Connections – Matt Feb 13 '18 at 14:09
I guess it will not work, because of security issues. But you can totally do what you want to do, if you use a bit VBA and you make the connection there - https://stackoverflow.com/search?q=connection+sql+from+cell – Vityata Feb 13 '18 at 14:14
https://stackoverflow.com/questions/44823865/dynamic-data-connection-string-without-vba – Vityata Feb 13 '18 at 14:15
Possible duplicate of [Dynamic data connection string without VBA](https://stackoverflow.com/questions/44823865/dynamic-data-connection-string-without-vba) – Vityata Feb 13 '18 at 14:15
@Vityata The answer on that question isn't exactly an answer – Matt Feb 13 '18 at 14:35

Excel connection string DBQ & DefualtDir from Cell

0 Answers0