macro / vba - copy text to notepad

[robo]

Hello to all,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I’ll be the first to admit, I consider myself an average user and know nothing about VBA.
I’m hoping one of you experts can help me with copying a range of cells into notepad and saving it as a .txt file onto a user’s desktop. The filename of the text is one listed in one of the cell’s.

Example:
Range of cells to copy to notepad: A1:A20
Cell to name filename of text file: A24

Thank you
Rob

 

[dafan]

Try this code:
http://www.mrexcel.com/forum/showthread.php?t=46163

 

[Andrew Poulsom]

Try:

Declare Function SHGetSpecialFolderLocation Lib "Shell32.dll" _
(ByVal hwndOwner As Long, ByVal nFolder As Long, pidl As ITEMIDLIST) As Long
Declare Function SHGetPathFromIDList Lib "Shell32.dll" Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, ByVal pszPath As String) As Long
Public Type ****EMID
    cb As Long
    abID As Byte
End Type
Public Type ITEMIDLIST
    mkid As ****EMID
End Type
Public Const MAX_PATH As Integer = 260
Public Const CSIDL_DESKTOP = &H0 '// The Desktop - virtual folder
Public Function fGetSpecialFolder(CSIDL As Long) As String
    Dim sPath As String
    Dim IDL As ITEMIDLIST
    fGetSpecialFolder = ""
    If SHGetSpecialFolderLocation(0, CSIDL, IDL) = 0 Then
        sPath = Space$(MAX_PATH)
        If SHGetPathFromIDList(ByVal IDL.mkid.cb, ByVal sPath) Then
            fGetSpecialFolder = Left$(sPath, InStr(sPath, vbNullChar) - 1) & ""
        End If
    End If
End Function
Sub Test()
    Dim ShNew As Worksheet
    Dim FName As String
    Dim Folder As String
    Set ShNew = Worksheets.Add
    With ThisWorkbook.Worksheets("Sheet1")
        .Range("A1:A20").Copy ShNew.Range("A1")
        FName = .Range("A24").Value
    End With
    Folder = fGetSpecialFolder(CSIDL_DESKTOP)
    FName = Folder & Application.PathSeparator & FName
    With ShNew
        .SaveAs Filename:=FName, FileFormat:=xlTextWindows
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
End Sub

 

[Legacy 68668]

.

Example:
Range of cells to copy to notepad: A1:A20
Cell to name filename of text file: A24

Thank you
Rob

Assuming A24 holds the full path incl. file extention.
e.g "c:\test\test.txt"
try

Sub test()
Dim rng As Range, delim As String, txt As String
Set rng = Application.InputBox("Select range", type:=8)
If rng Is Nothing Then Exit Sub
delim = vbTab  '<- delimiter
For i = 1 To rng.Row.Count
    txt = txt & vbCrLf & Evaluate("transpose(transpose(" & _
           .Rows(i).Address & "))"), delim)
Next
Open Range("a24").Value For Output As #1
    Print #1, Mid(txt, Len(vbCrLf) + 1)
Close #1
End Sub