×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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 Auto_Open()
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

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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!

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

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 paste the contents there, and then exit the macro."


---- Andy

There is a great need for a sarcasm font.

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

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: How to use the Watch Window as a Power Programming Tool 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
Thank you, Skip - I will check that out!

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

HAPPY HOLIDAYS, ALL!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close