Tab delimited files saving differently in 2007.

[mbarron]

Is there a setting available to change the save method that Excel2007 uses when saving tab delimited text files?

In 2003, blank rows of data were saved as a double carriage return after a row of data. In 2007 each cell in the blank row is being represented by a tab before a carriage return.

I've tried the various *.txt available and they all use the tab to represent the blank cells.

I can use a macro to save the file, open it in Word, use a find and replace for a string of tab characters and resave the file. The problem is that the people who work with the files "cannot be burdened" with the process. They need to be able to open the file, make a change and re-save the file without having to do anything extra.

[HansV]

I don't know of a way to change the way Excel exports text files. You could use the following macro to create the text file. You can, of course, modify it to prompt the user for the location and filename.

Code: Select all

Sub ExportTabDelimited()
  Const strFile = "Test.txt"
  Dim wsh As Worksheet
  Dim mr As Long
  Dim mc As Long
  Dim r As Long
  Dim c As Long
  Dim f As Long
  Dim strLine As String

  f = FreeFile
  Open strFile For Output As #f

  Set wsh = ActiveSheet
  mr = wsh.Cells.Find(What:="*", SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
  mc = wsh.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column

  For r = 1 To mr
    strLine = ""
    For c = 1 To mc
      strLine = strLine & vbTab & wsh.Cells(r, c)
    Next c
    If strLine = String(mc, vbTab) Then
      Print #f,
    Else
      Print #f, Mid(strLine, 2)
    End If
  Next r

  Close #f
End Sub

[mbarron]

Thanks Hans. I was afraid of that. Unfortunately it needed to be a macro free solution. I have no problems with macro's, but the users are a different story.

I'll have to keep looking. It looks like the program that uploads the text file into the data base will need to be updated to handle the tab rows.