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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,289
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
63,289
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
63,289
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
63,289
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,713
Messages
5,833,269
Members
430,200
Latest member
ADLHMA2022

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
Top