Solved: Create a .txt file from a range of selected cells

[qcoleman]

I would like to create a vba code that will copy a defined range of cells and the open a new worksheet and paste the results and then save the new worksheet as a Text (Tab delimited) .txt file. Is this possible?

[Kenneth Hobs]

It is better to state what you want, not how to get there.


Sub test()
Dim rc As Variant
Dim s As String, s2 As String

s = ActiveWorkbook.Path & "\Fruits.txt"

[A1] = "Fruit"
[A2] = "Apple"
[A3] = "Grape"
[A4] = "Orange"
[B1] = "Color"
[B2] = "Red/Golden"
[B3] = "Red/Green"
[B4] = "Orange"

Range("A1:B4").Copy
s2 = Replace(getClipboard(), vbTab, ",")
Application.CutCopyMode = False
MakeTXTFile s, s2

rc = Shell("notepad " & s, vbNormalFocus)
'Kill s
End Sub

Sub MakeTXTFile(filePath As String, str As String)
Dim hFile As Integer
If Dir(FolderPart(filePath), vbDirectory) = "" Then
MsgBox filePath, vbCritical, "Missing Folder"
Exit Sub
End If

hFile = FreeFile
Open filePath For Output As #hFile
If str <> "" Then Print #hFile, str
Close hFile
End Sub

Function FolderPart(sPath As String) As String
FolderPart = Left(sPath, InStrRev(sPath, "\"))
End Function

Function getClipboard()
'Add Reference: 'Reference: Microsoft Forms xx Object
Dim MyData As DataObject

On Error Resume Next
Set MyData = New DataObject
MyData.GetFromClipboard
getClipboard = MyData.GetText
End Function

[msuresh]

Sub create_txt_file()

Dim ws As Worksheet
Dim newwb As Workbook
Dim newws As Worksheet
Dim fname As String

Set ws = ActiveSheet
Set newwb = Application.Workbooks.Add
Set newws = newwb.Sheets.Add

ws.Range("A1:B2").Copy

newws.Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False

fname = Application.GetSaveAsFilename

If Right(fname, 4) = ".txt" Then
fname = fname
Else
fname = fname & "txt"
End If

Application.DisplayAlerts = False
newwb.SaveAs fname, xlText
Application.DisplayAlerts = True




End Sub

[msuresh]

Sub create_txt_file()

Dim ws As Worksheet
Dim newwb As Workbook
Dim newws As Worksheet
Dim fname As String

Set ws = ActiveSheet
Set newwb = Application.Workbooks.Add
Set newws = newwb.Sheets.Add

ws.Range("A1:B2").Copy

newws.Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False

fname = Application.GetSaveAsFilename

If Right(fname, 4) = ".txt" Then
fname = fname
Else
fname = fname & "txt"
End If

Application.DisplayAlerts = False
newwb.SaveAs fname, xlText
Application.DisplayAlerts = True

End Sub

[qcoleman]

This works great, but what if I wanted to take this a step further by creating a dynamic range to be selected. So for example I what to search row 5 for the word “Part” once that column is found I need the code to select the range of cell s that includes column that it found Part in row 6 and the last row found. Then I need it create a .txt file from the selection made. I tried getting this piece of code to work, but my attempts have not worked out.
Set cell = Rows(5).Find("*Part*")
LastRow = Cells(Rows.Count, cell.Column).End(xlUp).Row
Range(R5C" & cell.Column & ":R[-1]C" & cell.Column & "))

[monarchd]

How about:


Sub create_txt_file()
Dim ws As Worksheet
Dim newwb As Workbook
Dim newws As Worksheet
Dim fname As String
Dim LastRow As Long
Dim cell
Set ws = ActiveSheet
Set newwb = Application.Workbooks.Add
Set newws = newwb.Sheets.Add
Set cell = Rows(5).Find("*Part*")
LastRow = ws.Cells(ws.Rows.Count, "A:A").End(xlUp).Row
ws.Range("C2:C" & Range("A65536").End(xlUp).Row).Copy
newws.Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False
fname = Application.GetSaveAsFilename
If Right(fname, 4) = ".txt" Then
fname = fname
Else
fname = fname & "txt"
End If
Application.DisplayAlerts = False
newwb.SaveAs fname, xlText
Application.DisplayAlerts = True
End Sub

[qcoleman]

Ken,

I can’t seem to get your code to run, but it judging by the code this is along the lines of what I need. I really want to take the code beyond my original post and have it search for a word in a set row (i.e. column headings in row 5) once it finds that word (i.e. “Part”) then I need it to copy the cells from row 6 to the last row with data in whatever column the word search returns and then create a text file from that selection. This needs to be dynamic because the numbers of row down changes.