Microsoft Excel Webinar

Results 1 to 6 of 6

Thread: Solved: Export a column to text file of certain name

  1. #1

    Solved: Export a column to text file of certain name

    I want to export a column to a plain text file (no commas, no ""), just simple text.

    However, the output filename needs to be that in a given cell. This shouldn't be too hard, but everything I have searched for seems overly complicated and not quite what I want.

    Any thoughts?

  2. #2
    hi.
    see the udf_test sheet in attached file to see the removed chars.


    VB:
    Sub ColToTxtFile() 
         
        Dim tFile As Object 
        Dim ws As Worksheet 
        Dim cll As Range 
        Dim fPath As String 
        Dim LastRow As Long 
         
        Set ws = Worksheets("toTxt") 'change to suit
         
        fPath = "C:\my_docs\test\" 'change to suit
        If Right(fPath, 1) <> "\" Then fPath = fPath & "\" 
         
        LastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row 
         
        With CreateObject("Scripting.FileSystemObject") 
            Set tFile = .CreateTextFile(fPath & "mytextfile.txt", True) 
        End With 
         
        For Each cll In ws.Range("A1:A" & LastRow) 
            tFile.WriteLine (RemovePunctuation(cll.Value)) 
        Next cll 
         
        tFile.Close 
         
    End Sub 
     
    Function RemovePunctuation(r As String) As String 
         'http://www.mrexcel.com/forum/showthread.php?t=299432
         
        With CreateObject("vbscript.regexp") 
            .Pattern = "[^A-Z0-9 ]" 
            .IgnoreCase = True 
            .Global = True 
            RemovePunctuation = .Replace(r, "") 
        End With 
         
    End Function 
    
    
    Formatting tags added by mark007
    Attached Files Attached Files
      To view attachments your post count must be 0 or greater. Your post count is 0 momentarily.
    "But we ain't singing, we bringing drama."
    2pac

  3. #3

    Lightbulb Sorry, no loops please.

    Hi,

    Sorry, I don't like looping...so thought of something else.

    e.g.

    VB:
     
    Dim sFile As String 
    Dim oD As New MSForms.DataObject 
     
     'get file info from cell
    sFile = Range("OutFile").Text ' change this to desired range,
     
     'copy the desired column
    Columns("I:I").Copy 'change this to desired range, create your own logic
     
     'get data from clipboard
    oD.GetFromClipboard 
     
     'get text
    sContents = oD.GetText 
     
     'create and dump the data
    Dim fhFile As Integer 
    fFile = FreeFile 
    Open sFile For Output As #fFile 
    Print #fFile, sContents; 
    Close #fFile 
    Application.CutCopyMode = False 
    
    
    Formatting tags added by mark007
    Another method in my mind is:
    1. copy the desired range.
    2. create a text file on file system (using FileSystemObject or Open method).
    3. Launch the newly created file in notepad.exe (might use shell/cmd etc. method)
    4. paste/dump the data using some method (might use sendkeys method)

  4. #4
    Thanks guys.

    I ended up using Mancubus' code since chandansify's gave me an error in "MsForms.Dataobject". Perhaps because I am using 2003 still?

    Thanks

  5. #5
    Quote Originally Posted by ronjon65
    Thanks guys.

    I ended up using Mancubus' code since chandansify's gave me an error in "MsForms.Dataobject". Perhaps because I am using 2003 still?

    Thanks
    Perhaps "Microsoft Form 2.0 Library" is not there in your project's reference list.

  6. #6
    That is quite interesting bit of code, Chandan.

    My first thought was to use late binding. But this fails.
    VB:
    Dim oD As Object 
    Set oD = CreateObject("MSForms.DataObject") 
    
    
    Formatting tags added by mark007
    The reasons are discussed here.

    But most of the time, there's workaround. Following workaround seems to work fine. The red marked part eludes me, it looks like those strings we get to see in registry editor [akin to GUIDs].

    VB:
    Sub Test() 
        Dim sFile As String 
        Dim oD As Object 
         
        [I][COLOR=Red]Set oD = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")[/COLOR][/I] 
         
         'get file info from cell
        sFile = Range("OutFile").Text ' change this to desired range,
         
         'copy the desired column
        Columns("I:I").Copy 'change this to desired range, create your own logic
         
         'get data from clipboard
        oD.GetFromClipboard 
         
         'get text
        sContents = oD.GetText 
         
         'create and dump the data
        Dim fhFile As Integer 
        fFile = FreeFile 
        Open sFile For Output As #fFile 
        Print #fFile, sContents; 
        Close #fFile 
        Application.CutCopyMode = False 
    
    
    Formatting tags added by mark007
    And then it doesn't require the reference setting part. This example can be found @akihitoyamashiro.
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2010 in Home / 2007 in Office on Win XP
    --------------------------------------------------------------------------------------------------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •