VBA Write Active Sheet Cell Data to External File WITHOUT Quotes

[JohnTravolski]

What I'm trying to achieve should be simple, but Excel keeps making it very difficult for me. All I want is a macro that does exactly the following and nothing else:

• Export all of the data in the active sheet to a text file in the active workbook directory
• The filename should be the name of the active sheet + ".txt"
• Columns should be separated by tab characters
• Rows should be separated by newline characters
• Regardless of what characters are entered in any given cell (including commas, quotes, punctuation, etc.), Excel should NOT BE PUTTING QUOTES OR ANY OTHER CHARACTERS around any of the exported data in the output file.

Please do not recommend anything involving "save as." I've messed around with that before and if you try and save as a tab delimited file, some entries with commas have quotes magically put around them by excel. What we need to do is simply create a text file and write the contents of all cells containing data in the active worksheet as-is into that file with the mentioned delimiters. That's it.

I've found a million examples online and none of them can seem to get past the whole quote problem. Please, please help me.

 

[JohnTravolski]

Do any of you have this problem when you try to export cell data into text files using VBA?

 

[LockeGarmin]

Sub ToTabDelimitedTextFile(WS As Worksheet)

Dim FSO As Object 'Scripting.FileSystemObject '
Dim Stream As Object 'Scripting.TextStream '
Dim Path As String

Dim Row As Range
Dim Cell As Range

Dim Arr As Variant
Dim x As Long

Const IOModeOverwrite As Long = 2
Const IOModeAppend As Long = 8

  Set FSO = CreateObject("Scripting.FileSystemObject")
  Path = FSO.BuildPath(WS.Parent.Path, WS.Name) & IIf(WS.Name Like "*.txt", vbNullString, ".txt")
  
  Set Stream = FSO.OpenTextFile(Path, IOMode:=IOModeOverwrite, Create:=True)
  ReDim Arr(0 To WS.UsedRange.Columns.Count - 1)
  
  For Each Row In WS.UsedRange.Rows
    x = 0
    
    For Each Cell In Row.Cells
      Arr(x) = Cell.Value2
      x = x + 1
    Next Cell
    
    Call Stream.WriteLine(Join(Arr, vbTab))
  Next Row
  
  Stream.Close

End Sub

And you can pass in the Activesheet or any other worksheet object by calling it like this in your code:

Call ToTabDelimitedTextFile(ActiveSheet)

Out of curiosity what kind of system are you integrating the text file into? It seems fairly strange that a system would have a problem with quotes around each piece of data. Typically most systems wouldn't care even if you put quotes around every cell of your data or just those with other tabs in them.

 

[JohnTravolski]

You, my friend, are a lifesaver. Thank you so much! Now, I'll elaborate more on what I'm doing.

I'm trying to interface Excel with some of my C++ projects. Unfortunately, I have found it unreasonably difficult to try and read data directly from Excel files into C++ variables, so I have to manually export spreadsheets as plain text files and then import that data into my C++ variables by using file streams. Unfortunately, the export process was never consistent for me; because a lot of my spreadsheets had inconsistent data types (including plain text and numbers), the appearance of quotes in my final output would never be consistent. So some cells would have their contents in quotes and other wouldn't. Furthermore, when I had commas or quotes in some of that text, the sometimes as many as three pairs of quotes would magically show up in the final output! This inconsistency made it extremely difficult for me to read in the data exactly as I wanted it (the way it appeared in the spreadsheets), because some of my variables would contain quotes that they shouldn't have.

The fact that this has now been automated is a huge time saver and makes it so much easier to get the plain text files I needed. Thank you so much!

 

[LockeGarmin]

Aaah, ok that makes sense why the quotes would be problematic. Unfortunately I have experience working with Excel in C# using another library but not C++ so I don't think I could really help you there (Actually that makes it sound like I have C++ experience, but I don't really have any). When I was testing out the SaveAs method I noticed too that for some reason Tab Delimited files would quote cells with commas. It probably didn't need to be that way but it was still within the spec guidelines that would allow Excel to save and open the document for later use in that format.

I'm glad I could help! Thanks for indulging me in the follow up!

 

[Temelan]

I'm trying to use this code and I'm getting a Run-time error '52': Bad file name or number. What should I change?

This is the code I'm using:

Sub testrun()
Call ToTabDelimitedTextFile(ActiveSheet)
End Sub



Sub ToTabDelimitedTextFile(WS As Worksheet)

Dim FSO As Object 'Scripting.FileSystemObject '
Dim Stream As Object 'Scripting.TextStream '
Dim Path As String

Dim Row As Range
Dim Cell As Range

Dim Arr As Variant
Dim x As Long

Const IOModeOverwrite As Long = 2
Const IOModeAppend As Long = 8

  Set FSO = CreateObject("Scripting.FileSystemObject")
  Path = FSO.BuildPath(WS.Parent.Path, WS.Name) & IIf(WS.Name Like "*.txt", vbNullString, ".txt")
  
  Set Stream = FSO.OpenTextFile(Path, IOMode:=IOModeOverwrite, Create:=True)
  ReDim Arr(0 To WS.UsedRange.Columns.Count - 1)
  
  For Each Row In WS.UsedRange.Rows
    x = 0
    
    For Each Cell In Row.Cells
      Arr(x) = Cell.Value2
      x = x + 1
    Next Cell
    
    Call Stream.WriteLine(Join(Arr, vbTab))
  Next Row
  
  Stream.Close

End Sub

 

[Temelan]

It's happening right after I step into Set Stream = FSO......

 

[Temelan]

For some reason FSO doesn't work for me. However, I found another solution that I'm exploring.