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

Sk3l3

New Member
Joined
Apr 9, 2021
Messages
3
Office Version
  1. 365
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).


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

VBA Code:
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
FileName = ThisWorkbook.Path & "\" & Range("A1").Value & ".txt"
Just change the range reference to match whatever cell you want to pull the name from.
 

Sk3l3

New Member
Joined
Apr 9, 2021
Messages
3
Office Version
  1. 365
Welcome to the Board!


Sure, just change this line:

to something like this:
VBA Code:
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

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
You need to specify it like this:
VBA Code:
FileName = ThisWorkbook.Path & "\" & Sheets("Variable").Range("A1").Value & ".txt"
 
Solution

Sk3l3

New Member
Joined
Apr 9, 2021
Messages
3
Office Version
  1. 365
You need to specify it like this:
VBA Code:
FileName = ThisWorkbook.Path & "\" & Sheets("Variable").Range("A1").Value & ".txt"
You my friend are a legend!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help!
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,915
Messages
5,638,970
Members
417,062
Latest member
Canucks21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top