title: Using VBA to open a tab delimited .txt file to save to .xlsx format
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>I'm thinking the key is here:</p>
  
  <pre><code>Set WB = Workbooks.Open(folder + file, , , 1)
  </code></pre>
  
  <p>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.</p>
  
  <p>Thanks for any pointers you can give.</p>
  
  <hr>
  
  <p>Edit.</p>
  
  <p>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:</p>
  
  <pre><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
  </code></pre>