1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Export Excel to Text without ""

Discussion in 'Business Applications' started by JoeHart, Apr 20, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. JoeHart

    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
     

    Attached Files:

  2. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    64,620
    First Name:
    Chuck
    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.
     
  3. JoeHart

    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.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     

    Attached Files:

  5. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    64,620
    First Name:
    Chuck
    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?
     
  6. JoeHart

    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
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I'd like to hear if any of the tips helped (?)
     
  10. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/918067

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice