VBA interaction between Excel and another program

[ZenZilla]

Hey all,

Im tyring to write a VBA that will copy/paste between excel and another program, that program being Attachmate Reflections which also works with VBA macros.

I've built a few test iterations as I am not very familiar with Visual Basic, and I am slowly getting the hang of things however I've run into a problem I cannot seem to solve.

My current test code is as follows:


Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Test1()

Dim hostSettleTimeout As Integer
Dim hostSettleTime As Integer
Dim ibmCurrentScreen As IbmScreen
Dim ibmCurrentTerminal As IbmTerminal
Dim returnValue As Integer
Dim hiddenTextEntry As String
Dim MyScreen As IbmScreen


hostSettleTimeout = 30000
hostSettleTime = 3000

Set ibmCurrentTerminal = ThisFrame.SelectedView.Control
Set ibmCurrentScreen = ibmCurrentTerminal.Screen
Call ibmCurrentScreen.SetSelectionStartPos(15, 23)
Call ibmCurrentScreen.ExtendSelectionRect(15, 28)
Call ibmCurrentScreen.Copy

AppActivate ("Microsoft Excel")
Sleep 50
SendKeys "^v"
Sleep 50

End Sub

The frustrating part is that this was working moments before I added a line and then removed it. Now it doesn't paste anymore. If I change the "^v" to something simple say, "111" it correctly enters 111 into the active cell in excel, so I don't believe its a timing issue. I have no idea whats happening...

My other issue is that I cant seem to send an ENTER command through SendKeys either. I've tried using {ENTER}, "~", Char13. None worked.

Thanks for any help!

 

[BiocideJ]

I am posting this from memory, but I recall that using

Application.SendKeys
is a more preferred (and less buggy) way to use SendKeys.

Not sure if that is specifically what is causing your problems, however, because I don't have the secondary program to check.
Also, I sometimes put a DoEvents command before using SendKeys to make sure the app has an extra chance to be ready.

 

[ZenZilla]

I am posting this from memory, but I recall that using

Application.SendKeys
is a more preferred (and less buggy) way to use SendKeys.

Not sure if that is specifically what is causing your problems, however, because I don't have the secondary program to check.
Also, I sometimes put a DoEvents command before using SendKeys to make sure the app has an extra chance to be ready.

Application.sendkeys just returns run-time error 438: Object doesn't support this property or method.

The DoEvents shouldn't be needed as I have a delay already in place. Not to mention the fact that if I just enter in a simple command like SendKeys "111" it properly types 111 into excel....

 

[ZenZilla]

I've done some further testing to try and narrow down what isn't working. If I change the AppActivate command to reference an open notepad it properly pastes in the selected text. For some reason it is only excel that is not allowing the command to be properly executed, but it works with other sendkey commands as I mentioned such as SendKeys "100".

I'm completely confused

 

[BiocideJ]

