Consulting

Results 1 to 8 of 8

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
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    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
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  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
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    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 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  7. #7
    VBAX Newbie
    Joined
    Sep 2018
    Posts
    1
    Location
    Hi All,

    I am having a similar problem and I am trying to avoid starting my own thread. I decent with excel formulas but this VBA is way over my head. I tried using this code to create a text fie as well addapting it to my needs but Im having an issue at the tFile.WriteLine line of code. I get a type mismatch error and Im not sure what to do. any thoughts? greatly appriciated.

  8. #8
    Mancubus,

    I know this is an old thread but yours worked perfectly for what I needed. However, I would like a save window to pop up asking me for the name of the file and not predetermined as mytextfile.txt. Is there a way to do this?

    Thanks in advance!

Posting Permissions

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