Printable View
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?
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]
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::bug:
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)
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.Quote:
Originally Posted by ronjon65
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.
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.
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!