All

How can I get my querytables.add statement to refer to sql query text
that is stored in a text (.txt) file and is longer than 255 characters
(about 1600 characters actually). I'm using Excel 2003.

My VBA so far is this...

With ActiveSheet.QueryTables.Add(Connection:=ConnText,
Destination:=Range("B28"))
.CommandText = <need to get this to refer to c:\SqlQuery.txt
.Name = "Dimensions 1 EDI BellsMills TB"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

I have tried to use the following vba to read from the text file
(QuerySelect = C:\SqlQuery.txt, whilst QueryText & strSingleLine are
both string variables). The code works but because the string is
longer that 255 characters is doesn't pickup all the text.

Open QuerySelect For Input As FileNum
Do While Not EOF(FileNum)
Line Input #FileNum, strSingleLine
QueryText = QueryText + strSingleLine
Loop


Ideally what I want is my macro to refer directly to the text file.
Can this be done? Is there a way of doing something similar? Does
anyone have any suggestions?

Alternatively, I have also tried playing around with longer string
variables (eg Dim QueryText as string * 2000) but as soon as I write
anything to QueryText the remainder of the variable is populated with
spaces so when I try to do [QueryText = QueryText + strSingleLine] it
doesn't work. How could I successfully read the entire contents of
the file into a string variable?

As always, many thanks in advance

Stuart