PDA

View Full Version : [SOLVED] Help, i need to create a command button for saving selection as txt file



endri
07-16-2016, 04:40 AM
Hi,
i am newbie in excel scripting and i need help for creating a command button.
I need the button to save the selected row in a specified text file.
Then when i select another row, i want the button to save the newly selected row to the same text file by rewriting the existing one.
If possible i need the button to save(rewrite) the file automatically without asking confirmation.

I found the following code which works but it saves the entire workbook, and asks confirmation. I can't figure out how to replace "ActiveWorkbook." with active selection
"ActiveWorkbook.SaveAs Filename:="C:\filepath\example.txt", _
FileFormat:=xlUnicodeText, CreateBackup:=False"

Any help will be very appreciated!!!

Thank you!

mdmackillop
07-16-2016, 06:13 AM
Something like

Sub Test()
Dim r As Range
Set r = Intersect(ActiveSheet.UsedRange, ActiveCell.EntireRow)
txt = Join(Application.Transpose(Application.Transpose(r.Value)), "|")
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\TestDir\testfile.txt", True)
a.WriteLine txt
a.Close
End Sub

endri
07-16-2016, 06:26 AM
Something like

Sub Test()
Dim r As Range
Set r = Intersect(ActiveSheet.UsedRange, ActiveCell.EntireRow)
txt = Join(Application.Transpose(Application.Transpose(r.Value)), "|")
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\TestDir\testfile.txt", True)
a.WriteLine txt
a.Close
End Sub


Thank you very much for your help. I will try your code right way. Thank you!

endri
07-16-2016, 07:40 AM
Something like

Sub Test()
Dim r As Range
Set r = Intersect(ActiveSheet.UsedRange, ActiveCell.EntireRow)
txt = Join(Application.Transpose(Application.Transpose(r.Value)), "|")
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("c:\TestDir\testfile.txt", True)
a.WriteLine txt
a.Close
End Sub


Amazing, it worked like a charm!!! Thank you very much!!!

snb
07-16-2016, 08:46 AM
Please do not quote.

An alternative:


Sub M_snb()
ActiveCell.EntireRow.Copy

With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipboard
CreateObject("scripting.filesystemobject").createtextfile("G:\OF\new.txt").write .GetText
End With
End Sub