I was able to find a solutionoriginal code needs to be more than just converted to 64 bit form...for some reason need to add an extra line of code, see answer below.
============== end of edit
Still doesn't work after proper conversion to 64 bit. CMD window stays open until the WriteLine "exit" code line. hwnd value has been confirmed to be correct.
Since my last edit: Updated code to have proper conversion to W 64 bit (not as easy to do as my previous API functions that didn't use hwnd). Found great resource for proper API VBA declarations from Microsoft. Just download, put into text reader that lets you refine your search and search for the Function or Sub by name to see the full, correct declaration for 64 bit, functions, subs, custom data types, many pages long.
Added a confirmation that the CMD window Hwnd is correct (same handle from FindWindow as from GetWindow).
Strange behavior noticed. If you uncomment the "Stop" at the StopHere label in the HideWindow function, run the test program, then F8 step after it stops, the CMD window disappears. And this is with with just one F8 even though the Close Window codeline has not been executed. If comment out the stophere stop, put a break at BreakPoint3, run, Press F8 when it breaks, again the CMD window disappears.
Also, if you comment out the Stop, put break point at label breakpoint1 in the TestRoutine Sub, run the code, focus is on the CMD window when it hits the breakpoint. Put focus back to VBA code, F5, and the CMD window dissapears.
Put the break at BreakPont2, it is the VBA window that has focus at the break, not the CMD window. Press F5 or F8 and both will see the CMD window disappear.
Putting DoEvents in various places did not help.
The CMD window is visible in the Taskbar when is disappears, until the WriteLine "exit".
If I run put break at BreakPont1 and run it to pull up the CMD window, and in a second instance of Excel run the ShowWindow(hwnd, SW_HIDE) line the CMD window disappears and is no longer in the TaskBar, but it must be just hidden since if I resume the original code, the Debug.Print results/output appears. Both instances of Excel have the same hwnd value.
New Version of Code:
Option Explicit
' ShowWindow() Commands .............. I added declaration type, which is long
'https://learn.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-showwindow
'ShowWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal nCmdShow As Long) As Long
Public Const SW_HIDE As Long = 0 'Hides the window and activates another window.
Public Const SW_MINIMIZE As Long = 6 'Minimizes the specified window and activates the next top-level window in the Z order.
Public Const SW_FORCEMINIMIZE As Long = 11 'Minimizes a window, even if the thread that owns the window is not responding. This flag should only be used when minimizing windows from a different thread.
'-----------------------------------------------------------------------------------------------------------------------------------------------------------
'GetWindow Constant............... I added declaration type which is long
'GetWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal wCmd As Long) As LongPtr
Public Const GW_CHILD As Long = 5
Public Const GW_HWNDFIRST As Long = 0
Public Const GW_HWNDLAST As Long = 1
Public Const GW_HWNDNEXT As Long = 2
Public Const GW_HWNDPREV As Long = 3
Public Const GW_OWNER As Long = 4
'------------------------------------------------- added FindWindow to confirm correct handle-------------------------------------------------------------
Declare PtrSafe Function FindWindow Lib "user32" _
Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As LongPtr
Public Const HWND_TOP = 0 '//moves to top of Zorder
Public Const SWP_NOSIZE = &H1 '//Overwrites cx & cy to not resize window.
'------------------------------------------------------------------------------------------------------------------------------------------------------------
' API Functions PtrSafe added Per "Cheat Sheet" found at https://www.microsoft.com/en-us/download/details.aspx?id=9970
Public Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal nCmdShow As Long) As Long
Public Declare PtrSafe Function GetWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal wCmd As Long) As LongPtr
Public Declare PtrSafe Function GetDesktopWindow Lib "user32" () As LongPtr
Public Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As LongPtr, lpdwProcessId As Long) As Long
Sub TestRoutine()
Dim objShell As Object
Dim oExec As Object
Dim strResults As String
Set objShell = CreateObject("WScript.Shell")
Set oExec = objShell.exec("CMD /K")
Dim lngExecProcessId As Long
lngExecProcessId = oExec.ProcessID 'added intermiate step to confirm data type
BreakHere1: Call HideWindow(lngExecProcessId)
With oExec
.StdIn.WriteLine "Ping 127.0.0.1"
' .StdIn.WriteLine "ipconfig /all" 'don't need this length output test
.StdIn.WriteLine "exit"
Do Until .StdOut.AtEndOfStream
strResults = strResults & vbCrLf & .StdOut.ReadLine
DoEvents
Loop
End With
Set objShell = Nothing 'added this cleanup line just in case
Set oExec = Nothing
Debug.Print strResults
End Sub
Function HideWindow(iProcessID) As Long 'added type to match ShowWindow Type
Dim lptrWinHwnd As LongPtr, lptrWinHwndFindWindow As LongPtr
Do
lptrWinHwnd = GetHwndFromProcess(CLng(iProcessID))
DoEvents
Loop While lptrWinHwnd = 0
'confirm hwnd is correct--------------------------------------------------------------------------------------
lptrWinHwndFindWindow = FindWindow(vbNullString, "C:\WINDOWS\SYSTEM32\CMD.exe") 'Find the handle of the window based on the title
If lptrWinHwndFindWindow <> lptrWinHwnd Then Stop
'end of confirm hwnd is correct--------------------------------------------------------------------------------------
StopHere: 'Stop '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
BreakHere3: DoEvents 'same result with or without DoEvents here
BreakHere2: HideWindow = ShowWindow(lptrWinHwnd, SW_HIDE) ' SW_FORCEMINIMIZE doesnt help $$$$$$$$$$$$$$$
'Long LongPtr , Long
'ShowWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal nCmdShow As Long) As Long
DoEvents
End Function
Function GetHwndFromProcess(p_lngProcessId) As LongPtr 'As Long) As LongPtr
Dim lptrDesktop As LongPtr 'changed to ptr
Dim lptrChild As LongPtr 'changed to ptr
Dim lngChildProcessID As Long 'kept as long
On Error Resume Next
lptrDesktop = GetDesktopWindow()
'LongPtr checked
'GetDesktopWindow Lib "user32" () As LongPtr
lptrChild = GetWindow(lptrDesktop, GW_CHILD)
'LongPtr LongPtr, Long checked
'GetWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal wCmd As Long) As LongPtr
Do While lptrChild <> 0
Call GetWindowThreadProcessId(lptrChild, lngChildProcessID)
' no return long since is a call LongPtr Long checked
'GetWindowThreadProcessId Lib "user32" (ByVal hwnd As LongPtr, lpdwProcessId As Long) As Long
If lngChildProcessID = p_lngProcessId Then 'lngChildProcessID has to be Long so p_lngProcessId has to be long, checked
GetHwndFromProcess = lptrChild 'lptrChild has to be Ptr so the function has to return Ptr. checked
Exit Do
End If
lptrChild = GetWindow(lptrChild, GW_HWNDNEXT)
'LongPtr LongPtr Long checked
'GetWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal wCmd As Long) As LongPtr
Loop
On Error GoTo 0
End Function
=================== end of second edit ===================================
Added my code as requested by Steeeve. Also more info: My code is a copy/paste from the referenced question, into an empty workbook/new module, need to add PtrSafe to the API function declarations to get it to run because I am in 64 bit environment. In my attached code I have commented out my trials...with a $$$$$$$$ identifier. The original copy "as is" before any changes other than PtrSafe and my minor variations do not hide the window. The original code has DoEvents in a do loop, but adding DoEvents after the "Close Window" code did not help...they are commented out below.
Need some advice/hints on what to research to find solution to the referenced code not closing the CMD window.
Please do not label this a duplicate...it is new in that the previous answer is not working as coded, and no other of the many similar answers I found addressed this issue or gave a better alternative.
Brian Burns in 2015 gave a great answer to getting data from a command using WshShell.Exec Method, but this method, unlike WshShell.Run, does not have the option to run the CMD window minimized. This question referenced in my Title provided two workarounds (and actually predated Burns, in 2014, and didn't get nearly as many votes), one using RUN and dumping the output into a file and reading it back to VBA, and one by B Hart adding code to "quick before you see it" close the CMD window. I need to use the Hart solution for my project.
I have had both Burns' and Hart's code working for me. Burns of course has the CMD window always open. Unfortunately, Hart's code did not close the window as I had wanted. I am using W10 Pro, Office 365 Excel/VBA.
I tried a modification to the "ShowWindow" line...looking at the properties of "ShowWindow", Hart did not have an constant option of SW_FORCEMINIMIZE = 11. This is supposed to close the window regardless of threads used. I tried this option, still the CMD window stays open, no visible data, until the EXIT is executed.
Of course had to add PtrSafe to the function declarations for 64 bit W10.
Debugging shows the loop to find a match lngChildProcessID = p_lngProcessId was successful. The return variable HideWindow for ShowWindows has a value of 24, which I guess just means that the window was previously open, does not say it was successfully minimized.
This might have something to do with the problem....the comments section of ShowWindows says "The first time an application calls ShowWindow, it should use the WinMain function's nCmdShow parameter as its nCmdShow parameter. Subsequent calls to ShowWindow must use one of the values in the given list, instead of the one specified by the WinMain function's nCmdShow parameter." This comment is over my head so I do not know if this means cant use Hart's code until some sort of MainWindow call is made. But I get the feeling this comment is not true for new windows opened in Windows.
Any hints would be greatly appreciated.
I am appending Hart's code below for reference.
Option Explicit
' ShowWindow() Commands
Public Const SW_HIDE = 0
Public Const SW_MINIMIZE = 6
'GetWindow Constants
Public Const GW_CHILD = 5
Public Const GW_HWNDFIRST = 0
Public Const GW_HWNDLAST = 1
Public Const GW_HWNDNEXT = 2
Public Const GW_HWNDPREV = 3
Public Const GW_OWNER = 4
' API Functions
Public Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Public Declare Function GetWindow Lib "user32" (ByVal hwnd As Long, ByVal wCmd As Long) As Long
Public Declare Function GetDesktopWindow Lib "user32" () As Long
Public Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwProcessId As Long) As Long
Sub TestRoutine()
Dim objShell As Object
Dim oExec As Object
Dim strResults As String
Set objShell = CreateObject("WScript.Shell")
Set oExec = objShell.Exec("CMD /K")
Call HideWindow(oExec.ProcessID)
With oExec
.StdIn.WriteLine "Ping 127.0.0.1"
.StdIn.WriteLine "ipconfig /all"
.StdIn.WriteLine "exit"
Do Until .StdOut.AtEndOfStream
strResults = strResults & vbCrLf & .StdOut.ReadLine
DoEvents
Loop
End With
Set oExec = Nothing
Debug.Print strResults
End Sub
Function HideWindow(iProcessID)
Dim lngWinHwnd As Long
Do
lngWinHwnd = GetHwndFromProcess(CLng(iProcessID))
DoEvents
Loop While lngWinHwnd = 0
HideWindow = ShowWindow(lngWinHwnd, SW_MINIMIZE)
End Function
Function GetHwndFromProcess(p_lngProcessId As Long) As Long
Dim lngDesktop As Long
Dim lngChild As Long
Dim lngChildProcessID As Long
On Error Resume Next
lngDesktop = GetDesktopWindow()
lngChild = GetWindow(lngDesktop, GW_CHILD)
Do While lngChild <> 0
Call GetWindowThreadProcessId(lngChild, lngChildProcessID)
If lngChildProcessID = p_lngProcessId Then
GetHwndFromProcess = lngChild
Exit Do
End If
lngChild = GetWindow(lngChild, GW_HWNDNEXT)
Loop
On Error GoTo 0
End Function
Added my version as requested on edit.
Option Explicit
' ShowWindow() Commands
'https://learn.microsoft.com/en-us/windows/win32/api/winuser/nf-winuser-showwindow
Public Const SW_HIDE = 0
Public Const SW_MINIMIZE = 6
Public Const SW_FORCEMINIMIZE = 11 'added $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
'GetWindow Constants
Public Const GW_CHILD = 5
Public Const GW_HWNDFIRST = 0
Public Const GW_HWNDLAST = 1
Public Const GW_HWNDNEXT = 2
Public Const GW_HWNDPREV = 3
Public Const GW_OWNER = 4
' API Functions............. PtrSafe added $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Public Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Public Declare PtrSafe Function GetWindow Lib "user32" (ByVal hwnd As Long, ByVal wCmd As Long) As Long
Public Declare PtrSafe Function GetDesktopWindow Lib "user32" () As Long
Public Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwProcessId As Long) As Long
Sub TestRoutine()
Dim objShell As Object
Dim oExec As Object
Dim strResults As String
Set objShell = CreateObject("WScript.Shell")
Set oExec = objShell.exec("CMD /K")
'DoEvents 'uncommenting DoEvents does not help $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
Call HideWindow(oExec.ProcessID)
'DoEvents 'uncommenting DoEvents does not help $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
With oExec
.StdIn.WriteLine "Ping 127.0.0.1"
.StdIn.WriteLine "ipconfig /all"
.StdIn.WriteLine "exit"
Do Until .StdOut.AtEndOfStream
strResults = strResults & vbCrLf & .StdOut.ReadLine
DoEvents
Loop
End With
Set oExec = Nothing
Debug.Print strResults
End Sub
Function HideWindow(iProcessID)
Dim lngWinHwnd As Long
Do
lngWinHwnd = GetHwndFromProcess(CLng(iProcessID))
DoEvents
Loop While lngWinHwnd = 0
HideWindow = ShowWindow(lngWinHwnd, SW_MINIMIZE) ' SW_FORCEMINIMIZE doesnt help $$$$$$$$$$$$$$$
End Function
Function GetHwndFromProcess(p_lngProcessId As Long) As Long
Dim lngDesktop As Long
Dim lngChild As Long
Dim lngChildProcessID As Long
On Error Resume Next
lngDesktop = GetDesktopWindow()
lngChild = GetWindow(lngDesktop, GW_CHILD)
Do While lngChild <> 0
Call GetWindowThreadProcessId(lngChild, lngChildProcessID)
If lngChildProcessID = p_lngProcessId Then
GetHwndFromProcess = lngChild
Exit Do
End If
lngChild = GetWindow(lngChild, GW_HWNDNEXT)
Loop
On Error GoTo 0
End Function
