Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting text file into a VBA string variable
I'd like to read a short text string in from the hard disk and assign it to
a VBA string variable. I hunted around in the VBA help file, and couldn't find anything on getting files. I don't want to open a file and put it onto a Worksheet and then read it from there. (And then close the Sheet.) A bit of overkill for a four character long file. Don <donwiss at panix.com. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting text file into a VBA string variable
Try this
Open "C:\Myfile.txt" for input as #1 Input #1, ShortText Close #1 Mystring=ShortText "Don Wiss" wrote: I'd like to read a short text string in from the hard disk and assign it to a VBA string variable. I hunted around in the VBA help file, and couldn't find anything on getting files. I don't want to open a file and put it onto a Worksheet and then read it from there. (And then close the Sheet.) A bit of overkill for a four character long file. Don <donwiss at panix.com. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting text file into a VBA string variable
You need something like this for binary access
Sub ReadFile() Dim hFile As Long Dim strFile As String Dim strData As String * 4 hFile = FreeFile strFile = "C:\Something.txt" Open strFile For Binary Access Read As hFile Len = 4 Get hFile, 1, strData Close hFile MsgBox strData End Sub or you could look at ReadLine in the filesytstemobject library Robin Hammond www.enhanceddatasystems.com "Don Wiss" wrote in message ... I'd like to read a short text string in from the hard disk and assign it to a VBA string variable. I hunted around in the VBA help file, and couldn't find anything on getting files. I don't want to open a file and put it onto a Worksheet and then read it from there. (And then close the Sheet.) A bit of overkill for a four character long file. Don <donwiss at panix.com. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting text file into a VBA string variable
On 5 Jan 2005, Francis Ang wrote:
"Don Wiss" wrote: I'd like to read a short text string in from the hard disk and assign it to a VBA string variable. Try this Open "C:\Myfile.txt" for input as #1 Input #1, ShortText Close #1 Mystring=ShortText Thank you. Input worked. I had been trying with Get in the middle and it wasn't working. Don <donwiss at panix.com. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting text file into a VBA string variable
On 5 Jan 2005, Francis Ang wrote:
Try this Open "C:\Myfile.txt" for input as #1 Input #1, ShortText Close #1 Mystring=ShortText This works fine for my first use. But then I was trying to read in a multi-line file, where each line is delimited by a NL/LF pair. (Which makes it really just a vector in the file.) But the input just gets the first line. Adding Do While Not EOF and concatenating each string (with NL/LFs) has two problems: (1) There are commas in the text that are not delimiters, and (2) it strips away leading blanks in the front of each line. Now I could create a file with double quotes around each line, but this is an existing txt file that I'd like to still be readable in Notepad. I suspect the answer for this is to use the Get statement. But when I try this I get a Run-time error '54': Bad file mode. Don <donwiss at panix.com. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting text file into a VBA string variable
On Thu, 6 Jan 2005, Robin Hammond wrote:
You need something like this for binary access Sub ReadFile() Dim hFile As Long Dim strFile As String Dim strData As String * 4 hFile = FreeFile strFile = "C:\Something.txt" Open strFile For Binary Access Read As hFile Len = 4 Get hFile, 1, strData Close hFile MsgBox strData End Sub How would I expand this to read in a file of unknown length? or you could look at ReadLine in the filesytstemobject library Okay. But this stops at the first newline character. I'd like to read those characters in also, just as the file exists on the disk. Don <donwiss at panix.com. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting text file into a VBA string variable
Don,
try this Sub ReadMultiLines() 'requires a reference to the Windows Script Host Object Model Dim FSO As FileSystemObject Dim tsInput As TextStream Dim strFile As String Dim strLine As String strFile = "D:\DspfxId.txt" Set FSO = New FileSystemObject Set tsInput = FSO.OpenTextFile(strFile, 1) Do While Not tsInput.AtEndOfStream strLine = tsInput.ReadLine Debug.Print strLine Loop tsInput.Close Set FSO = Nothing End Sub Robin Hammond www.enhanceddatasystems.com "Don Wiss" wrote in message ... On Thu, 6 Jan 2005, Robin Hammond wrote: You need something like this for binary access Sub ReadFile() Dim hFile As Long Dim strFile As String Dim strData As String * 4 hFile = FreeFile strFile = "C:\Something.txt" Open strFile For Binary Access Read As hFile Len = 4 Get hFile, 1, strData Close hFile MsgBox strData End Sub How would I expand this to read in a file of unknown length? or you could look at ReadLine in the filesytstemobject library Okay. But this stops at the first newline character. I'd like to read those characters in also, just as the file exists on the disk. Don <donwiss at panix.com. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting text file into a VBA string variable
On Thu, 6 Jan 2005, Robin Hammond wrote:
try this Sub ReadMultiLines() 'requires a reference to the Windows Script Host Object Model Looking at the list under VBA's Tools - References... I'm not sure just which is the one I need to check. Don <donwiss at panix.com. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting text file into a VBA string variable
In my list of references it shows up exactly as I referred to it in the
comment. If it is not there, browse for wshom.ocx in your system32 folder. Robin Hammond www.enhanceddatasystems.com "Don Wiss" wrote in message ... On Thu, 6 Jan 2005, Robin Hammond wrote: try this Sub ReadMultiLines() 'requires a reference to the Windows Script Host Object Model Looking at the list under VBA's Tools - References... I'm not sure just which is the one I need to check. Don <donwiss at panix.com. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting text file into a VBA string variable
Sub Tester4()
Dim fname As String Dim sVal As String fname = "C:\xlText\MyText.txt" sVal = OpenTextFileToString2(fname) Debug.Print sVal End Sub Function OpenTextFileToString2(ByVal strFile As String) As String ' RB Smissaert - Author Dim hFile As Long hFile = FreeFile Open strFile For Input As #hFile OpenTextFileToString2 = Input$(LOF(hFile), hFile) Close #hFile End Function Produced: this is line 1 This is line 2 This is line 3 This is line 4 which is what was in the text file. .. -- Regards, Tom Ogilvy "Don Wiss" wrote in message ... I'd like to read a short text string in from the hard disk and assign it to a VBA string variable. I hunted around in the VBA help file, and couldn't find anything on getting files. I don't want to open a file and put it onto a Worksheet and then read it from there. (And then close the Sheet.) A bit of overkill for a four character long file. Don <donwiss at panix.com. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting text file into a VBA string variable
On Thu, 6 Jan 2005 08:51:55 -0500, Tom Ogilvy wrote:
Function OpenTextFileToString2(ByVal strFile As String) As String ' RB Smissaert - Author Dim hFile As Long hFile = FreeFile Open strFile For Input As #hFile OpenTextFileToString2 = Input$(LOF(hFile), hFile) Close #hFile End Function Works like a charm. Nice and simple. I can feed the output of this function directly into a user form textbox. Thanks, Don <donwiss at panix.com. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text strings from a variable string | New Users to Excel | |||
Setting a string variable as the file name | Excel Discussion (Misc queries) | |||
find a variable text string | Excel Discussion (Misc queries) | |||
How to activate a file when the filename is represented by a string variable | Excel Programming | |||
Using a variable string in a file save path | Excel Programming |