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.

Solved: Saving Excel Data to a text file

Discussion in 'Business Applications' started by Pmwh, Jul 27, 2009.

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

    Pmwh Thread Starter

    Joined:
    Mar 14, 2002
    Messages:
    105
    I have an excel file with the current scenario:

    A12 - this cell uses a CONCATENATE formula (from other cells) to create a directory structure and file name (example- C:\project\vendor\41576_info.txt). The directory structure already exists. The the *.txt file does not exist.

    I would like to use a VB macro that will create the new text file in the specific directory and add any content in cells A13:A17 to the text file. Any suggestions on how to do this? Thanks in advance

    PS - I did try to look through previous posts but the search function was retuning errors.
     
  2. Jimmy the Hand

    Jimmy the Hand

    Joined:
    Jul 28, 2006
    Messages:
    1,223
    Try this code:
    Code:
    Sub WriteToTxt()
        Dim FNumber As Long, FName As String
        Dim cel As Range
        
        
        FNumber = FreeFile
        FName = Range("A12").Value
        Open FName For Output As FNumber
        For Each cel In Range("A13:A17")
            Write #FNumber, cel.Value
        Next
        Close FNumber
    End Sub
     
  3. Pmwh

    Pmwh Thread Starter

    Joined:
    Mar 14, 2002
    Messages:
    105
    Jimmy the Hand - you code works. Not sure why it takes text values and adds " " in the text file. It does not do this for numerical values. Thanks for your help.
     
  4. Pmwh

    Pmwh Thread Starter

    Joined:
    Mar 14, 2002
    Messages:
    105
    Here is another piece of code to get the result I am looking for. Thought it was worth sharing in case someone else is looking for a similar solution.

    Code:
    Sub testing123()
    
          Open Range("a12") For Output As 1
              For i = 13 To 17
                  Print #1, Range("a" & i)
              Next
          Close 1
    End Sub
    
     
  5. Pmwh

    Pmwh Thread Starter

    Joined:
    Mar 14, 2002
    Messages:
    105
    Also for the record, my first successful attempt at making this happen was a little more complicated than the first two pieces of code.

    Code:
    Sub WriteTxt()
    ExportRangeAsDelimitedText ThisWorkbook.Name, _
       "Output", "A13:A17", "C:\company\info.txt", _
       ";", True, True, False
    
    End Sub
    
    This creates/replaces the same file in the same specified location each time. Now I needed to copy it into the correct direcftory and give it the correct name.
    So I used similar code to save out a .BAT file (something I was already doing for other events I was making happen in Windows).

    Code:
    Sub WriteBat()
    
    Call write_info
    
    ExportRangeAsDelimitedText ThisWorkbook.Name, _
       "Output", "A2:A10", "C:\company\NewDir.Bat", _
       ";", True, True, False
    
    End Sub
    
    In this BAT file, I had a line to copy the file to the correct directory (as specified in A12) and the new name (again, this value was retrieved from A12)

    Code:
    'This BAT file will execute from the Excel VBA
    'The new name and location are pulled from cell A12
    copy C:\company\info.txt c:\project\vendor\xxxx_info.txt
    
    This obviously took a lot more code and required some crazy thinking to create. But it did produce the results I was looking for.
     
  6. 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/846822

  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