OK. Had to look up the function (that's the problem of posting from memory).

The syntax would be
Application.SendKeys ("^v",True)

This basically should cause the sendkeys command to not return control to the macro until it is completed.

also, DoEvents =/= Sleep
DoEvents actually allows other processes outside of Excel to get control of the processor whereas Sleep just causes the processor to not do anything in the calling application for the specified time interval. (this may not be the technically correct explanation, but it is functionally accurate in my experience)


If this still doesn't work, perhaps there is some InterOperability Reference under Tools>References of the VBA Window that would allow you to set an object for the other program and control it that way. (similar to how you can run macros from Excel that control another Microsoft application)
EDIT: Actually, this second option should be HIGHLY favored as SendKeys (even when you get it working) is not always the most reliable way to control another window.

 

[sirluke34]

Not sure if you have to use Excel to work between the two programs, but you might want to check out a small program called "AutoHotKey". It was built for exactly this type of function and is probably much more stable. They have lots of examples and the community is great, just like it is here. It is a scripting language, but it's pretty easy to grasp.

Example script to copy between programs:

[COLOR=#880000]#SingleInstance force[/COLOR]

[COLOR=#666600];[/COLOR] YOU CAN DEFINE A SPECIFIC WINDOW TO COPY FROM
[COLOR=#666600];[/COLOR]   [COLOR=#660066]If[/COLOR] you don[COLOR=#008800]'t it prompts all open windows to choose
;   If you do it will get you right to this one
;     (which it may contain your copy-clips):
defineWindow = 

Sleep, 100
WinGetActiveTitle, WorkingWindowTitle      ; Retrieve working window title
clipsaved := clipboardAll 	; to leave Clipboard untouched...
Sleep, 100
clipboard =               	; empty clipboard

if (defineWindow)
 {
  WinActivate, %defineWindow%
  GoSub, FinalAction
 }

Winget, AllWins, list                ; get all windows'[/COLOR] [COLOR=#660066]IDs[/COLOR]
 [COLOR=#660066]Loop[/COLOR][COLOR=#666600],[/COLOR] [COLOR=#666600]%[/COLOR][COLOR=#660066]AllWins[/COLOR][COLOR=#666600]%[/COLOR]
 [COLOR=#666600]{[/COLOR]
  [COLOR=#660066]Wingettitle[/COLOR][COLOR=#666600],[/COLOR] [COLOR=#660066]WinTitle[/COLOR][COLOR=#666600],[/COLOR] [COLOR=#666600]%[/COLOR] [COLOR=#008800]"ahk_id "[/COLOR] [COLOR=#660066]AllWins[/COLOR][COLOR=#666600]%[/COLOR]A_Index[COLOR=#666600]%[/COLOR]
  [COLOR=#660066]WinTitle[/COLOR][COLOR=#666600]%[/COLOR]A_Index[COLOR=#666600]%[/COLOR] [COLOR=#666600]=[/COLOR] [COLOR=#666600]%[/COLOR][COLOR=#660066]WinTitle[/COLOR][COLOR=#666600]%[/COLOR]     [COLOR=#666600];[/COLOR] store titles [COLOR=#000088]in[/COLOR] array
 [COLOR=#666600]}[/COLOR]
[COLOR=#660066]WinCount[/COLOR] [COLOR=#666600]=[/COLOR] [COLOR=#006666]0[/COLOR]
[COLOR=#660066]Gui[/COLOR] [COLOR=#660066]Font[/COLOR][COLOR=#666600],[/COLOR] s8[COLOR=#666600],[/COLOR] [COLOR=#660066]Tahoma[/COLOR]
[COLOR=#660066]Gui[/COLOR] [COLOR=#660066]Margin[/COLOR][COLOR=#666600],[/COLOR] [COLOR=#006666]9[/COLOR][COLOR=#666600],[/COLOR][COLOR=#006666]5[/COLOR]
[COLOR=#660066]Loop[/COLOR] [COLOR=#666600]%[/COLOR][COLOR=#660066]AllWins[/COLOR][COLOR=#666600]%[/COLOR]                      [COLOR=#666600];[/COLOR] list win titles [COLOR=#000088]in[/COLOR] GUI
 [COLOR=#666600]{[/COLOR]
  [COLOR=#660066]If[/COLOR] [COLOR=#660066]WinTitle[/COLOR][COLOR=#666600]%[/COLOR]A_Index[COLOR=#666600]%[/COLOR] [COLOR=#000088]in[/COLOR] [COLOR=#666600],[/COLOR][COLOR=#660066]Program[/COLOR] [COLOR=#660066]Manager[/COLOR][COLOR=#666600],%[/COLOR][COLOR=#660066]WorkingWindowTitle[/COLOR][COLOR=#666600]%[/COLOR]
   [COLOR=#660066]Continue[/COLOR]                      [COLOR=#666600];[/COLOR] [COLOR=#660066]Exclude[/COLOR] [COLOR=#000088]no[/COLOR][COLOR=#666600]-[/COLOR]name[COLOR=#666600],[/COLOR] [COLOR=#660066]Program[/COLOR] manager[COLOR=#666600],[/COLOR] working win
  [COLOR=#660066]But[/COLOR] [COLOR=#666600]:=[/COLOR] [COLOR=#660066]WinCount[/COLOR] [COLOR=#666600]+[/COLOR] [COLOR=#006666]1[/COLOR]
  [COLOR=#660066]Gui[/COLOR] [COLOR=#660066]Add[/COLOR][COLOR=#666600],[/COLOR] [COLOR=#660066]Text[/COLOR][COLOR=#666600],[/COLOR] x5 gexec[COLOR=#666600],[/COLOR] [COLOR=#666600]%[/COLOR] [COLOR=#660066]WinTitle[/COLOR][COLOR=#666600]%[/COLOR]A_Index[COLOR=#666600]%[/COLOR]
  [COLOR=#660066]Gui[/COLOR] [COLOR=#660066]Add[/COLOR][COLOR=#666600],[/COLOR] [COLOR=#660066]Button[/COLOR][COLOR=#666600],[/COLOR] w0 h0 gExec[COLOR=#666600],[/COLOR] [COLOR=#666600]%[/COLOR] [COLOR=#660066]WinTitle[/COLOR][COLOR=#666600]%[/COLOR]A_Index[COLOR=#666600]%[/COLOR]
  [COLOR=#660066]WinCount[/COLOR][COLOR=#666600]++[/COLOR]
 [COLOR=#666600]}[/COLOR]
[COLOR=#000088]if[/COLOR] [COLOR=#666600](![/COLOR][COLOR=#660066]WinCount[/COLOR][COLOR=#666600])[/COLOR]                      [COLOR=#666600];[/COLOR] [COLOR=#660066]If[/COLOR] [COLOR=#000088]no[/COLOR] other window [COLOR=#000088]is[/COLOR] open
 [COLOR=#666600]{[/COLOR]
  [COLOR=#660066]MsgBox[/COLOR][COLOR=#666600],[/COLOR] [COLOR=#666600],[/COLOR] [COLOR=#660066]FastCopyPaste[/COLOR][COLOR=#666600],[/COLOR] [COLOR=#660066]Hey[/COLOR] [COLOR=#000088]friend[/COLOR] you don[COLOR=#008800]`'t have any other window. Exit.
  WinActivate, %WorkingWindowTitle%
  clipboard = clipsaved             ; Restore Clipboard
  Exitapp
 }

Gui, Show, , FastCopyPaste
WinGetPos, , , WW, , FastCopyPaste        ; Repos win if too small to show title
if (WW<500)
 {
  sysget, Mon, Monitorworkarea
  scwidth := MonRight - MonLeft
  WinMove, FastCopyPaste, , (scwidth/2)-(250), , 500
 }

; show hand cursor
hCurs:=DllCall("LoadCursor","UInt",NULL,"Int",32649,"UInt")
OnMessage(0x200,"WM_MOUSEMOVE") ;cursor function

Return

; --- END OF AUTOEXEC SECTION

Exec:
WinActivate, %A_GuiControl%
FinalAction:
Sleep, 50
CoordMode, ToolTip, Screen
ToolTip, Select what you want to copy to Window •%WorkingWindowTitle%• and press Esc, 0, 0
KeyWait, Esc, D
ToolTip
send ^c
ClipWait, 0.5, 1
WinActivate, %WorkingWindowTitle%
if (!clipboard)
 {
  MsgBox, Nothing was selected/copied. Exit.
  WinActivate, %WorkingWindowTitle%
  clipboard := clipsaved             ; Restore Clipboard
  Exitapp
 }
sleep 100
Send, ^v
clipboard := clipsaved    ; Restore Clipboard
Exitapp

GuiEscape:
GuiClose:
Exitapp
Return

WM_MOUSEMOVE(wParam,lParam)
{
 IfWinActive, FastCopyPaste
  {
   Global hCurs
   MouseGetPos,,,,ctrl
   ifinstring, ctrl, Static
   DllCall("SetCursor","UInt",hCurs)
  }
 Return
}[/COLOR]

 

[wellsr]

If all you're trying to do is copy and paste, it'd be better to use clipboard controls than to use sendkeys - which is notoriously flaky. It looks like you'll only need to figure out how to paste since you're copying okay from Reflections. To use the clipboard controls, you'll have to add a reference to Microsoft Forms 2.0 Object Library.

(1) Open your VBA editor.​

(2) Click Tools > References.​

(3) Check the box next to “Microsoft Forms 2.0 Object Library.”​

(4) Press “OK.”​

The process would be similar if you're using Attachmate Reflections to host your macros.

Sub PasteFromClipboard()
    Dim clipboard As MSForms.DataObject
    Dim str1 As String


    Set clipboard = New MSForms.DataObject
    
    clipboard.GetFromClipboard
    str1 = clipboard.GetText
End Sub

You would set whatever cell (or range) you want equal to the value stored in the variable str1 at the end of the above example.

Source: VBA Control Your Clipboard (Disclaimer: My article)

 

[ZenZilla]

Application.SendKeys "^v",True does not work. I believe reflection does not support this. DoEvents also does not make a difference compared to Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long).

@wellsr, at the end of the day this is what I would like the macro to do:

1. Copy a string of text from an Excel cell (Lets say A2)
2. Paste this into specific coordinates into reflections
3. press enter
4. Check if resultant page is one of two possible formats. (Should be as easy as checking the text in a specific location)
5. Copy text from reflection (location determined by above determination of format type)
6. Paste into another excel cell (Lets say B2)
7. Move down to next set and copy (ie A3)

Repeat 10,000 times.