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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.
 
Upvote 0
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"
 
Upvote 0
You need to specify it like this:
VBA Code:
FileName = ThisWorkbook.Path & "\" & Sheets("Variable").Range("A1").Value & ".txt"
 
Upvote 0
Solution
You are welcome.
Glad I was able to help!
:)
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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
Back
Top