pittbb
asked on
VBA: Saving Excel as Text file
I want to save an excel file as a text file in VBA:
xlFile.SaveAs filename:=outputFile, FileFormat:=xlText
The problem is it is enclosing the double quote character in quotes in the saved file. For example, suppose the text in cell A1 is "Test." Then the saved test file will contain """Test.""" How do I get it to save as a text file without enclosing double quote characters in quotes?
xlFile.SaveAs filename:=outputFile, FileFormat:=xlText
The problem is it is enclosing the double quote character in quotes in the saved file. For example, suppose the text in cell A1 is "Test." Then the saved test file will contain """Test.""" How do I get it to save as a text file without enclosing double quote characters in quotes?
ASKER
The documentation on saveas is:
expression.SaveAs(FileName
so the code you provided has "test" as the filename, and outputfile as the fileformat. So let me clarify my question:
I'm not trying to do anything with the quotes in my code - the quotes are already parts of values in the cells of the worksheet I want to save. When I save it (either through file > save as... on the menu, or through code), anywhere there is a " (i.e. a double quote) in any of the cells of the worksheet, it gets saved as """ (i.e. a double quote enclosed in quotes). I want it to save as a text file without these extra quotes.
expression.SaveAs(FileName
so the code you provided has "test" as the filename, and outputfile as the fileformat. So let me clarify my question:
I'm not trying to do anything with the quotes in my code - the quotes are already parts of values in the cells of the worksheet I want to save. When I save it (either through file > save as... on the menu, or through code), anywhere there is a " (i.e. a double quote) in any of the cells of the worksheet, it gets saved as """ (i.e. a double quote enclosed in quotes). I want it to save as a text file without these extra quotes.
I have used this trick in the past to save Excel data "as is" into a text file.
Run a piece of code, or a spreadsheet formula, to concatenate the entire row of data into a single cell. Then save as a text file and check of Tab as your separator.
Leon
Run a piece of code, or a spreadsheet formula, to concatenate the entire row of data into a single cell. Then save as a text file and check of Tab as your separator.
Leon
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Leon,
Can you elaborate a little. You mean to contacenate everything in row 1 into cell A1, everything in row 2 into cell A2, etc, and then where (in code) do you have the option of setting tab as a separator?
Craigewens,
I like your solution, except that it prompts to save the text file everytime. Is there a way around this?
Can you elaborate a little. You mean to contacenate everything in row 1 into cell A1, everything in row 2 into cell A2, etc, and then where (in code) do you have the option of setting tab as a separator?
Craigewens,
I like your solution, except that it prompts to save the text file everytime. Is there a way around this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Leon, that last one is what I was looking for. Unfortunately, there's a sublety about it, so I'm not going to use it. It seems to be putting spaces inbetween columns rather than tabs. Both of you answered my question with a solution that worked for what I specified, so I think its fair that I split the points. Here's what I think I'm going to use:
iFileNum = FreeFile
Open replace(outputFile, ".xls", ".txt") For Output As #iFileNum
For i = 1 To xlSheet.Cells.SpecialCells
For j = 1 To xlSheet.Cells.SpecialCells
If Not IsError(xlSheet.Cells(i, j).Value) Then
strRow = strRow & xlSheet.Cells(i, j).Value & Chr(9)
End If
Next j
Print #iFileNum, strRow
strRow = ""
Next i
Close #iFileNum
xlFile.Close
iFileNum = FreeFile
Open replace(outputFile, ".xls", ".txt") For Output As #iFileNum
For i = 1 To xlSheet.Cells.SpecialCells
For j = 1 To xlSheet.Cells.SpecialCells
If Not IsError(xlSheet.Cells(i, j).Value) Then
strRow = strRow & xlSheet.Cells(i, j).Value & Chr(9)
End If
Next j
Print #iFileNum, strRow
strRow = ""
Next i
Close #iFileNum
xlFile.Close
As long as it works for you. Thanks for the grade,
Leon
Leon
xlFile.SaveAs(chr(34) & "test" & chr(34), outputfile)
Hope this helps.
-Jim