Import Multiple Colon Delimited Files in to Excel

[jarvisaurus]

How do I convert multiple .txt files into excel from a specified path? I'd like each file to be in a separate workbook saved into the same folder. I have hundreds of files each month and converting one by one using the wizard is rather time consuming. Thanks in advance.

 

[Joe4]

Give this a shot:

Sub MyGetFile()
 
    Dim MyPath As String
    Dim MyFile As String
 
    Application.ScreenUpdating = False
'   Set file path
    MyPath = "G:\C\Test\"
 
    MyFile = Dir(MyPath & "*.txt")
 
'   Loop through all text files in path
    Do While Len(MyFile) > 0
 
        Workbooks.OpenText Filename:= _
            MyPath & MyFile, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
            Comma:=False, Space:=False, 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), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _
            (14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
            Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array( _
            27, 1), Array(28, 1), Array(29, 1)), TrailingMinusNumbers:=True
 
        ActiveWorkbook.SaveAs Filename:=Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) - 4) & ".xls"
        ActiveWorkbook.Close False
        MyFile = Dir
    Loop
 
    Application.ScreenUpdating = True
 
    MsgBox "Completed...", vbInformation
 
End Sub

 

[jarvisaurus]

I actually just got it before I seen this but all I had to do was:

Set Wb = ActiveWorkbook
Wb.SaveAs FileName:=Left(Wb.Name, Len(Wb.Name)) & ".xls", _
FileFormat:=xlWorkbookNormal
Wb.Close SaveChanges:=False

Thank you!

 

[Joe4]

Yeah, I am not sure where you got your original code, but the issue was it should have been:
.FullName
and not
.FileName
I was trying to work off the code you posted originally before going back and re-writing it myself (and then assuming nothing).

 

[jarvisaurus]

My macro suddenly stop working...it was working fine last week =( any ideas? All it does is open the first file instead of opening and saving each file as .xls in the current folder.

My current macro:

Sub getfile()
   Dim TargetSht As Worksheet
   Dim i As Integer
   Dim Wks As Worksheet
   Application.ScreenUpdating = False
   Set TargetSht = ThisWorkbook.ActiveSheet
   With Application.FileSearch
      .NewSearch
      .LookIn = "C:\Documents and Settings\user\Desktop\test"
      .SearchSubFolders = False
      .FileName = "*.txt*"
      If .Execute() > 0 Then
         MsgBox "There were " & .FoundFiles.Count & " file(s) found."
         For i = 1 To .FoundFiles.Count
            Workbooks.OpenText FileName:= _
            .FoundFiles(i), Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
            xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
            Comma:=False, Space:=False, 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), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _
            (14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
            Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array( _
            27, 1), Array(28, 1), Array(29, 1)), TrailingMinusNumbers:=True
            ActiveWorkbook.SaveAs FileName:=Left(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName)) & ".xls"
            ActiveWorkbook.Close SaveChanges:=False
         Next i
      Else
         MsgBox "There were no files found."
      End If
   End With
   Application.ScreenUpdating = True
End Sub

 

[Joe4]

If it was working last week and if you have not changed the macro any, then the most likely culprit is that something with your system/files changed. Double-check your paths and files to make sure it is looking in the correct place.

If you cannot figure it out, try stepping through your macro line-by-line using the F8 button and watch it each step of the way so you can see what it is doing (if you hover over variables as you come to that line, it will tell you what their values are).

 

[jarvisaurus]

Thank you! User error! think my coworker copied over the shortcut...I ran it manually and it worked. I just need to put some sort of password on it =)