Getting text file into a VBA string variable

[Don Wiss]

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>.

 

[Francis Ang]

Try this

Open "C:\Myfile.txt" for input as #1
Input #1, ShortText
Close #1

Mystring=ShortText

 

[Robin Hammond]

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]

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>.

 

[Don Wiss]

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>.

 

[Don Wiss]

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>.

 

[Robin Hammond]

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]

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>.

 

[Robin Hammond]

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

 

[Tom Ogilvy]

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.

..

 

[Don Wiss]

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>.