CODEWITHSUNDEEP
excelvbatext
Amostafa
Amostafa

Reputation: 79

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)

Upvotes: 1

Views: 2563

Answers (3)

Luiz Nunes
Luiz Nunes

Reputation: 1

Thanks for the code. I did some small changes so I can reuse it in many different cases and call at any point of the code, using up to 3 different args (you may increase if you wish). like this below example.

note: you may change "totalBananas,EN2003" to anything you find impossible to exist in your files... I used it this way because I am not sure how to declare the args as optional :-p I don't think they are really possible to be optional anyway.

...
Call FixTextFile(file_name, "blabla", "0000", "")
...

Sub FixTextFile(inFile As Variant, fixArg1 As String, fixArg2 As String, fixArg3 As String)
    Dim resArg1, resArg2, resArg3 As Long
    Dim outFile As String
    Dim data As String
            
    If fixArg1 = "" Then fixArg1 = "totalBananas,EN2003"
    If fixArg2 = "" Then fixArg2 = "totalBananas,EN2003"
    If fixArg3 = "" Then fixArg3 = "totalBananas,EN2003"
    
    Open inFile For Input As #1
        
    outFile = inFile & ".alt"
    Open outFile For Output As #2
        
    Do Until EOF(1)
        Line Input #1, data
              
        resArg1 = InStr(1, data, fixArg1)
        resArg2 = InStr(1, data, fixArg2)
        resArg3 = InStr(1, data, fixArg3)
        
        If Trim(data) <> "" And resArg1 < 1 And resArg2 < 1 And resArg3 < 1 Then
            Print #2, data
        End If
              
    Loop
        
    Close #1
    Close #2
        
    Kill inFile
    Name outFile As inFile
        
    MsgBox "File alteration completed!"
End Sub

Upvotes: 0

brettdj
brettdj

Reputation: 55692

Suggest you add further code to

  • test if the file actually exists
  • test if the file is of a valid type for excel to open

code

Dim SourceFile As String
Dim PathFile As String
Set ActiveBook = Application.ActiveWorkbook

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

Do Until EOF(1)
    Line Input #1, SourceFile
    SourceFile = Trim$(SourceFile)
    If Len(Dir(ActiveWorkbook.Path & "\" & SourceFile)) > 0 Then
         Select Case Right$(SourceFile, Len(SourceFile) - InStrRev(SourceFile, "."))
         Case "xls", "xls*"
         Set Source = Workbooks.Open(ActiveWorkbook.Path & "\" & SourceFile)
         Case Else
         Debug.Print "source not valid"
         End Select
    End If
Loop

Upvotes: 1

ManishChristian
ManishChristian

Reputation: 3774

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)

Upvotes: 2

Related Questions