1

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;
lala
  • 83
  • 2
  • 9

1 Answers1

2

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