VBA Code to extract selected data from Excel to .txt file

[Ismailovech]

Hi All,

I have a code that enables me to save selected column data from excel to a new .txt file, I need a help to update the code to save the files automatically to a specific folder path and rename the files automatically as a function of the selected data (row and column) header.

Is that possible?!

Sub ExportRangetoFile()
'Update 20130913
Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
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

Thanks,
Ahmed

 

[Kenneth Hobson]

Welcome to the forum!

Sub ExportRangetoFile()
  Dim saveFile As String, WorkRng As Range
 
  saveFile = "d:\t\"  'drive:\path\, with trailing backslash
 
  On Error Resume Next
  Set WorkRng = Application.Selection
  Set WorkRng = Application.InputBox("Range", "Some Title", WorkRng.Address, Type:=8)
  saveFile = saveFile & Cells(WorkRng.Row, 1).MergeArea(1, 1) & "-" & Cells(1, WorkRng.Column) & ".txt"
  MsgBox saveFile
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
 
  With Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    WorkRng.Copy
    .Range("A1").PasteSpecial xlValues
    'saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
    .Parent.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
    .Parent.Close False
  End With
 
  Application.CutCopyMode = False
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub

 

[Ismailovech]

Welcome to the forum!

Sub ExportRangetoFile()
  Dim saveFile As String, WorkRng As Range

  saveFile = "d:\t\"  'drive:\path\, with trailing backslash

  On Error Resume Next
  Set WorkRng = Application.Selection
  Set WorkRng = Application.InputBox("Range", "Some Title", WorkRng.Address, Type:=8)
  saveFile = saveFile & Cells(WorkRng.Row, 1).MergeArea(1, 1) & "-" & Cells(1, WorkRng.Column) & ".txt"
  MsgBox saveFile

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False

  With Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    WorkRng.Copy
    .Range("A1").PasteSpecial xlValues
    'saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
    .Parent.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
    .Parent.Close False
  End With

  Application.CutCopyMode = False
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub

It is perfect, thanks a lot.

Thanks,
Ahmed