Results 1 to 15 of 15
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Trouble with a MSKB article (Excel 97> VBA)

    <P ID="edit" class=small>Edited by Andrew Cronnolly on 07-Nov-01 16:39.</P>Edit to include hyperlink

    Ladies and gentlemen

    I have found an interesting MS Knowledge Base article number <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q129/7/96.asp?LN=EN-GB&SD=gn&FR=0>Q129796</A>.

    I copied the code into a module and I tried to compile with Option Explicit and it told me that there is a variable that I did not defined.

    I need your help in knowing what should I do with that variable. Here is the code that I think is the problem:


    Public Function ExecCmd(cmdline$)
    Dim proc As PROCESS_INFORMATION
    Dim start As STARTUPINFO

    '/Initialize the STARTUPINFO structure:
    start.cb = Len(start)

    '/Start the shelled application:
    Bold[ret&] = CreateProcessA(vbNullString, cmdline$, 0&, 0&, 1&, _
    NORMAL_PRIORITY_CLASS, 0&, vbNullString, start, proc)

    '/Wait for the shelled application to finish:
    ret& = WaitForSingleObject(proc.hProcess, INFINITE) ret& is the undefined variable...
    Call GetExitCodeProcess(proc.hProcess, ret&)
    Call CloseHandle(proc.hThread)
    Call CloseHandle(proc.hProcess)
    ExecCmd = ret&
    End Function

    OK this also needs the following lines:

    Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
    lpApplicationName As String, ByVal lpCommandLine As String, ByVal _
    lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
    ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
    ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, _
    lpStartupInfo As STARTUPINFO, lpProcessInformation As _
    PROCESS_INFORMATION) As Long

    I know it should be declared as Long but how do I do that in the function? If I write:

    Dim ret& As Long it does not work?!

    Should I rename it?

    Thanks

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Trouble with a MSKB article (Excel 97> VBA)

    Hi Wassim,
    Have you defined the STARTUPINFO and PROCESS_INFORMATION types and declared the INFINITE and NORMAL_PRIORITY_CLASS constants?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Trouble with a MSKB article (Excel 97> VBA)

    You are really declaring the variable as long twice. Dim Ret& is th esame as Dim Ret As Long. I would suggest you use the latter.

    Andrew C

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Trouble with a MSKB article (Excel 97> VBA)

    Rory

    Yes I am. I have the following code doing it:

    Private Type STARTUPINFO
    cb As Long
    lpReserved As String
    lpDesktop As String
    lpTitle As String
    dwX As Long
    dwY As Long
    dwXSize As Long
    dwYSize As Long
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute As Long
    dwFlags As Long
    wShowWindow As Integer
    cbReserved2 As Integer
    lpReserved2 As Long
    hStdInput As Long
    hStdOutput As Long
    hStdError As Long
    End Type

    Private Type PROCESS_INFORMATION
    hProcess As Long
    hThread As Long
    dwProcessID As Long
    dwThreadID As Long
    End Type

    I did rename it and declared it in the function as Dim lRetValue As Long, it seem to work OK.

    I hate to get a MSKB article that does not work. I think of these sample code as tried and tested. But they some time do fail.

    Thanks

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Trouble with a MSKB article (Excel 97> VBA)

    Andrew

    Yes I do know that Ret& is long, but does:

    Ret& = ???? mean its declared? Or must we still do a

    Sub BlahBlah()
    Dim Ret as ????
    End Sub

    BTW I did change the name and declared it in the function since it was the only place used...It seems to work...

    Thanks

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Trouble with a MSKB article (Excel 97> VBA)

    If you use Option Explicit you need to use:
    Dim ret&
    or
    Dim ret as Long
    You can't use
    Dim ret& as Long
    as you discovered because the & on the end declares it implicitly to be a long datatype, so it would be like using
    Dim ret as long as long
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Trouble with a MSKB article (Excel 97> VBA)

    Rory

    I need your help..

    Is it possible to make this API use a Minimized window, I don't know how to find the property that would make it use a minimized window.

    Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
    lpApplicationName As String, ByVal lpCommandLine As String, ByVal _
    lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
    ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
    ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As String, _
    lpStartupInfo As STARTUPINFO, lpProcessInformation As _
    PROCESS_INFORMATION) As Long

    Also is it possible to make the user unable to open the window if they see it in the taskbar and click it?

    Thanks for your help.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  8. #8
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Trouble with a MSKB article (Excel 97> VBA)

    Andrew

    I need your help.

    Would you take a look at the thread and see if you can help me with the message I sent to Rory?

    It has to do with minimizing the window and locking it.

    Thanks

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Trouble with a MSKB article (Excel 97> VBA)

    Cobbling a couple things together...

    In that STARTUPINFO structure you'll notice the option wShowWindow. <A target="_blank" HREF=http://msdn.microsoft.com/library/en-us/dnw98bk/html/makingmodifyingprocesses.asp>This MSDN article</A> says that the integer value for this option follows the conventions for iCmdShow, which appears from a search to be the same value used in the ShowWindow API call. Win32API.txt lists the following possible values:

    ' ShowWindow() Commands
    Const SW_HIDE = 0
    Const SW_SHOWNORMAL = 1
    Const SW_NORMAL = 1
    Const SW_SHOWMINIMIZED = 2
    Const SW_SHOWMAXIMIZED = 3
    Const SW_MAXIMIZE = 3
    Const SW_SHOWNOACTIVATE = 4
    Const SW_SHOW = 5
    Const SW_MINIMIZE = 6
    Const SW_SHOWMINNOACTIVE = 7
    Const SW_SHOWNA = 8
    Const SW_RESTORE = 9
    Const SW_SHOWDEFAULT = 10
    Const SW_MAX = 10

    In addition to setting wShowWindow, the MSDN article says that you need to set dwFlags, or else the setting will be ignored. It lists the following likely suspect: STARTF_USESHOWWINDOW. Win32API.txt lists this assignment:

    Const STARTF_USESHOWWINDOW = &H1

    I think that's the same as the decimal value 1.

    Saving frequently and experimenting...does setting these two values hide the window?

  10. #10
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Trouble with a MSKB article (Excel 97> VBA)

    jscher2000
    Thanks a lot. This is what I was looking for. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Trouble with a MSKB article (Excel 97> VBA)

    Can you post the code for creating the variable and setting the values in the STRUCT? I have a similar problem in another application it would be a useful reference. Thanks.

  12. #12
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Trouble with a MSKB article (Excel 97> VBA)

    jscher2000

    Refer to the following MS Knowledge Base article Q129796. This is where I got the code from.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Trouble with a MSKB article (Excel 97> VBA)

    Where did you insert the new lines to set dwFlags and wShowWindow? Before the last line below? Can you sketch in the details?

    <pre>Dim start As STARTUPINFO

    ' Initialize the STARTUPINFO structure:
    start.cb = Len(start)</pre>


  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Trouble with a MSKB article (Excel 97> VBA)

    Jefferson,

    If I may but in, adding the SW constants and STARTF_USESHOWWINDOW = &H1 which you have shown above, if you just include the following lines in the ExecCmd function (wShowWindow and dwFlags are elements of the Type STARTUPINFO, an instance of which is declared as start in the code) : <pre> start.wShowWindow = SW_HIDE ' substitute rquired window state.
    start.dwFlags = STARTF_USESHOWWINDOW </pre>

    If have tested the above with a batch file and it works fine. However if you are hiding the shelled window, it is very important the application involved is self terminating, as otherwise your calling application will never get control back. I did find a problem with starting in minimised state, in that if the window was restored, it never properly and re-minimised, an image of the window actually remains on the screen. So most likely the code above needs some tweaking, or else use the SetWindowPos API to control the appearance.

    Hope it helps, and most likely you will refine it.

    Andrew C

  15. #15
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Trouble with a MSKB article (Excel 97> VBA)

    jscher2000

    Sorry I missed your message. Here is how I did it:

    With start
    .cb = Len(start)
    If Not IsMissing(lWindowStyle) Then
    .dwFlags = STARTF_USESHOWWINDOW
    .wShowWindow = lWindowStyle
    End If
    End With

    where start is Dim start As STARTUPINFO, and I pass the lWindowStyle to the function
    lReturnValue = ExecCmd("notepad.exe", 2) 2 = Minimized with Focus.

    HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •