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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
What does the message box line of code I gave you in my previous post return?
 
Upvote 0
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".
 
Upvote 0
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?
 
Upvote 0
I am using version 2003-2007. Filepath won't always be the same but I can always change the path manually. Thank you!
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top