Pasting data from Excel into multiple Notepad Sessions

[bpecue802]

Hi Everyone,

I need some help writing code that will grab data from one sheet, paste said data into Notepad, go back into the same excel sheet, grab a different set of data, and paste into a different Notepad session, overall leaving me with two cascading notepad sessions.

The basic code I have written goes into the sheet, copies the data, opens notepad, but does not paste the data. This always leaves me with two blank Notepad sessions. From checking through things, Notepad doesn't seem to be getting focus when it opens, not allowing my sendkeys to paste into it.

Can you even open up two Notepad sessions? Can someone help me out?!

You'll find the code I have below.

Thanks,

Pecue-

Sheets("Jan").Select 'where my data is stored
    Range("N2:N70").Select 'the range I want selected
    Selection.Copy 'basic copy
    Notepad = Shell("notepad.exe", vbNormalFocus) 'opening NP with normal focus
    AppActivate Notepad 'my attempts to give notepad always seem to fail
    Application.SendKeys "^V", 1 'i added extra time thinking the sendkeys weren't working but this wasn't the problem
    Application.DisplayAlerts = False 'ruling out any pop ups
    Sheets("Jan").Select 'back into the same sheet from before
    Range("AC2:AC180").Select ' selecting the new data set
    Selection.Copy 'copying second data set
    Shell "notepad.exe", vbNormalFocus 'opening notepad different way hoping for better result
    SendKeys "^V" 'no such luck
    Application.DisplayAlerts = False
    Sheets("Upload Button").Select 'back to original sheet where button is to activiate macro

'Im new so if we could keep it basic that would help! Thanks!

[mancubus]

welcome to the forum.

this worked for me:

Sub ct_notepad()
    Sheets("Jan").Range("N2:N70").Copy
    Shell "notepad.exe", vbNormalFocus
    SendKeys "^V"
    Application.Wait Now + TimeValue("00:00:05")
    Range("AC2:AC180").Copy
    Shell "notepad.exe", vbNormalFocus
    SendKeys "^V"
End Sub

[Kenneth Hobs]

SendKeys() requires UAC to be disabled. Avoid that method if possible.

Did you really need Notepad open? You are really just making a text file.

See if these give you some ideas. Run T from a blank workbook.

Sub t()
    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, ",")
    s2 = getClipboard()
    Application.CutCopyMode = False
    MakeTXTFile s, s2
    rc = Shell("notepad " & s, vbNormalFocus)
    'Kill s
End Sub

Sub InsertTxtFileContents()
    Dim s As String, a() As String, b() As String
    Dim i As Long, i2 As Long, r As Range
    s = ActiveWorkbook.Path & "\Fruits.txt"
    Set r = Range("C5")
    FileLoadToArray a(), s
    i2 = 0
    For i = LBound(a) To UBound(a)
        b() = Split(a(i), ",")
        If UBound(b) > 0 Then r.Offset(i2, 0).Resize(1, UBound(b) + 1).Value = b()
        i2 = i2 + 1
    Next i
    '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


'http://www.visualbasic.happycodings.com/Files_Directories_Drives/code54.html
'Purpose     :  Reads a file into a string array.
'Inputs      :  asLines()               A string array (see Outputs)
'               sFileName               The path and file name of the file to open and read
'Outputs     :  Returns an empty string on success, else returns the error decription
'               asLines(1 to NumLines)  String array containing the file
'Notes       :  Usually used for text files, but will load any file type.



Function FileLoadToArray(ByRef asLines() As String, ByVal sFileName As String) As String
    Dim iFileNum As Long, lFileLen As Long
    Dim sBuffer As String
    'Initialise Variables
    On Error GoTo ErrFailed
    'Open File
    iFileNum = FreeFile
    Open sFileName For Binary Access Read As #iFileNum
    'Get the size of the file
    lFileLen = LOF(iFileNum)
    If lFileLen Then
        'Create output buffer
        sBuffer = String(lFileLen, " ")
        'Read contents of file
        Get iFileNum, 1, sBuffer
        'Split the file contents
        asLines = Split(sBuffer, vbNewLine)
    End If
    Close #iFileNum
    'Return success
    FileLoadToArray = ""
    Exit Function
    ErrFailed:
    Debug.Assert False
    Debug.Print Err.Description
    FileLoadToArray = Err.Description
    'Close file
    If iFileNum Then
        Close #iFileNum
    End If
End Function

[bpecue802]

Thanks to both Mancubus and Kenneth for some sweet responses. With a little tweaking from Mancubus code, I was able to make things work. I was making a dropdown list that populated when you enabled the macro. Overall it works perfect now! Here's my code. Is there any way to make it less lengthy with all the IF and ELSE statements? Or is it the best way to go about this.

Thanks again!!

