Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables.add with sql query text stored in separate text file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables.add with sql query text stored in separate text file
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables.add with sql query text stored in separate text file
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables.add with sql query text stored in separate text fi
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. "StuartBisset" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables.add with sql query text stored in separate text fi
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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Querytables.add with sql query text stored in separate text file
Joel
Please ignore previous message. The VBA didn't work 1st time but its working now. Thanks for your help, much appreciated :-) Stuart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
QueryTables.Add() not returning text from SQL Express table | Excel Programming | |||
Functions, Arrays and number/text stored as text | Excel Worksheet Functions | |||
I open a CSV file with excel, but the text is separate with comma! | Excel Discussion (Misc queries) | |||
Ascii iinput file - separate text from Numbers | Excel Worksheet Functions | |||
Make Table Query into Text File | Excel Programming |