export excel data to text file and save it using cell value as the file name

matrix26

New Member
Joined
Dec 16, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I have the following code that allows me to select specific cells in an excel document and export them to a text file.
I want to modify the code so it will pre-fill the filename with the value found in cell B2.
I've found many examples of how to do this but just can't get any of the examples to work within my code.
Can anyone help out?
Thank you in advance


Sub ExportRangetoFile()
'Update 20210310

Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
xTitleId = "COPY COLUMN B"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).Paste
saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
wb.SaveAs FileName:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,142
VBA Code:
Option Explicit

Sub ExportRangetoFile()
'Update 20210310

Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
xTitleId = "COPY COLUMN B"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).Paste
saveFile = Range("B2").Value
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
 

matrix26

New Member
Joined
Dec 16, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
Sub ExportRangetoFile() 'Update 20210310 Dim wb As Workbook Dim saveFile As String Dim WorkRng As Range On Error Resume Next xTitleId = "COPY COLUMN B" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Application.ScreenUpdating = False Application.DisplayAlerts = False Set wb = Application.Workbooks.Add WorkRng.Copy wb.Worksheets(1).Paste saveFile = Range("B2").Value wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False wb.Close Application.CutCopyMode = False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Hi,

Thanks, but this and doesn't work.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,142
Made two changes to the macro. This works here :

VBA Code:
Option Explicit

Sub ExportRangetoFile()
'Update 20210310

Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
'xTitleId = "COPY COLUMN B"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).Paste
saveFile = Range("B2").Value
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
 

matrix26

New Member
Joined
Dec 16, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Made two changes to the macro. This works here :

VBA Code:
Option Explicit

Sub ExportRangetoFile()
'Update 20210310

Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
'xTitleId = "COPY COLUMN B"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).Paste
saveFile = Range("B2").Value
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
I appreciate your help, this doesn't work either.
The script runs up to the selection of cells then ends without saving anything.

Thank you for trying.
 

matrix26

New Member
Joined
Dec 16, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I don't understand. The code works here.
I'm wondering if it's because I'm using a work laptop and I need to also set a path string.
Let me try that and I'll get back to you.
 

matrix26

New Member
Joined
Dec 16, 2020
Messages
30
Office Version
  1. 2016
Platform
  1. Windows
Got it to work.
I added this line
InitialFileName = Range("B2")

And changed this line
saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
to
saveFile = Application.GetSaveAsFilename(InitialFileName, fileFilter:="Text Files (*.txt), *.txt")

Thank you for all of your help.
I really appreciate it
 
Solution

Forum statistics

Threads
1,140,999
Messages
5,703,645
Members
421,307
Latest member
morrden86

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