Sub Unitil_upload_button()
    If (Sheets("Upload Button").Range("A15") = "1") Then
        Sheets("Jan").Range("AC2:AC180").Copy
        Shell "notepad.exe", vbNormalFocus
        SendKeys "^V"
        Application.Wait Now + TimeValue("00:00:01")
        Sheets("Jan").Range("N2:N70").Copy
        Shell "notepad.exe", vbNormalFocus
        SendKeys "^V"
    Else
        If (Sheets("Upload Button").Range("A15") = "2") Then
            Sheets("Feb").Range("AC2:AC180").Copy
            Shell "notepad.exe", vbNormalFocus
            SendKeys "^V"
            Application.Wait Now + TimeValue("00:00:01")
            Sheets("Feb").Range("N2:N70").Copy
            Shell "notepad.exe", vbNormalFocus
            SendKeys "^V"
        Else
            If (Range("A15") = "3") Then
                Sheets("Mar").Range("AC2:AC180").Copy
                Shell "notepad.exe", vbNormalFocus
                SendKeys "^V"
                Application.Wait Now + TimeValue("00:00:01")
                Sheets("Mar").Range("N2:N70").Copy
                Shell "notepad.exe", vbNormalFocus
                SendKeys "^V"
           Else
                If (Range("A15") = "4") Then
                    Sheets("Apr").Range("AC2:AC180").Copy
                    Shell "notepad.exe", vbNormalFocus
                    SendKeys "^V"
                    Application.Wait Now + TimeValue("00:00:01")
                    Sheets("Apr").Range("N2:N70").Copy
                    Shell "notepad.exe", vbNormalFocus
                    SendKeys "^V"
                 Else
                    If (Range("A15") = "5") Then
                        Sheets("May").Range("AC2:AC180").Copy
                        Shell "notepad.exe", vbNormalFocus
                        SendKeys "^V"
                        Application.Wait Now + TimeValue("00:00:01")
                        Sheets("May").Range("N2:N70").Copy
                        Shell "notepad.exe", vbNormalFocus
                        SendKeys "^V"
                    Else
                        If (Range("A15") = "6") Then
                            Sheets("Jun").Range("AC2:AC180").Copy
                            Shell "notepad.exe", vbNormalFocus
                            SendKeys "^V"
                            Application.Wait Now + TimeValue("00:00:01")
                            Sheets("Jun").Range("N2:N70").Copy
                            Shell "notepad.exe", vbNormalFocus
                            SendKeys "^V"
                       Else
                            If (Range("A15") = "7") Then
                                Sheets("Jul").Range("AC2:AC180").Copy
                                Shell "notepad.exe", vbNormalFocus
                                SendKeys "^V"
                                Application.Wait Now + TimeValue("00:00:01")
                                Sheets("Jul").Range("N2:N70").Copy
                                Shell "notepad.exe", vbNormalFocus
                                SendKeys "^V"
                            Else
                                If (Range("A15") = "8") Then
                                   Sheets("Aug").Range("AC2:AC180").Copy
                                   Shell "notepad.exe", vbNormalFocus
                                   SendKeys "^V"
                                   Application.Wait Now + TimeValue("00:00:01")
                                   Sheets("Aug").Range("N2:N70").Copy
                                   Shell "notepad.exe", vbNormalFocus
                                   SendKeys "^V"
                               Else
                                   If (Range("A15") = "9") Then
                                       Sheets("Sep").Range("AC2:AC180").Copy
                                       Shell "notepad.exe", vbNormalFocus
                                       SendKeys "^V"
                                       Application.Wait Now + TimeValue("00:00:01")
                                       Sheets("Sep").Range("N2:N70").Copy
                                       Shell "notepad.exe", vbNormalFocus
                                       SendKeys "^V"
                                  Else
                                       If (Range("A15") = "10") Then
                                            Sheets("Oct").Range("AC2:AC180").Copy
                                            Shell "notepad.exe", vbNormalFocus
                                            SendKeys "^V"
                                            Application.Wait Now + TimeValue("00:00:01")
                                            Sheets("Oct").Range("N2:N70").Copy
                                            Shell "notepad.exe", vbNormalFocus
                                            SendKeys "^V"
                                      Else
                                            If (Range("A15") = "11") Then
                                                Sheets("Nov").Range("AC2:AC180").Copy
                                                Shell "notepad.exe", vbNormalFocus
                                                SendKeys "^V"
                                                Application.Wait Now + TimeValue("00:00:01")
                                                Sheets("Nov").Range("N2:N70").Copy
                                                Shell "notepad.exe", vbNormalFocus
                                                SendKeys "^V"
                                        Else
                                            If (Range("A15") = "12") Then
                                                Sheets("Dec").Range("AC2:AC180").Copy
                                                Shell "notepad.exe", vbNormalFocus
                                                SendKeys "^V"
                                                Application.Wait Now + TimeValue("00:00:01")
                                                Sheets("Dec").Range("N2:N70").Copy
                                                Shell "notepad.exe", vbNormalFocus
                                                SendKeys "^V"
                                                  End If
                                              End If
                                         End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If
End Sub

[bpecue802]

And when I say "Enabled" in the post above I mean you push a big button that says, "GO!"

[mancubus]

you're welcome.

as kennets said, sendkeys method is not reliable all the time.

i strongly recommend you use the procedures by kenneth.

[Kenneth Hobs]

Seldom does one need more than 2-3 IF-Else-IF structures. "Select Case" structure can often help.

At a minimum, make a Sub where you pass your inputs and then your code reduces down quite a bit.

Another speed method is to put all of what you need for input parameters into an array and there iterate the array.

Try making a short example file so that we can see the pattern of what you are trying to do. Patterns are one of the biggest reasons to write macros.