Dynamic data connection string without VBA

Question

Is it possible to have a connection string depend on a cell value or a named formula in an .xlsx file (i.e. non-macro enabled sheet)?

Background:

Every month I create a new workbook and update the external data source. This way I have workbooks with the results for each month and can always go back to look at a past month's data.

Now, since I use multiple pivot tables on the same data, I use a data connection. The connection string looks something like this, of which the data source is the relevant one.

Provider=Microsoft.ACE.OLEDB.12.0;
User ID=Admin;
Data Source=C:\pathtofile\myworkbook1705.xlsx;
Mode=Share Deny Write;
Extended Properties="HDR=NO;
";
Jet OLEDB // etc. etc //

I usually update the data source path manually, but since I want to expand the project I am doing to multiple other tasks, this manual update procedure becomes tiresome (change 1705 in the file path to 1706 from a cell value).

A few reasons keep me from making the solution in VBA:

Security warning from macro-enabled file.
Users know Excel-sheets as .xlsx and may not recognize .xlm.
Inheritance. My successor, who eventually needs to edit something in the sheet might not know VBA.

I have searched around with no success. Relevant links that unfortunately do not answer the question are linked below.

support.office.com: Change the source data for a PivotTable (Standard click way of updating)
Excel macro to change external data query connections - e.g. point from one database to another (VBA solution)
Microsoft Excel Data Connections - Alter Connection String through VBA (VBA solution to alteration of connection string)

Have you tried the following: https://stackoverflow.com/q/20521056/3560603 — fhossfel, Jul 03 '17 at 15:15

Vityata · Answer 1 · 2017-12-12T15:06:06.680

1

Long story short - NO. It is a giant security issue this way. If you find a way to do it, inform Microsoft immediately, they would be happy to fix it :) .

It would be definitely considered a bad practice by anyone who has ever earned a single dollar with SQL to read the data from a cell in Excel. I mean, the connection string is probably the most sensitive information that you possess. Even if you use VBA, make sure that the passwords are always written by the user in the cells (or through a form) and then erased. Not hardcoded in the code.

Microsoft can be considered bad by plenty of people, but they would never leave such a security hole in their products.

edited Dec 12 '17 at 15:06

answered Jul 03 '17 at 13:41

Vityata

42,633
8
55
100

1

I have not even *considered* the security issues that would have been involved if this was possible. Now that you point it out, I understand fully. Well, at least I appreciate getting a response! I'll make sure to make a proper safe VBA solution :) – Therkel Jul 04 '17 at 13:38
@Therkel - good luck in doing this. In general, if you take a look at my GitRepo here - https://github.com/Vitosh/VBA_personal/blob/45afa5f0bcc9233ac01d1ea86c0f9f321b563824/Connection.vb is some simple way to protect a password. E.g., you may use `str_connection_string` function, to read the output of the user and to pass something else to the database as a password, thus the user does not need to know the real password. Plus, if you make sure that you always erase the entry of the user in the spreadsheet, where he/she puts the password you would be a bit safe. – Vityata Jul 04 '17 at 13:55

Dynamic data connection string without VBA

1 Answers1

Linked