CODEWITHSUNDEEP
excelvbams-query
lala
lala

Reputation: 83

Embedded MS Query connection in Excel

The original task I had was to combine two worksheets dynamically. I discovered Microsoft Query lets me do it by building queries to pull data from the worksheets. However, the data connection stored in the file has references to my local computer. Is there a way to make this "embedded" in the file instead, such that I can share the file and anyone can refresh it? Here's the connection string.

DSN=Excel Files;DBQ=C:\Users\nana\Documents\Model\Project x Model - DRAFT 2015 07 13 0410 - Copy.xlsx;DefaultDir=C:\Users\nana\Documents\Model;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

Upvotes: 1

Views: 495

Answers (1)

user844705
user844705

Reputation:

I think you can set the DSN in the code so something like this should work

currentDir = Replace(WScript.ScriptFullName,WScript.ScriptName,””)

which will give you the directory the excel file was run from and then you can change your DNS params to reflect the variable:

DSN=Excel Files;DBQ=" & currentDir & "Source.xls;DefaultDir=" & currentDir & ";DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;

That should mean the dsn always references the same workbook...

*edit I should mention this came from here http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26514035.html

Upvotes: 2

Related Questions