PDA

View Full Version : Pasting data from Excel into multiple Notepad Sessions



bpecue802
07-17-2013, 10:27 AM
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
07-17-2013, 11:49 AM
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
07-17-2013, 12:38 PM
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
07-17-2013, 12:50 PM
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
07-17-2013, 12:51 PM
And when I say "Enabled" in the post above I mean you push a big button that says, "GO!" :super:

mancubus
07-17-2013, 01:36 PM
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
07-17-2013, 01:48 PM
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.