Insert text file into VBA code

[barryleajo]

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.

Please Login or Register  to view this content.

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

[ragulduy]

Are you copying that exactly as is to the text file, I'm guessing that when you read it back in to stSQL the string being assigned would be like:

Please Login or Register  to view this content.

Rather than replacing the usrform variables with the values.

Could you check what the value of stSQL is when you load it from the textfile? (use msgbox stSQL or a watch)

[ragulduy]

I think you might be able to use the callbyname method and evaluate or some form to get round this but I'm not sure exactly how to get it to work. I think the easiest way would be to rewrite your textfile and loop through each line. for example have the first line of your textfile as:
usrformtextbox1
then you can get the value using:
callbyname(txtfil.readline,".value",vbget)
and build the string up that way.

[barryleajo]

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.

[ragulduy]

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

Please Login or Register  to view this content.

Then your code would be

Please Login or Register  to view this content.

[barryleajo]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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?

Please Login or Register  to view this content.

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

[ragulduy]

hmm, yeah, didn't work how I though, try this:

Please Login or Register  to view this content.

[barryleajo]

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

Please Login or Register  to view this content.

to my userform reference i.e.

Please Login or Register  to view this content.

but it now errors with

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

- this gives me the following error

Please Login or Register  to view this content.

I feel we are so close with this

[ragulduy]

See attached macro and text file, this gives me the correct value of stSQL. If you still struggle, I'll need you to upload your workbook so I can test with where everything is, it is too difficult to follow the structure this way.

example.xlstest.txt

[barryleajo]

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.

[Norie]

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.

[ragulduy]

Barry,

No problem - this one has been a learning experience for me to! I'd suggest placing an additional textbox on your userform and making it hidden, then assigning your variable to that textbox then retrieving it the same way as the others.

[barryleajo]

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.

[barryleajo]

@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

[ragulduy]

I think the alternative would be to make a class object with a property you can reference.

see:
http://stackoverflow.com/questions/2...135003#2135003

[barryleajo]

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.