!! VBA !! Copying and Pasting to another application

[kbdavis11]

Okay all - this is driving me insane . I am trying to come up with a VBA macro that will allow me to press a button and the following things will happen:

1. Select a range in a (already completed) template.
2. Copy the selected range.
3. Switch application windows
4. Go into 'notes' section of application.
5. Paste Selection
6. Get out of notes section
7. Switch back to Excel
8. Clear template data
9. Change active cell back to default location

I have compiled most of the code, however, I can't seem to get the information to either Copy or Paste. I am unsure of what the culprit is of the two... Everything else seems to work except that it just doesn't copy..

Here is the VBA macro I have compiled to this point. Any suggestions?

Sub Macro3()'
' Macro3 Macro
'


'
    Range("A1:B71").Select
    SendKeys "^C"              'Copies Selected Text
        
    AppActivate "AccuTerm 2K2"
    SendKeys "2^M", True    'Enters to notes screen
    SendKeys "^M", True     'Confirms above (Enter key)
    SendKeys "^V", True      'Pastes into client application
            
    Application.Wait (Now + TimeValue("0:00:05"))
    'Providing time for client application to finish
    'pasting...
    
    SendKeys "^M", True   'Next three enters are to
    SendKeys "^M", True   '...exit notes section
    SendKeys "^M", True
    AppActivate "Microsoft Excel"
                              
    Range("B52:B62").Clear  'Clears the Template
    Range("B52").Select       'Resets Cell Position
    
    Dim numLock As New NumLockClass
If numLock.value = False Then numLock.value = True
    'turns NumLock back on
    'Class Module
        
End Sub

 

[cerfani]

you can use vba code to copy... range.copy... Range("A1:B71").Copy (you will notice the ants indicating it is in the clipboard)

what from your code actually works? imo i dont think vba is how you should accomplish this. I would write something in .net

If you use .net with Excel interop, you have access to everything vba and all of .net and windows sdk

 

[kbdavis11]

Everything except the copy works. I have already tried Range().Copy and Selection.Copy

 

[cerfani]

are you sure cause Range("A1:B71").Copy will put your value in the clipboard... if you only had that one line in your macro, you could then manually paste it with Ctrl+V... that code will put it into the global clipboard that windows uses so it would indeed paste if you did it manually

my only guess is losing focus causes excel to clear it

 

[kbdavis11]

Okay so I modified the code to show as this now:

Sub Macro3()'
' Macro3 Macro
'


'
    Range("A1:B71").Select
    Selection.Copy
        
    AppActivate "AccuTerm 2K2"
    SendKeys "2", True    'Enters to notes screen
    SendKeys "^M", True   'Confirms above (Enter key)
    Application.Wait (Now + TimeValue("0:00:01"))
    SendKeys "^V", True   'Pastes into client application
            
    Application.Wait (Now + TimeValue("0:00:05"))
    'Providing time for client application to finish pasting...
    
    SendKeys "^M", True   'Next three enters are to
    SendKeys "^M", True   '...exit notes section
    SendKeys "^M", True
    AppActivate "Microsoft Excel"
                              
    Range("B52:B62").Clear  'Clears the Template
    Range("B52").Select     'Resets Cell Position
    
    Dim numLock As New NumLockClass
If numLock.value = False Then numLock.value = True
    'turns NumLock back on
        
End Sub

I was incorrect. The Selection.Copy does in fact work. It goes into the other application with the data copied to the clipboard. HOWEVER, SendKeys is not sending the CTRL-V to paste. However, since I have the 5-second delay set, during that delay I manually hit CTRL-V and it pasted the data from the spreadsheet. So it appears the FINAL piece to this puzzle is getting VBA to paste.

 

[cerfani]

i think the problem is, the code execution stops when excel loses focus... i might be wrong, just a guess

try to switch windows and then have a messagebox popup to let you know that the vba code is running if you catch my drift

i think the sendkey for ctrl+v isn't even being executed and this is cause of the way windows works and is a security feature

 

[kbdavis11]

i think the problem is, the code execution stops when excel loses focus... i might be wrong, just a guess

try to switch windows and then have a messagebox popup to let you know that the vba code is running if you catch my drift

I would completely agree with you except for the fact that VBA presses "2", "^M", inside the other application. I can see it navigate in there. Then there is the 5-second pause, then it will hit enter ("^M") three times, switch back to excel, clear the template, and re position itself to the correct cell.

 

[cerfani]

ok if it works then you need to focus on the textbox... double check when you switch to the app that the textbox is focused because if it is not focused then you are doing ctrl+v when the wrong control is active. Maybe you need to send some Tabs first.

Test it on notepad. Open Notepad and have vba switch to that and do ctrl+v to test

 

[kbdavis11]

ok if it works then you need to focus on the textbox... double check when you switch to the app that the textbox is focused because if it is not focused then you are doing ctrl+v when the wrong control is active. Maybe you need to send some Tabs first.

Test it on notepad. Open Notepad and have vba switch to that and do ctrl+v to test

Strange. I did you suggestion and opened notepad. VBA pasted into notepad just fine. So i went back into VBA and added another SendKeys just to test things out and it noted the other application with that sendkeys and the enter afterwards, but still didn't paste. Here is what I used for that test:

Sub Macro3()'
' Macro3 Macro
'


'
    Range("A1:B71").Select
    Selection.Copy
        
    AppActivate "AccuTerm 2K2"
    SendKeys "2", True    'Enters to notes screen
    SendKeys "^M", True   'Confirms above (Enter key)
    Application.Wait (Now + TimeValue("0:00:01"))
        
[COLOR=#00FF00]    SendKeys "Testing" & "^M", True[/COLOR]
    SendKeys "^V", True   'Pastes into client application
            
    Application.Wait (Now + TimeValue("0:00:05"))
    'Providing time for client application to finish pasting...
    
    SendKeys "^M", True   'Next three enters are to
    SendKeys "^M", True   '...exit notes section
    SendKeys "^M", True
    AppActivate "Microsoft Excel"
                              
    Range("B52:B62").Clear  'Clears the Template
    Range("B52").Select     'Resets Cell Position
    
    Dim numLock As New NumLockClass
If numLock.value = False Then numLock.value = True
    'turns NumLock back on
        
End Sub

But the thing I do not understand is that during the 5-second pause, I can manually hit CTRL-V and it pastes just fine.

 

[cerfani]

try not pasting... just try sending a V... it should type the character 'v' to where you are trying to paste... i think windows does not like what you are doing for some reason or when you send ctrl+v via sendkey the textbox does not receive this input. So the textbox gets focused once it switches?? The caret thing should be blinking in the textbox before SendKey ^V