Importing Text Data into Excel

[SowBelly]

Hello:

I have about 1300 CSV files that are all exactly formatted the same
and named similarly i.e. Bom1.txt, Bom2.txt., etc. I want to convert
each .txt file into an .xls file. Is there a way to do this by either:

1. Creating a macro to convert the .txt file to a .xls file. OR

2. Using VBA to batch convert several .txt files to individual .xls
files. OR

3. Using VBA to batch convert several .txt files to individual sheets
in a single .xls file.

The .xls files could be saved with the same name as the .txt files in
the same directory.

Appreciate your help.

"Pigs can't be humans, but humans can be Pigs!"

Eating at the trough of life.

Oink!

SowBelly

 

[Dave Peterson]

Put all your text files in a dedicated folder (copy, not move--just in case!).

This'll open each .txt file and save it as .xls. Each .txt file gets its own
..xls file. (option #2).

Option Explicit
Sub testme01()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim tempWkbk As Workbook
Dim xlsFileName As String

'change to point at the folder to check
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.txt")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

Application.ScreenUpdating = False

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
Application.StatusBar = "Processing: " & myFiles(fCtr)
Workbooks.OpenText Filename:=myPath & myFiles(fCtr), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1)

Set tempWkbk = ActiveWorkbook

xlsFileName = myPath & myFiles(fCtr)
xlsFileName = Left(xlsFileName, Len(xlsFileName) - 4) & ".xls"

tempWkbk.SaveAs Filename:=xlsFileName, FileFormat:=xlWorkbookNormal
tempWkbk.Close savechanges:=False

Next fCtr
End If

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub

I recorded a macro when I opened a text file to get this portion:

Workbooks.OpenText Filename:=myPath & myFiles(fCtr), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, Comma:=True, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1)

If you need to change the layout, you could record a macro and plop it in this
spot.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm