VBA, reading text file, assign each line of text file to variable

Question

I am reading login information from my C drive using VBA.

Sub ReadFile()
  Dim iTxtFile As Integer
  Dim strFile As String
  Dim strFileText As String
  strFile = "C:\temp\logon.txt"
  iTxtFile = FreeFile
  Open strFile For Input As FreeFile
  strFileText = Input(LOF(iTxtFile), iTxtFile)
  Debug.Print strFileText
  Close iTxtFile
End Sub

in the immediate window I get :

username1234
password1234

can I assign these to a variable? I thought about looping through strings to assign to variable but these seem to be 1 string. How can I work around this?

Thanks.

You can read the file line-by-line https://stackoverflow.com/questions/11528694/read-parse-text-file-line-by-line-in-vba — Tim Williams, Aug 02 '23 at 15:42

score 0 · Accepted Answer · answered Aug 02 '23 at 15:44

This should work:

Sub ReadFile()

  Dim iTxtFile As Integer
  Dim strFile As String
  Dim strFileText As String

' Added Lines
  Dim strLines() As String
  Dim strUsername As String
  Dim strPassword As String
'

  strFile = "C:\temp\logon.txt"
  iTxtFile = FreeFile
  Open strFile For Input As FreeFile
  strFileText = Input(LOF(iTxtFile), iTxtFile)

' Added Lines
  strLines = Split(strFileText, vbCrLf)
  strUsername = strLines(0)
  strPassword = strLines(1)
'

  Debug.Print strFileText
  
' Added Lines
  Debug.Print "Username: " & strUsername
  Debug.Print "Password: " & strPassword
'

  Close iTxtFile
  
End Sub

CAVEAT: The above solution depends on the file being a Windows file where lines are delimited by carriage return and line feed. It also depends on the file having no extra spaces or leading blank lines.

Note: Please don't store usernames and passwords in plain text files.

thank you for this.. is there anyway to make this a function? I need to pass `strUsername` and `strPassword` to another Sub routine. — Jonnyboi, Aug 02 '23 at 16:58
@Jonnyboi That is a different question. Perhaps you can give it a go on your own, and if you can't figure it out on your own, ask a new question of Stackoverflow. Although, have a look at this: https://stackoverflow.com/questions/5339807/return-multiple-values-from-a-function-sub-or-type when you get a chance. It might help. And, as always, if your original question has been answered, it is polite click the check mark to accept the answer. — StoneGiant, Aug 02 '23 at 18:33

score 0 · Answer 2 · answered Aug 02 '23 at 15:47

0

Split strFileText so that you can get the values in an array. Then print the same.

data = Split(strFileText, vbCrLf)
Debug.Print "User : " & data(0)
Debug.Print "Password : " & data(1)

answered Aug 02 '23 at 15:47

Sudipto Bhattacharya

99
1
5

score 0 · Answer 3 · answered Aug 23 '23 at 06:40

Here is a simple way of reading a text file character by character. This code relies on the fact that the file is a Windows (CRLF) coded file. This means that each line is terminated by carriage return (CR) - CHR(13) - and line feed - CHR(10) - (LF). As the routine is implemented as a Public function it can be called from an Excel sheet eg =fnGetLine(2)

Option Explicit

Sub sbTest()
 MsgBox fnGetLine(2)
End Sub

Public Function fnGetLine(iLineNum As Integer) As String

    Dim sLines(99) As String
    If iLineNum > 99 Then Exit Function
    
    Dim iLines As Integer
    Dim sChar As String
    Dim ii As Long
    Dim iTxtFile As Integer
    Dim strFileName As String
    Dim strFileText As String
    
    strFileName = ActiveWorkbook.Path & "\username.txt"
    iTxtFile = FreeFile
    Open strFileName For Input As FreeFile
    strFileText = Input(LOF(iTxtFile), iTxtFile)
    Close iTxtFile
    
    iLines = 1
    sLines(1) = ""

    For ii = 1 To Len(strFileText) Step 1
        sChar = Mid(strFileText, ii, 1)
        If sChar = Chr(13) Then
            If Mid(strFileText, ii + 1, 1) = Chr(10) Then
                iLines = iLines + 1
                sLines(iLines) = ""
                ii = ii + 1
            Else
                Exit Function ' error
            End If
        Else
            sLines(iLines) = sLines(iLines) & sChar
        End If
    Next ii

    fnGetLine = sLines(iLineNum)

End Function

The encodings supported by Notepad are - ANSI - American National Standards Institute, UTF-16 LE - Little endian, UTF-16 BE - Big endian, UTF-8 - the default on Windows 11, UTF-8 with BOM

BOM = byte order mark - https://learn.microsoft.com/en-us/globalization/encoding/byte-order-mark

https://en.wikipedia.org/wiki/Windows_code_page

Microsoft adopted a Unicode encoding (first the now-obsolete UCS-2, which was then Unicode's only encoding), i.e. UTF-16 for all its operating systems from Windows NT onwards, but additionally supports UTF-8 (aka CP_UTF8) since Windows 10 version 1803.

VBA, reading text file, assign each line of text file to variable

3 Answers3