PDA

View Full Version : Solved: Export a column to text file of certain name



ronjon65
04-02-2012, 04:15 PM
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?

mancubus
04-03-2012, 12:41 AM
hi.
see the udf_test sheet in attached file to see the removed chars.



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

chandansify
04-03-2012, 01:48 AM
Hi,

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

e.g.


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



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)

ronjon65
04-04-2012, 02:47 PM
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 :)

chandansify
04-04-2012, 07:26 PM
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.

shrivallabha
04-04-2012, 09:00 PM
That is quite interesting bit of code, Chandan.

My first thought was to use late binding. But this fails.
Dim oD as Object
Set oD = CreateObject("MSForms.DataObject")

The reasons are discussed here (http://www.mrexcel.com/forum/showthread.php?t=192500).

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

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

And then it doesn't require the reference setting part. This example can be found @akihitoyamashiro (http://akihitoyamashiro.com/en/VBA/LateBindingDataObject.htm).

Cicerone
09-05-2018, 08:53 PM
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.

Taimaishoo
10-26-2022, 08:16 AM
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!