This document discusses four main ways to read text files in VBA: 1) the Input # statement, which reads data into variables but does not preserve leading/trailing spaces; 2) the Input function, which gets individual characters but requires knowing the number of characters; 3) using file system object functions to read line-by-line while preserving formatting; and 4) adding the Microsoft Scripting Runtime reference to use file system object functions.
Copyright:
Attribution Non-Commercial (BY-NC)
Available Formats
Download as DOC, PDF, TXT or read online from Scribd
This document discusses four main ways to read text files in VBA: 1) the Input # statement, which reads data into variables but does not preserve leading/trailing spaces; 2) the Input function, which gets individual characters but requires knowing the number of characters; 3) using file system object functions to read line-by-line while preserving formatting; and 4) adding the Microsoft Scripting Runtime reference to use file system object functions.
This document discusses four main ways to read text files in VBA: 1) the Input # statement, which reads data into variables but does not preserve leading/trailing spaces; 2) the Input function, which gets individual characters but requires knowing the number of characters; 3) using file system object functions to read line-by-line while preserving formatting; and 4) adding the Microsoft Scripting Runtime reference to use file system object functions.
Copyright:
Attribution Non-Commercial (BY-NC)
Available Formats
Download as DOC, PDF, TXT or read online from Scribd
This document discusses four main ways to read text files in VBA: 1) the Input # statement, which reads data into variables but does not preserve leading/trailing spaces; 2) the Input function, which gets individual characters but requires knowing the number of characters; 3) using file system object functions to read line-by-line while preserving formatting; and 4) adding the Microsoft Scripting Runtime reference to use file system object functions.
Copyright:
Attribution Non-Commercial (BY-NC)
Available Formats
Download as DOC, PDF, TXT or read online from Scribd
Download as doc, pdf, or txt
You are on page 1/ 2
Read Data frm Text Files (VBA)
Reading Text Files using VBA is one of the major development activity of programmers. There are multiple ways to read a file 1. 2. 3. 4. Input # Statement Input Function Get Function File System Object Functions
Input # Statement Dim MyString, MyNumber Open "c:\test.txt" For Input As #1 ' Open file for input. Do While Not EOF(1) ' Loop until end of file. Input #1, MyString, MyNumber ' Read data into two variables.
Debug.Print MyString, MyNumber ' Print data to the Immediate window. Loop Close #1 ' Close file.
However, the bug here is Input # does not take the leading or trailing spaces with it. That is, ' My Name is ' becomes 'My Name is'. This will not be the correct one as we need to get the spaces also Then Input function comes handy Dim MyChar Open "c:\test.txt" For Input As #1 ' Open file. Do While Not EOF(1) ' Loop until end of file. MyChar = Input(1, #1) ' Get one character. Debug.Print MyChar ' Print to the Immediate window. Loop Close #1 ' Close file.
However, the bug here will be the input that one needs - the number of characters to be extracted. The obvious option is File system object Sub Read_text_File() Dim oFSO As New FileSystemObject Dim oFS Set oFS = oFSO.OpenTextFile("c:\textfile.TXT") Do Until oFS.AtEndOfStream sText = oFS.ReadLine
Loop End Sub
This will read line-by line. all you need to add the Microsoft Scripting Runtime in the reference
