Expected Function or variable" error on the substring .OpenText

abberyfarm

Well-known Member
Joined
Aug 14, 2011
Messages
733
Hi there,

I am trying to use the macro below to loop through text files in a folder. It is supposed to import each file one by one and save it as an excel file.

I'm getting a compiler error message "Expected Function or variable", with highlight on the substring .OpenText from above.

Would anybody know what is wrong?

Thanks


Code:
Sub exampleLoop() 
    Dim fName As String 
    Dim fPath As String 
    Dim newBook As Workbook 
    fPath = "E:\folder\" 
     
     
    If Right(fPath, 1) <> "\" Then 
        fPath = fPath & "\" 
    End If 
     
     
    fName = Dir(fPath & "*.txt") 
    Application.ScreenUpdating = False 
    Do Until fName = "" 
        Set newBook = Workbooks.OpenText(Filename:=fPath & fName, Origin:= _ 
        xlMSDOS, startRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _ 
        , ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=True _ 
        , Space:=True, Other:=True, OtherChar:="^", FieldInfo:=Array(Array(1, 1) _ 
        , Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _ 
        Array(9, 1)), TrailingMinusNumbers:=True) 
         
        newBook.SaveAs Filename:=fPath & Left(fName, Len(fName) - 4) & ".xlsx", _ 
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False 
        newBook.Close savechanges:=False 
         
         'Get next file
        fName = Dir() 
    Loop 
    Application.ScreenUpdating = True 
End Sub
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Reggie74

Board Regular
Joined
Sep 26, 2014
Messages
51
Try this ...

Code:
Sub exampleLoop()


    Dim fName As String
    Dim fPath As String   
   
    fPath = "C:\#data\Funds"
    
     If Right(fPath, 1) <> "\" Then
    fPath = fPath & "\"
    End If
     
    fName = Dir(fPath & "*.txt")
    Application.ScreenUpdating = False
    
    Do Until fName = ""
        Workbooks.OpenText Filename:=fPath & fName, Origin:= _
         xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
           ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False, Comma:=True, _
        Space:=True, Other:=True, OtherChar:="^", FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
          Array(9, 1)), TrailingMinusNumbers:=True
              '   fpa.SaveAs Filename:=fPath & Left(fName, Len(fName) - 4) & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        
        ActiveWorkbook.SaveAs Filename:=fPath & Left(fName, Len(fName) - 4) & ".xlsx", _
                                         FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
                                         
        ActiveWorkbook.Close savechanges:=False                   'Get next file
        
        fName = Dir()
     Loop
     
 Application.ScreenUpdating = True
 
 End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,311
Members
409,862
Latest member
lbisacca
Top