Export Excel to Text without ""

[JoeHart]

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

 

[cwwozniak]

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]

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]

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

 

[cwwozniak]

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]

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]

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]

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

 

[Keebellah]

I'd like to hear if any of the tips helped (?)