Results 1 to 4 of 4

macro / vba - copy text to notepad

This is a discussion on macro / vba - copy text to notepad within the Excel Questions forums, part of the Question Forums category; Hello to all, Iíll be the first to admit, I consider myself an average user and know nothing about VBA ...

  1. #1
    New Member
    Join Date
    Mar 2009
    Location
    racine, wi
    Posts
    7

    Default macro / vba - copy text to notepad

    Hello to all,

    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

  2. #2
    Board Regular
    Join Date
    May 2008
    Location
    Netherlands
    Posts
    692

    Default Re: macro / vba - copy text to notepad

    Try this code:
    Open Notepad form VBA
    Regards,
    Stefan


    Using Office 2010

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,607

    Default Re: macro / vba - copy text to notepad

    Try:

    Code:
    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

  4. #4

    Join Date
    Oct 2006
    Posts
    2,541

    Default Re: macro / vba - copy text to notepad

    Quote Originally Posted by robo View Post
    .

    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
    Code:
    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com