Querytables.add with sql query text stored in separate text file

[StuartBisset]

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

 

[Joel]

first an excel cell can contain up to 32,536 characters. You can only see
the 1st 256 characters. You simply need to open the text fiel with the sql
and read the data.

If you data is on more than one line in the file this may be a problem. You
may have to remove the CR (carraiage return) and LF (LineFeed) from the string


Sub GetQuery()

ReadFile = Application _
.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", _
Title:="Select Read File")
If ReadFile = False Then
MsgBox ("No file Selected - Exiting Macro")
End If

Set fs = CreateObject("Scripting.FileSystemObject")
Set fin = fs.OpenTextFile(ReadFile, _
ForReading, TristateTrue)

Sql = fin.readall
Sql = Replace(Sql, vbCrLf, "")

fin.Close

With ActiveSheet.QueryTables.Add(Connection:=ConnText, _
Destination:=Range("B28"))
.CommandText = Sql
.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


End Sub

 

[StuartBisset]

Hi Joel

Thanks for your prompt reply. Do I need to tick a reference library
(Tools > References in the VBE) to use the code I've quoted below as
OpenTextFile, ReadFile, ForReading and TristateTrue are not recognised
methods/properties in my VBE?

Set fs = CreateObject("Scripting.FileSystemObject")
Set fin = fs.OpenTextFile(ReadFile, _
ForReading, TristateTrue)

Kind regards

Stuart

 

[Joel]

You don't need a reference library. You only need libraries if you are using
other office products functions. For example ADO objects are a Microsoft
Access library functions. Excel has the File I/O functions built into the
excel library.

 

[Joel]

I checked my Excel VBA References and 4 references automatically are included
on all computers I've worked with

Visual Basic for Applications
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library

I've seen problems when these libraries where no pointing to the correct DLL
file. Usually this happens when you upgrade from one verion of Office to
another or from XP to Vista. I also have seen this problem when you use

 

[StuartBisset]

Joel

Please ignore previous message. The VBA didn't work 1st time but its
working now.

Thanks for your help, much appreciated

Stuart