Excel 2013 macro working erratically in AppSphere (VDI) platform running Server 2008

[Sparky1157]

I was running an Excel 2010 macro on a Gen-Y VDI system running Windows 7 and everything worked just fine. Now, however, we upgraded to Excel 2013 with an AppSphere VDI system running Server 2008. The macro now behaves very erratic - sometimes it does nothing, and sometimes it will get part way through and then just quit.

Please see code below:

Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hWnd As Integer) As Integer
Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Integer
Declare Function IsIconic Lib "user32.dll" (ByVal hWnd As Integer) As Boolean
Declare Function ShowWindow Lib "user32.dll" (ByVal hWnd As Integer, ByVal nCmdShow As Integer) As Integer
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Const SW_RESTORE As Integer = 9
Const SW_SHOW As Integer = 5

Sub Autpen()
Dim WshShell As Object
Dim hWnd As Integer
Dim Temp As Integer 'Dummy variable - see below
hWnd = FindWindow(vbNullString, "Inbox - ... Outlook")
If hWnd > 0 Then
SetForegroundWindow (hWnd)

If IsIconic(hWnd) Then 'Restore if minimized
Temp = ShowWindow(hWnd, SW_RESTORE) 'Temp is meaningless, but compiler wanted "=" sign
Else
Temp = ShowWindow(hWnd, SW_SHOW)
End If

SendKeys ("{Tab}")
Sleep 200 ' Pause for 1/5 of a second.
SendKeys ("{Tab}")
Sleep 200 ' Pause for 1/5 of a second.
SendKeys ("{Tab}")
Sleep 200 ' Pause for 1/5 of a second.
SendKeys ("{Tab}")
Sleep 200 ' Pause for 1/5 of a second.
SendKeys ("{Down}")
Sleep 100 ' Pause for 1/10 of a second.
SendKeys ("{Down}")
Sleep 100 ' Pause for 1/10 of a second.
SendKeys ("{Down}")
Sleep 100 ' Pause for 1/10 of a second.
SendKeys ("{Down}")
Sleep 100 ' Pause for 1/10 of a second.
SendKeys ("{Down}")
Sleep 100 ' Pause for 1/10 of a second.
SendKeys ("{Enter}")
Sleep 1000 ' Pause for 1 second.
SendKeys ("{Enter}")
Sleep 1000 ' Pause for 1 second.
SendKeys ("{Tab}")
Sleep 200 ' Pause for 1/5 of a second.
SendKeys ("{Tab}")
Sleep 200 ' Pause for 1/5 of a second.
SendKeys ("{Tab}")
Sleep 200 ' Pause for 1/5 of a second.
SendKeys ("{Tab}")
Sleep 200 ' Pause for 1/5 of a second.
SendKeys ("^a")
Sleep 100 ' Pause for 1/10 of a second.
SendKeys ("^c")
Sleep 100 ' Pause for 1/10 of a second.
SendKeys ("^n")
Sleep 1000 ' Pause for 1 second.
SendKeys ("{Tab}")
Sleep 100 ' Pause for 1/10 of a second.
SendKeys ("{Tab}")
Sleep 100 ' Pause for 1/10 of a second.
SendKeys ("{Tab}")
Sleep 100 ' Pause for 1/10 of a second.
SendKeys ("{Tab}")
Sleep 100 ' Pause for 1/10 of a second.
SendKeys ("^a")
Sleep 100 ' Pause for 1/10 of a second.
SendKeys ("^v")
End If
'
' Quit Excel
'
Application.Quit
End Sub

 

[SkipVought]

Hi,

Let me ask you something. Would you EVER get into your car and decide before leaving that you would stop and wait at every stop sign for 10 seconds before proceeding, at which time you would blindly accelerate through each intersection?

I’m guessing that you would answer in the negative.

So why do you wait (sleep) a fixed length of time after issuing a command to an asynchronous system (some system outside of your Excel application)?

Like stoppng at an intersection, you issue a command to another system. You must wait until you reveive some sort of feedback that it is time to proceed. In the case of the intersection, you look for information that indicates a clear and safe intersection. In the case of your Excel VBA program, you need to look for an indication from the system that the system is ready for processing.

Sleep whatever is totally inadequate!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Sparky1157]

Thanks, Skip! Constructive criticism is always welcome.

I must admit that I'm not an expert at VBA programming, so my attempt was apparently clumsy at best. However, the purpose of my posting was to obtain a solution to the problem - could you please point me in the right direction on what option(s) to explore to help myself improve the macro?

I sincerely do want to learn, and the original macro did not work without the "sleep" commands. I did try the WinWaitActive command, but wasn't successful in getting it to work....

Best regards,

Steve

 

[SkipVought]

We need to know what system you are sending commands to?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Sparky1157]

My apologies - I thought I had made that clear in my original post as to what system I was working on. It was recently upgraded from a Gen-Y VDI system running Windows 7 to an AppSphere VDI system running Server 2008.

If you're interesting in what application I'm sending the commands to, as per the FindWindow command in my macro I'm working with the Microsoft Outlook (2013) inbox....

Please let me know if I can supply any other pertinent information to help you in helping me!

 

[SkipVought]

So your program is sending commands to Outlook and hence,waiting for Outlook to process that command and then what?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Sparky1157]

I'm going to my inbox, then going to my drafts folder where I open a stored email, copy it's contents, open a new email window, copy the contents there, and then exit the macro.

The point is to automate opening a new email with pre-stored information that I can then modify and subsequently send the email myself. Once the macro runs I then complete the editing process manually and send the email ... that's all there is to it.

 

[Andrzejek]

Did you mean:
"I'm going to my inbox, then going to my drafts folder where I open a stored email, copy it's contents, open a new email window, copy [blue]paste[/blue] the contents there, and then exit the macro."


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Why are you running this in Excel? Why not in outlook?

Rather than trying to automate via the user interface, why not use Outlook Object Model objects to copy-paste your stored email object as needed.

Check out faq707-4594 as a tool for discovery. I’ve never programmed much in or using the Outlook Object Model, but when I did, I used this technique along with the Object browser to figure out where things were and what properties were available and the values I was expecting to see as I stepped through my code in discovery.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Sparky1157]

Thank you, Skip - I will check that out!

And, Andy - yes, I meant to type "paste" - my apologies for any confusion!

HAPPY HOLIDAYS, ALL!