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

[matrix26]

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

 

[Logit]

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]

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]

Made two changes to the macro. This works here :

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]

Made two changes to the macro. This works here :

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.

 

[Logit]

I don't understand. The code works here.

 

[matrix26]

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]

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

 

[Logit]

Glad you have an answer !