Import Multiple Colon Delimited Files in to Excel

jarvisaurus

Board Regular
Joined
Nov 26, 2010
Messages
52
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.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
This topic has been discussed a number of times on this forum. If you use the Advanced Search functionality, you should be able to locate a lot of threads on this topic. Post back if you have any problems locating those.
 

jarvisaurus

Board Regular
Joined
Nov 26, 2010
Messages
52
Sorry Joe, I have searched and everything I found was to consolidate all the files into a single sheet or multiple worksheets within the same workbook. I'm fairly new to vba and have tried editing a couple that I found but to no avail. Thanks anyway, I'll keep looking.
 

jarvisaurus

Board Regular
Joined
Nov 26, 2010
Messages
52
I got up to this far:

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 = "filepath"
.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
Set wb = ActiveWorkbook
wb.SaveAs FileName = Left(wb.FileName, Len(wb.FileName) - 4) & ".xls", _
FileFormat:=xlWorkbookNormal
wb.Close SaveChanges:=False
Next i
Else
MsgBox "There were no files found."
End If
End With
Application.ScreenUpdating = True
End Sub

_____________________________________________________

but I am having trouble saving error on this part:

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

Your help is greatly appreciated. Thanks.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What exactly is the error that it is giving you?

If you add the line (before the code causing the error):
MsgBox wb.FileName
this will tell you the file name and may give some clues as to what the problem is.
 

jarvisaurus

Board Regular
Joined
Nov 26, 2010
Messages
52
"Save as Method of workbook class failed" and it also asks me if I want to save over the file "False".
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What does the message box line of code I gave you in my previous post return?
 

jarvisaurus

Board Regular
Joined
Nov 26, 2010
Messages
52
Sorry thought that was the message box...I put that line before the error and I received "runtime error 438...object does not support this property or method".
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
Which version of Excel are you using?
I don't believe that "Application.FileSearch" works in versions of Excel 2007 and later. I'll see what I can whip up.

Is your file path going to always be the same, so it can be automated so that users aren't prompted to select it?
 

jarvisaurus

Board Regular
Joined
Nov 26, 2010
Messages
52
I am using version 2003-2007. Filepath won't always be the same but I can always change the path manually. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,026
Messages
5,526,325
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top