Insert text file into VBA code

Hi Everyone
I have an SQL query statement built-up in Excel 2010 VBA, which updates a database, similar to below. I am using ADO and the command .Execute(stSQL) from within Excel VBA - works fine.

stSQL = "UPDATE Organisation " _
   & "SET Org_Ref = '" & usrformtextbox1.Value & "', " _
        & "Org_Name = '" & usrformtextbox2.Value & "', " _
        & "Contact_Lastname = '" & usrformtextbox3.Value & "', " _
        & "Contact_Firstname = '" & usrformtextbox4.Value & "', " _
        & "Org_Contact_Email = '" & usrformtextbox5.Value & "', " _
        & "Org_Contact_Tel = '" & usrformtextbox6.Value & "' " _
    & "WHERE Org_ID = " & prikey

I want to take this 'hard coded' query out of Excel, save it, and load it into the variable stSQL from a text file, when require, without any user intervention.

I have copy/pasted and saved this into a text file and I can read this back into the variable stSQL. In this way the query will NOT NOW RUN with .Execute(stSQL). It throws an error which I think comes SQL Server "Incorrect syntax near '&' "

If I manually "Insert" the same text file from the Excel VBE, Insert menu, File ... command, the variable stSQL/query runs OK.
I want the text file to be "imported/inserted/loaded" by VBA, assigned to the variable stSQL and run via VBA code with no user input.
Can anyone help with this please.

I have previously posted this http://stackoverflow.com/questions/1...lude-variables without any success.

With thanks
Barry

Hi yudlugar
Thanks for responding this is driving me nuts!
The value of stSQL is the string literal of the text file contents, so no it doesn't recognise the contents of the textboxes.
It doesn't have the double quotes though, as you have shown.

Sorry, the double quotes would be how the equivalent of how to assign stSQL like that from vba.
I would suggest writing your textfile like this

UPDATE Organisation 
SET Org_Ref = '
VAR usrformtextbox1 
 ', Org_Name = ' 
VAR usrformtextbox2
', Contact_Lastname = ' 
VAR usrformtextbox3
', Contact_Firstname = ' 
VAR usrformtextbox4 
', Org_Contact_Email = ' 
VAR usrformtextbox5
', Org_Contact_Tel = 'u
VAR srformtextbox6
' WHERE Org_ID = prikey

Then your code would be

Sub macro_1()
Dim fs, txtfile, stSQL, str
Set fs = CreateObject("scripting.filesystemobject")
Set txtfile = fs.opentextfile("E:\test.txt")
Do Until txtfile.atendofstream
    str = txtfile.readline
    If Left(str, 3) = "VAR" Then
        stSQL = stSQL & CallByName(Split(str, " ")(1), ".value", VbGet)
    Else
        stSQL = stSQL & str
    End If
Loop
End Sub

OK yudgular - this is an exciting way forward for me but it doesn't quite work yet.
It is bugging out at the first VAR line in the text file with "Object required" run-time error.
The string stSQL has built-up as below when the error occurs

"SELECT * FROM Organisation ORDER BY Org_Ref"

I have put my fully qualified userform/textbox references in the text file e.g.

usrOrg.txtOrgCliOrgRef

Is your syntax in the brackets OK?
I have never used CallByName before but that (1) after the Split function just somehow looks a bit uncomfortable?

CallByName(Split(str, " ")(1), ".value", VbGet)

When it bugs out there are values in the fully qualified text box controls.
I do appreciate your help.

Good call but ...
To get it going in my code I have:
..changed the first VAR in the textfile to txtOrgCliOrgRef i.e. just the textbox name without the userform name
..changed the reference to the userform in your code

userform1.Controls(...

to my userform reference i.e.

usrOrg.Controls(...

but it now errors with

Object doesn't support this property or method

I have also tried putting quotes around the SPLIT return value although I am not sure if the syntax of this is right

CallByName(usrOrg.Controls(Chr(34) & Split(str, " ")(1)) & Chr(34), ".value", VbGet)

- this gives me the following error

Could not find the specified object

I feel we are so close with this

OK yudlugar - nearly bingo!
I had an errant "." in front of the "value" argument which was hashing things up.

String builds OK other than for the variable prikey - which isn't a textbox.
Can you see an easy way of also including (normal) variables in addition to controls within your code?
Presumably I will also split any (normal) variables out to be other VAR lines in the textfile?

Great job, I would never have come across this without your help and experience.

Barry

Just curious, have you considered saving the SQL as a parameter query in the database.

Then from VBA/ADO you can pass the parameters to the query from the userform and then run it.

Norie
I have only just started using SQL Server and so no - at least not yet.
I'm using a switch from Access to SQL Server as an excuse to 'streamline' my existing code a bit.
I'm afraid 'parameter queries' and 'storing queries in the database' are a bit alien to me at the moment.
I would appreciate any 'beginner tutorials/references' you could point me at though - in anticipation.
Thank you for your interest.

@yudlugar
Thanks for your comments again.
I don't think including additional textboxes is an option for me.
I'm 'converting' some existing code and there are many userforms and many bits of existing SQL queries that that I'm using.
Unless you have any further advice I could probably deal with it through the naming conventions of my variables/controls. Then use 'Select Case' or something within the code you have provided for me.

I'll leave this thread for a short while to see if any further ideas come back and then close it as Solved.
You have been an immense help and I can probably make some progress from here now.
Norie has also suggested another idea which I ought to explore.
Thank you

Mmm - not as simple as I thought.
How do you get the value of a common-or-garden variable to be recognised from its name in the textfile?
Thanks for the pointer on Class Objects - I'll take a look.