Macro to export a column to .txt file with unique file name.

[Sk3l3]

Hi I'm a complete novice with VBA. I found the following thread with some VBA code that does exactly what I wanted it to, which is generate a text file with the contents of a range of cells in (code below).

Macro to export a column to .txt file

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

www.mrexcel.com

I was wondering if there was a way to allow the file name to be pulled from a cell rather than always being test.txt?

e.g. if Cell A1 contains test1, the file name would be test1.txt. If A1 contains test2 the file name would be test2.txt etc. etc.

Thanks in advance
Matt

Private Sub CommandButton1_Click()
 
  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("A1", Cells(Rows.Count, "A").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

 

[Joe4]

Welcome to the Board!

I was wondering if there was a way to allow the file name to be pulled from a cell rather than always being test.txt?

Sure, just change this line:

FileName = ThisWorkbook.Path & "\test.txt"

to something like this:

FileName = ThisWorkbook.Path & "\" & Range("A1").Value & ".txt"

Just change the range reference to match whatever cell you want to pull the name from.

 

[Sk3l3]

Welcome to the Board!


Sure, just change this line:

to something like this:

FileName = ThisWorkbook.Path & "\" & Range("A1").Value & ".txt"

Just change the range reference to match whatever cell you want to pull the name from.

Awesome thank you this works great. Is there a way of pulling that filename from another sheet within the same workbook I tried doing the following but it didn't work

FileName = ThisWorkbook.Path & "\" & Range("Variables!A1").Value & ".txt"

 

[Joe4]

You need to specify it like this:

FileName = ThisWorkbook.Path & "\" & Sheets("Variable").Range("A1").Value & ".txt"

 

[Sk3l3]

You need to specify it like this:

FileName = ThisWorkbook.Path & "\" & Sheets("Variable").Range("A1").Value & ".txt"

You my friend are a legend!!

 

[Joe4]

You are welcome.
Glad I was able to help!