Edit OLE DB Query

[jodjim]

Is it possible to write a procedure in Excel to change the OLE DB query based on a value entered?

For example, if it's April 05 the Data Source in the following script changes from Mar05 to Apr05. There are more than one Company with each one on a separate sheet.

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Mar05\Company01.xls


Thanks

 

[01Patrik]

quite possible.

define the connection string as a string variable, and combine with if, case or any other method to return "april05" as a string value.

'This can by calculated on DATE, entered like string (like below) or retrieved from a cell, possibilities are unrestricted

MyMonth = "April05" 

ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\" & myMonth & "\Company01.xls"

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[jodjim]

Thanks, Patrik. I'll give it a try.

 

[jodjim]

Hi Patrik,

Here's what I came up with.
Sheets(tSht).QueryTables(1).Connection
"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & tSource

The values for tSht and tSource (path/filename) seem to be okay but I'm getting Error #1004. I tried other options suggested in an Excel book but it uses user-defined functions which is another challenge for me. Thanks a lot for your help.

jodjim