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
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