Awesome Tajan! That works. Thank you! Here's the final script.
Sub ImportDATFileLoopAllFilesInFolder2B()
'PURPOSE: To loop through all DAT files in a user specified folder and perform a set task on them
Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim myExtension2 As String
Dim FldrPicker As FileDialog
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With
'In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings
'Target File Extension (must include wildcard "*")
myExtension = "*.dat*"
myExtension2 = ".xlsx"
'Target Path with Ending Extention
myFile = Dir(myPath & myExtension)
'Just to show how we auto adjust the width of column A.
Columns("A:A").EntireColumn.AutoFit
'Loop through each Excel file in folder
Do While myFile <> ""
DoEvents
'We now import the selected text file, and data is
'inserted in a new spreadsheet. If you want to use
'another delimiter, you must change "Semicolon:=True"
'to "Semicolon:=False" and set another delimiter
'(e.g. "Tab") to True.
Workbooks.OpenText Filename:=myPath & myFile, _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:=Chr(124), _
TrailingMinusNumbers:=True, Local:=True
'Save and Close Workbook
With ActiveWorkbook
.SaveAs myPath & Left(myFile, InStrRev(myFile, ".") - 1) & myExtension2, _
FileFormat:=xlOpenXMLWorkbook
.Close
End With
'Ensure Workbook has closed before moving on to next line of code
DoEvents
'Get next file name
myFile = Dir
Loop
'Message Box when tasks are completed
MsgBox "Task Complete!"
ResetSettings:
'Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Bookmarks