Excel Hints

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.


    [vba]
    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
    [/vba]
    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."

  3. #3

    Lightbulb Sorry, no loops please.

    Hi,

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

    e.g.

    [vba]
    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

    [/vba]

    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.
    [VBA]Dim oD as Object
    Set oD = CreateObject("MSForms.DataObject")[/VBA]

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

    [VBA]Sub Test()
    Dim sFile As String
    Dim oD As Object

    Set oD = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

    '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[/VBA]

    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
  •