!! 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

 

[AlphaFrog]

I don't know AccuTerm but maybe consider/research alternatives to using send keys.

1.) In Excel VBA, copy to the clipboard (as you do now) then call an AccuTerm macro\script to paste from the clipboard to its notes screen. In other words, have AccuTerm do the pasting.

2.) There may be libraries you can reference from Excel VBA which allow you to access AccuTerm objects. See the AccuTerm documentation or look in the VBA editor menu under Tools\References to see if there are any AccuTerm libs in the list.

It seems you have the copy part working in Excel. Perhaps you could find a solution for pasting in the AccuTerm support forum?

 

[cerfani]

technically he can use windows sdk to do this if he knows how to import and use the correct functions

then he can get the textbox and edit its text property via memory... i haven't done this in vba but in .net it can be done

 

[kbdavis11]

I don't know AccuTerm but maybe consider/research alternatives to using send keys.

1.) In Excel VBA, copy to the clipboard (as you do now) then call an AccuTerm macro\script to paste from the clipboard to its notes screen. In other words, have AccuTerm do the pasting.

2.) There may be libraries you can reference from Excel VBA which allow you to access AccuTerm objects. See the AccuTerm documentation or look in the VBA editor menu under Tools\References to see if there are any AccuTerm libs in the list.

It seems you have the copy part working in Excel. Perhaps you could find a solution for pasting in the AccuTerm support forum?

I like this. I have had much more success in getting it to paste then with my previous method. But it's only pasting now at about a 30% success rate, but again I am using SendKeys to type a "shortcut" key that calls for the Macro in AccuTerm. Do you know of a way that VBA can call the accuterm script directly? I will attempt to provide any relevant information I can think of regarding the AccuTerm script.

The file that contains the script is located:

C:\Program Files (x86)\Atwin\AccuTerm Macros.atsc

Here is the AccuTerm script itself:

Sub Macro1	'<Macro = Macro1>
	'<Shortcut = Shift+Ctrl+Z>
	'<Desc = recorded 6/28/2016 3:37:55 PM>
	On Error Resume Next
	With ActiveSession
		.InputMode = 1
		'.WaitFor 0, 0, "1HCMD (/,?): "
		.Output "2" & Chr$(13)
		.Paste ""
		.InputMode = 0
	End With
End Sub

Here's the thing, if I do the shortcut key myself it will do the script every single time. So the culprit again would be the SendKeys. I even attempted to place my sendkey for the shortcut key using:

        Dim myTxt As String
        myTxt = "^+Z" 'Hotkey for client macro

SendKeys myTxt, True

Here is my current VBA code that is attempting to interact with my AccuTerm script:

#If VBA7 Then ' Excel 2010 or later
 
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal Milliseconds As LongPtr)
 
#Else ' Excel 2007 or earlier
 
    Public Declare Sub Sleep Lib "kernel32" (ByVal Milliseconds As Long)
 
#End If


Sub Macro3()
'
' Macro3 Macro
'


'
    Range("A1:B71").Select
    Selection.Copy
        
    AppActivate "AccuTerm 2K2"
    Sleep 50
        
        Dim myTxt As String
        myTxt = "^+Z" 'Hotkey for client macro
        
    SendKeys myTxt, True   'Pastes into client application
    
    Application.Wait (Now + TimeValue("0:00:07"))
    '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

 

[AlphaFrog]

Look for the AccuTerm type library. See the AccuTerm Programmers guide Page #14 (page 18 in the PDF file). Do a file search for ATWIN2K2.TLB on your computer.

Type Library
AccuTerm 2K2 includes a type library, ATWIN2K2.TLB, which contains
information about AccuTerm’s public objects, including their properties,
methods, events, arguments and constants. When specifying the object type
in a declaration, always use the type library as a reference. This will ensure
compatibility with future versions of AccuTerm. For example, to declare a
VB object variable as type “AccuTerm”, add a reference to “AccuTerm 2K2
Classes” (Project->References), then use the following declaration:

Dim obj As AccuTermClasses.AccuTerm
AccuTerm Object Reference 15

The AccuTerm Object
The AccuTerm object is AccuTerm’s top-level application object.

To access the AccuTerm object of a running instance of AccuTerm, use the following syntax:

Dim obj as Object
Set obj = GetObject(, "ATWin32.AccuTerm")

To create a new instance of AccuTerm, use:

Dim obj as Object
Set obj = CreateObject("ATWin32.AccuTerm")

 

[kbdavis11]

Thank you for the suggestion. I am not at all familiar with integrating into Excel, and I have came across an issue as well.

Firstly, the issue:

Upon pasting the declaration, I receive a "Compile Error: Expected: end of statement" (underline is the highlight portion of the error)

Dim obj As AccuTermClasses.AccuTerm
[COLOR=#ff0000]AccuTerm Object [U]Reference[/U] 15[/COLOR]

Regardless of the error, this would be something that should be in a Class Module, correct?

 

[cerfani]

class module is for making custom objects... like if you want to extend functionality of some other object or keep track of some collection of data and have functions to manipulate or use it... i believe a code module is fine

If you want to write a class that allows you to paste stuff into other apps then yeah maybe you need to make a class for it that you can reuse and call from other vba code.

 

[AlphaFrog]

Thank you for the suggestion. I am not at all familiar with integrating into Excel, and I have came across an issue as well.

Firstly, the issue:

Upon pasting the declaration, I receive a "Compile Error: Expected: end of statement" (underline is the highlight portion of the error)

Dim obj As AccuTermClasses.AccuTerm
[COLOR=#ff0000]AccuTerm Object [U]Reference[/U] 15[/COLOR]

Regardless of the error, this would be something that should be in a Class Module, correct?

Confirm that you found the ATWIN2K2.TLB file and selected it under VBA Tools\ References.

This is my best guess. Otherwise, I don't know what to do. It's most likely that this is not correct.

Put it in a standard code module.

[COLOR=darkblue]Sub[/COLOR] Macro1()
    
    [COLOR=darkblue]Dim[/COLOR] objAT [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    
    [COLOR=green]'To access the AccuTerm object of a running instance of AccuTerm[/COLOR]
    [COLOR=darkblue]Set[/COLOR] objAT = GetObject(, "ATWin32.AccuTerm")
    
    ActiveSheet.Range("A1:B71").Copy
    
    [COLOR=darkblue]With[/COLOR] objAT.ActiveSession  [COLOR=green]'or maybe just: With objAT[/COLOR]
        .InputMode = 1
        [COLOR=green]'.WaitFor 0, 0, "1HCMD (/,?): "[/COLOR]
        .Output "2" & Chr$(13)
        .Paste ""
        .InputMode = 0
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]