Macro to export a column to .txt file

[vikramjeet23]

Can I please get some help to have a button to export column B to a .txt file

 

[ZVI]

Below is a clipboard usage example:

Sub ColumnToTxt1()
 
  Dim s As String, FileName As String, FileNum As Integer
 
  ' Define full pathname to TXT file
  FileName = ThisWorkbook.Path & "\test.txt"
 
  ' Copy range of B column to the clipboard
  Range("B1", Cells(Rows.Count, "B").End(xlUp)).Copy
 
  ' Copy clipboard text to the 's' variable
  With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
     .GetFromClipboard
     s = .GetText
  End With
  Application.CutCopyMode = False
 
  ' Write s to the TXT file
  FileNum = FreeFile
  If Len(Dir(FileName)) > 0 Then Kill FileName
  Open FileName For Binary Access Write As FileNum
  Put FileNum, , s
  Close FileNum
 
End Sub

 

[vikramjeet23]

Thank you ZVI. I am getting a run time error on the below line of the code

With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

 

[ZVI]

That late binding code can be Excel's version or object registration dependent.
Well, try early binding version.
1. Insert to workbook a Userform: VBE - Insert - UserForm
The reference to Microsoft Form 2.0 Object library will appear in VBE - Tools - References.
2. Delete (or not) that form - Right Click on UserForm1 - Remove UserForm1 - No
3. Use the below code

Sub ColumnToTxt2()
 
  Dim s As String, FileName As String, FileNum As Integer
 
  ' Define full pathname of TXT file
  FileName = ThisWorkbook.Path & "\test.txt"
 
  ' Copy range to the clipboard
  Range("B1", Cells(Rows.Count, "B").End(xlUp)).Copy
 
  ' Copy column content to the 's' variable via clipboard
  With New DataObject
     .GetFromClipboard
     s = .GetText
  End With
  Application.CutCopyMode = False
 
  ' Write s to TXT file
  FileNum = FreeFile
  If Len(Dir(FileName)) > 0 Then Kill FileName
  Open FileName For Binary Access Write As FileNum
  Put FileNum, , s
  Close FileNum
 
End Sub

 

[vikramjeet23]

This is beautiful. it worked. I dont have knowledge of VBA so i dont understand what the below is. Is it possible to have a custom button which completes the processing without any prompt

1. Insert to workbook a Userform: VBE - Insert - UserForm
The reference to Microsoft Form 2.0 Object library will appear in VBE - Tools - References.
2. Delete (or not) that form - Right Click on UserForm1 - Remove UserForm1 - No

Thank you so much

 

[ZVI]

You don't need do the points 1 & 2 once more.
Just do it once and save workbook.
After that the code of that workbook will be good for any PC.

 

[vikramjeet23]

Thank you so much ZVI. You are a superstar !!!

 

[ZVI]

Glad it helped, Vikramjeet!