Getting text file into a VBA string variable

#1 01-05-2005, 07:06 PM

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 01-05-2005, 08:06 PM

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 01-05-2005, 08:06 PM

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" <donwiss@no_spam.com> wrote in message
news:pfuot05mk3f4trsar5brq0n7n1moj4usrr@4ax.com...
> 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 01-05-2005, 08:06 PM

On 5 Jan 2005, Francis Ang <FrancisAng@discussions.microsoft.com> 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 01-05-2005, 08:06 PM

On 5 Jan 2005, Francis Ang <FrancisAng@discussions.microsoft.com> 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 01-05-2005, 09:06 PM

On Thu, 6 Jan 2005, Robin Hammond <rjNOrhSPAM@PLEASEnetvigator.com> 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 01-05-2005, 10:06 PM

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" <donwiss@no_spam.com> wrote in message
news:1k4pt0hnsrcjbn9qc1suhtp5b904j3coa4@4ax.com...
> On Thu, 6 Jan 2005, Robin Hammond <rjNOrhSPAM@PLEASEnetvigator.com> 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 01-06-2005, 12:06 AM

On Thu, 6 Jan 2005, Robin Hammond <rjNOrhSPAM@PLEASEnetvigator.com> 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 01-06-2005, 12:06 AM

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" <donwiss@no_spam.com> wrote in message
news:fmept0t3kdvom1p16eg1k5r04gprgcd1o7@4ax.com...
> On Thu, 6 Jan 2005, Robin Hammond <rjNOrhSPAM@PLEASEnetvigator.com> 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 01-06-2005, 09:06 AM

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" <donwiss@no_spam.com> wrote in message
news:pfuot05mk3f4trsar5brq0n7n1moj4usrr@4ax.com...
> 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 01-06-2005, 08:06 PM

On Thu, 6 Jan 2005 08:51:55 -0500, Tom Ogilvy <twogilvy@msn.com> 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>.

Welcome to the Excel Forum

Forum Statistics