Using VBA to open a tab delimited .txt file to save to .xlsx format

Question

I'm trying to use VBA in Excel to automate turning a .txt file (tab delimited) into a .xlsx file instead. Here's what I have:

Set WB = Workbooks.Open(folder + file, , , 1)
If Right(file, 3) = "txt" Or Right(file, 3) = "xls" Then
    Application.DisplayAlerts = False
    WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 4) + "_" + metricDate + "_.xlsx", _
        FileFormat:=51
    Application.DisplayAlerts = True
Else
    Application.DisplayAlerts = False
    WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 5) + "_" + metricDate + "_.xlsx", _
        FileFormat:=51
    Application.DisplayAlerts = True
End If
WB.Close

This is just a snip of code, of course, and the first part of the code is the most relavant, I think. I've only started checking the transformed .txt files, as they are 10% he size they should be after saving. Turns out, the twenty columns are smushed into three, and all spaces and tabs have been removed. Not sure what is going on, as I don't use VBA very often.

I'm thinking the key is here:

Set WB = Workbooks.Open(folder + file, , , 1)

The 1 at the end signifies tab delimited. Not sure what it will do for the .xls files it also opens, but I'll worry about that next.

Thanks for any pointers you can give.

Edit.

I changed the code around to treat .txt and .xls differently, as I should have done in the first place. Here is the current code:

Dim WB As Workbook
'Dim WBS As Workbooks

If Right(file, 3) = "txt" Then
    Set WB = Workbooks.OpenText Filename:=folder + file, DataType:=xlDelimited, Tab:=True
    Application.DisplayAlerts = False
    WB(1).SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 4) + "_" + metricDate + "_.xlsx", _
        FileFormat:=51
    Application.DisplayAlerts = True
    WB.Close
ElseIf Right(file, 3) = "xls" Then
    Set WB = Workbooks.Open(folder + file)
    Application.DisplayAlerts = False
    WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 4) + "_" + metricDate + "_.xlsx", _
        FileFormat:=51
    Application.DisplayAlerts = True
    WB.Close
Else
    Set WB = Workbooks.Open(folder + file)
    Application.DisplayAlerts = False
    WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 5) + "_" + metricDate + "_.xlsx", _
        FileFormat:=51
    Application.DisplayAlerts = True
    WB.Close
End If

Did you try Workbooks.OpenText? That seems to work for me. Try Workbooks.OpenText Filename:="C:\filenameandpath", DataType:=xlDelimited, Tab:=True — MattB
That's an excellent start, but then I don't see how to save it as an xlsx afterward. There's no built in save functionality that I can see for Workbooks (plural), just for Workbook (singular). — user3264015
Excel's method call to a single workbook is plural Workbooks. It should work okay. — Automate This
I only see you opening 1 workbook so the saveas method should work for you. Dim wb As Workbook wb.SaveAs "fileNameHere", Excel.XlFileFormat.xlOpenXMLWorkbook — Sorceri
You're already using Workbooks to open your file. Just use Set WB = Workbooks.OpenText Filename:=... and you'll have your (one) workbook open. To save, you can use what you're using now or you can save it using the FileFormat:=xlOpenXMLWorkbook property. — MattB

MattB MattB · Accepted Answer · 2014-03-24T18:43:10

Lets try this again with your own code down here where I have more room. Try the following and read my comments above. I think you'll find it works:

'I'm adding this line.  I'm assuming you have it in your code, but just to be certain...
Dim WB As Excel.Workbook
'This line opens your tab delimeted text file.
Set WB = Workbooks.OpenText(Filename:=folder + file, DataType:=xlDelimited, Tab:=True
If Right(file, 3) = "txt" Or Right(file, 3) = "xls" Then
    'This section turns off alerts, saves the workbook opened in the previous step as xlsx and turns alerts back on.
    Application.DisplayAlerts = False
    WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 4) + "_" + metricDate + "_.xlsx", FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
Else
    'Again, this section saves the workbook opened in the previous step as xlsx.
    Application.DisplayAlerts = False
    WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 5) + "_" + metricDate + "_.xlsx", FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
End If
WB.Close

I'm also debating whether or not you really need that If statement. It looks like you're doing the exact same thing and naming the workbook using the same convention. You might not need it. I left it because you didn't ask about it specifically. You could skip it and just save the workbook I think.

Edit: You need the If statement to pick the method you use to open your workbook...

'I'm adding this line.  I'm assuming you have it in your code, but just to be certain...
Dim WB As Excel.Workbook
If Right(file, 3) = "txt" then
    'This line opens your tab delimeted text file.
    Set WB = Workbooks.OpenText(Filename:=folder + file, DataType:=xlDelimited, Tab:=True
Else
    'This line opens your xls and xlsx books
    Set WB = Workbooks.Open(folder + file) 'no additional parameters should be needed
End If
Application.DisplayAlerts = False
WB.SaveAs filename:=folder + milestone + "_" + loadtype + "_" + Left(file, Len(file) - 4) + "_" + metricDate + "_.xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
WB.Close

If you're iterating over a number of these input workbooks you may want to do

Set WB = Nothing

just to be safe.

Edit: I'll let my shame hang out up there... the OpenText method does not return an object, so you'll have to set the WB object using Set WB = Workbooks(file) after you've opened it assuming file is the full filename including extension. My bad on that one.

Using VBA to open a tab delimited .txt file to save to .xlsx format

1 Answers