Export Excel to Text without ""

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

JoeHart

Thread Starter
Joined
Aug 17, 2008
Messages
125
Hi,
I have an excel spreadsheet that I would like to convert to a .txt file. When I try this all of my commas are shown as "," I need to remove the double quote. The file is correct if I convert to a .csv file, but I would like it to have the .txt file extention.

In the first line of the attched file you will see something similar to this.
OUCT1447.6POUFPL4/10/2010 0:004/10/2010 1:00
If I save the attached file as text I would like for every tab to insert a comma so that the line would look like:
OUCT,1447.6,,,,,,POU,FPL,4/21/2010 0:00,4/21/2010 1:00
This file should be named C:\ATC\OUCT_TTC which is a text file.

I am trying to creat a macro to take the attached file, which is a tab in a worksheet I have and automatically save it as C:\ATC\OUCT_TTC. Then I have to upload this file to a website which can only read it as a txt.
I hope this is clear, and thanks for the help.
Joe
 

Attachments

cwwozniak

Chuck
Moderator
Joined
Nov 28, 2005
Messages
68,726
Save it as a CSV file. Then rename it to change the extension to TXT before uploading.
As far as I can tell, CSV and TXT files are the same type of file. It is just matter of how MS-Excel puts the text in the file.

Or am I missing something?

I have no idea if a macro can rename a file after creating it.
 

JoeHart

Thread Starter
Joined
Aug 17, 2008
Messages
125
That is exactly what I want, but I have to do this a lot and I am looking for a way to automate it. If it were possible to add a couple lines of code to change the .csv file to .txt my problem would be solved.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
This is the code:

Sub writeComments()
Dim filename As String
Dim xrow As Integer
filename = Environ$("TEMP") & "\OutPut.txt"
Open filename For Output As #1
For xrow = 1 To Range("H65536").End(xlUp).Row
Print #1, Cells(xrow, 8).Value & Cells(xrow, 9).Value & Cells(xrow, 10).Value & Cells(xrow, 11).Value
Next xrow
Close #1
Shell "Notepad " & filename, vbNormalFocus
End Sub

I put a button on the sheet to execute it.

Let me hear if it helps you :D
 

Attachments

cwwozniak

Chuck
Moderator
Joined
Nov 28, 2005
Messages
68,726
Hi Hans,

Thank you for jumping in. This was way over my head.

Any chance that an FTP function can be added in to handle JoeHart's need to also upload the generated text file to a server?
 

JoeHart

Thread Starter
Joined
Aug 17, 2008
Messages
125
I figure it out. Hans you were very close but I actually wanted a "," between fields. I tried to explain it but I guess I did not do a great job. The code I used is below.
Thanks again,
Joe
Sub UPDATE_OUCT_TTC()
Sheets("OUCT_TTC").Select
Range("A1:K2688").Select
' Dimension all variables
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer
' Prompt user for destination filename
DestFile = "C:\ATC\OUCT_TTC.txt"
' Obtain next free file handle number
FileNum = FreeFile()
' Turn error checking off
On Error Resume Next
' Attempt to open destination file for output
Open DestFile For Output As #FileNum
' If an error occurs report it and end
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If
' Turn error checking on
On Error GoTo 0
' Loop for each row in selection
For RowCount = 1 To Selection.Rows.Count
' Loop for each column in selection
For ColumnCount = 1 To Selection.Columns.Count
' Write current cell's text to file with quotes
Print #FileNum, Selection.Cells(RowCount, _
ColumnCount).Text;
' Check if cell is in last column
If ColumnCount = Selection.Columns.Count Then
' If so then write a blank line
Print #FileNum,
Else
' Otherwise write a comma
Print #FileNum, ",";
End If
' Start next iteration of ColumnCount loop
Next ColumnCount
' Start next iteration of RowCount loop
Next RowCount
'Close destination file
Close #FileNum
End Sub
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
Well, all you have to do is add'the , to my line of code and you're done:

Print #1, Cells(xrow, 8).Value & "," & Cells(xrow, 9).Value & "," & Cells(xrow, 10).Value & "," & Cells(xrow, 11).Value
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
Some help to execute FTP and transfer:

Syntax to trigger ftp from command line or to call from vba code


command line:
ftp -s:<full path to folder containing instructions>\Mytranfser.txt


Sample contents of 'Mytransfer.txt'


open 192.168.123.23
<username>
<password>
<source folder>
put <fullpath to where the file to tranfser is located>\filename.txt <- any filename with or without extension
quit

To execute from within vba code:

Call Shell("cmd /C ftp -sc:\GTC\Mytransfer.txt", vbMinimizedNoFocus)

There is one important fact to take into consideration.
Username and password are not scrambled so anyone with access to this txt file can reat it and retrieve credentials.

Always lock your PC when not at your desk
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top