Evalute a string with variables

[foxbldr]

Hi All,

I have stored a sql script in text file which look like following:

Select Code, CodeDesc, EffDate from DTran where
Code =' StrCode '

I read contents of text file into a string and pass the string to a QueryTable object. StrCode is a string variable in my module.

If StrCode is '0034', how do I evalaute my sql string so that it reads :

Select Code, CodeDesc, EffDate from DTran where
Code ='0034 '

Any help is much appreciated.

 

[SkipVought]

Hi,

You will have to replace the value between the tics with the value in StrCode; kind of klunky.

WHY do you have this SQL in a text file? What's the objective here?



Skip,
[sub]
[red][/red]
[/sub]

 

[TonyJollans]

Hi foxbldr,

If what you want to do is to always replace the (sub-)string "StrCode" with the value of the variable StrCode then you can use:

strQuery = Replace(strQuery, "StrCode", StrCode)

but there is no (simple) general purpose way of in-string variable substitution and if you have other variables you will need to hard code the names of each.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[foxbldr]

Skip:

I am changing code behind a large excel book which extracts data from Ms Access 97 queries.

ActiveSheet.QueryTables.Add(Connection:= _
        "FINDER; T:\sql_queries\prod\DailyTran.dqy",....

Management wants to get rid all Ms Access DBs but want to keep all excel workbooks and functionality.

I am removing all hard-coded path and putting sql statements in simple text files for easy maintenace and auditing purposes. Most of the sql statements have variables and variables get the values from the certain cells in worksheet (i.e. Current Date)

Tony:

Yes, you are right! I have mulitple variables and need to write Replace command for every variable. Perhaps I can use an array of variables then go through the array while issuing Replace command.

Btw, I used WorksheetFunction.Substitute method and it works in the similar way.


Thank you for your quick replies.

Foxbldr