Search code examples
excelvbatext

Ignoring blank lines and spaces in text files when reading

I have a text file with file addresses listed line by line.

Sometimes, however, the users go in there and accidentally add a space or a blank line between the addresses and that crashes the entire code.

How could I avoid this when reading the file using VBA?

This is the current block used to open the text file and read addresses line by line:

Set ActiveBook = Application.ActiveWorkbook

PathFile = ActiveWorkbook.Path & "\FilePaths.txt"
Open PathFile For Input As #1

Do Until EOF(1)
    Line Input #1, SourceFile            
    Set Source = Workbooks.Open(SourceFile)
Solution

  • You will add two lines which will ignore blank lines and spaces like this: 

    Line Input #1, SourceFile
SourceFile = Trim(SourceFile)    '~~> This will trim all the spaces
If Not SourceFile = "" Then      '~~> This will check if lines is empty
    Set Source = Workbooks.Open(SourceFile)