UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA Function To Findwindow Using Wildcard, Access 2016    
 
   
justair07
post Jul 10 2018, 10:55 AM
Post#1



Posts: 759
Joined: 22-August 13



Hello,

I have an Excel who's title partially changes periodically. Using API and the FinWindow function, I'm able to control the application. But if the title changes at all it stops working. I was hoping for a way to apply a wildcard so I can work around this issue. Heres the API function I'm using:

CODE
Public Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" ( _
     ByVal lpClassName As String, _
     ByVal lpWindowName As String) As Long


And here is my call:

CODE
Sub APITest()

dbwnd = FindWindow(vbNullString, "MyExcel.xlsx")
    
    Call ShowWindow(dbwnd, SW_RESTORE)

End Sub


Thank you for any help provided,

- Justin
This post has been edited by justair07: Jul 10 2018, 10:55 AM
Go to the top of the page
 
ADezii
post Jul 10 2018, 12:56 PM
Post#2



Posts: 2,114
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. This was a tough nut to crack, but here goes:
  2. Required Declarations:
    CODE
    Public Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Public Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal nCmdShow As Long) As Long
    Public Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long
    Public Declare PtrSafe Function GetWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal wCmd As Long) As Long
    Declare PtrSafe Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" (ByVal hwnd As LongPtr) As Long
    Public Const GW_HWNDNEXT = 2
  3. Function Definition:
    CODE
    Public Function GetHandleFromPartialCaption(ByRef lWnd As Long, ByVal sCaption As String) As Boolean
    Dim lhWndP As Long
    Dim sStr As String

    GetHandleFromPartialCaption = False
    lhWndP = FindWindow(vbNullString, vbNullString)

    Do While lhWndP <> 0
      sStr = String(GetWindowTextLength(lhWndP) + 1, Chr$(0))
      GetWindowText lhWndP, sStr, Len(sStr)
      sStr = Left$(sStr, Len(sStr) - 1)
        If InStr(1, sStr, sCaption) > 0 Then
          GetHandleFromPartialCaption = True
            lWnd = lhWndP
              Exit Do
        End If
          lhWndP = GetWindow(lhWndP, GW_HWNDNEXT)
    Loop
    End Function
  4. Sample Call to Function. Substitute your own Values for the conPARTIAL_TITLE USER DEFINED CONSTANT. If the Constant (in this case 8874) appears 'anywhere' in the Window Title of an 'Excel' Window, the Code will display a Dialog Box indicating success as well as letting you know the Value of the Window Handle containing the Partial Title. After this, you can use ShowWindow() to display the Window.
    CODE
    '************** USER DEFINED **************
    Const conPARTIAL_TITLE As String = "8874"
    '******************************************
    Dim lhWndP As Long

    If GetHandleFromPartialCaption(lhWndP, " - Excel") = True Then      'Is it an Excel Window
      If GetHandleFromPartialCaption(lhWndP, conPARTIAL_TITLE) = True Then   'Excel Window, does it contain the Partial Title?
        MsgBox "An Excel Window with a Partial Title of " & conPARTIAL_TITLE & " has been found!. " & _
               "The Handle to this Window is [" & lhWndP & "].", vbInformation, "Window Found"
        
      Else
        MsgBox "An Excel Window with a Partial Title of " & conPARTIAL_TITLE & " has NOT been found!", _
                vbExclamation, "Window NOT Found"
      End If
    Else
      MsgBox "No Excel Windows are Open!", vbExclamation, "Excel Window Not Found"
    End If
  5. I have tested this Code, not extensively as I would have liked, but it does appear to work quite well. The rest I leave to you.
  6. Good Luck with your Project!

This post has been edited by ADezii: Jul 10 2018, 12:57 PM
Go to the top of the page
 
justair07
post Jul 10 2018, 01:32 PM
Post#3



Posts: 759
Joined: 22-August 13



ADezii,

This is pretty cool. The part that changes is the "- Excel". So my file might be named MyExcel.xlsx but the caption changes from "MyExcel.xlsx - Excel" to "MyExcel.xlsx - Saved"

With that being said, I think I don't need the first IF statement. I commented it out and it seems to work well. What do you think? I'll continue to test as well.

Thanks,

- Justin
Go to the top of the page
 
ADezii
post Jul 10 2018, 01:41 PM
Post#4



Posts: 2,114
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
I think I don't need the first IF statement. I commented it out and it seems to work well. What do you think?

It should work well unless the Title Search String exists in a non Excel Window.
Go to the top of the page
 
justair07
post Jul 10 2018, 01:43 PM
Post#5



Posts: 759
Joined: 22-August 13



Yup! Works great! Thank you so much!! fundrink.gif
Go to the top of the page
 
ADezii
post Jul 10 2018, 01:50 PM
Post#6



Posts: 2,114
Joined: 4-February 07
From: USA, Florida, Delray Beach


yw.gif Just keep in mind that the Code will find the 1st Window that has a Title with the partial Search String, and NOT any subsequent Windows.
This post has been edited by ADezii: Jul 10 2018, 01:55 PM
Go to the top of the page
 
justair07
post Jul 10 2018, 02:04 PM
Post#7



Posts: 759
Joined: 22-August 13



Good to know! like.png
Go to the top of the page
 
justair07
post Jul 11 2018, 07:55 AM
Post#8



Posts: 759
Joined: 22-August 13



Hi ADezii,

Is it possible to for the code to work for any file type? For example if the window open is a PDF and it's name is EHS-114-FLOW Rev 7.pdf but the Rev number changes periodically. Here is what i tried:

CODE
'************** USER DEFINED **************
Const conPARTIAL_TITLE As String = "EHS-114-FLOW"
'******************************************
Dim lhWndP As Long

If GetHandleFromPartialCaption(lhWndP, conPARTIAL_TITLE) = True Then
        Call ShowWindow(lhWndP, SW_MINIMIZE)

    Else
End If



EDIT:
After further testing it appears that code works great if the window is already open. But... if the file is closed and I use my code to open the file and then try to minimize it, the SW_MINIMIZE does not work even though the handle is found. Here is my full code:

CODE
Dim strFolder As String
Dim strFile   As String
Dim strLink   As String
Dim intSlash As Integer

strFile = "\\mypath\EHS-114-FLOW*.pdf"
strlook = Dir(strFile)
  
If strlook = "" Then
    strfile2 = "\\mypath\EHS-114-FLOW*.pdf"
    strlook = Dir(strfile2)
End If

strFolder = "\\mypath\"

    
    start_doc = ShellExecute(dbwnd, "open", strFolder & strlook, 0, 0, SW_NORMAL)
  
  Call CloseWinTimerSetting
  
'************** USER DEFINED **************
Const conPARTIAL_TITLE As String = "EHS-114-FLOW"
'******************************************
Dim lhWndP As Long

If GetHandleFromPartialCaption(lhWndP, conPARTIAL_TITLE) = True Then

MsgBox "An Excel Window with a Partial Title of " & conPARTIAL_TITLE & " has been found!. " & _
           "The Handle to this Window is [" & lhWndP & "].", vbInformation, "Window Found"
        Call ShowWindow(lhWndP, SW_MINIMIZE)
    Else
    
        MsgBox "An Excel Window with a Partial Title of " & conPARTIAL_TITLE & " has NOT been found!", _
            vbExclamation, "Window NOT Found"


Thank you
This post has been edited by justair07: Jul 11 2018, 08:25 AM
Go to the top of the page
 
ADezii
post Jul 11 2018, 10:06 AM
Post#9



Posts: 2,114
Joined: 4-February 07
From: USA, Florida, Delray Beach


A few minor adjustments should do the trick. For the sake of brevity, I did not include any Validation or Error Checking Code.
CODE
'****************** USER DEFINED ******************
Const conPARTIAL_TITLE As String = "EHS-114-FLOW"
Const conFOLDER As String = "C:\Test\"
Const conFILE_SPEC As String = "EHS-114-FLOW*"
'**************************************************
Dim lhWndP As Long
Dim strFile As String
Dim lngRetVal As Long

If GetHandleFromPartialCaption(lhWndP, conPARTIAL_TITLE) = True Then
  Call ShowWindow(lhWndP, SW_MINIMIZE)
Else
  strFile = Dir$(conFOLDER & strFile, vbNormal)
  
  lngRetVal = ShellExecute(0, "open", conFOLDER & strFile, "", conFOLDER, SW_NORMAL)
End If

This post has been edited by ADezii: Jul 11 2018, 10:08 AM
Go to the top of the page
 
justair07
post Jul 11 2018, 10:54 AM
Post#10



Posts: 759
Joined: 22-August 13



Hello,

Thanks again. Certainty close but I'm getting the same issue of the window not minimizing unless its already open. For example: I run my code to open the pdf, call a timer to wait 10 seconds, then exicute your function to minimize the window.

It works if the pdf is already open but not when it has to be opened. iconfused.gif

I also tried WM_CLOSE, still no luck.

EDIT:

Does WM_CLOSE work the same way?

CODE
Public Const WM_CLOSE As Long = &H10


CODE
Call ShowWindow(lhWndP, WM_CLOSE)


EDIT:

Maybe it helps of I explain my purpose.

I want the code to open the pdf, then I want it to close the pdf after a certain time has elapsed.

I already have my timer sub

-Justin
This post has been edited by justair07: Jul 11 2018, 11:17 AM
Go to the top of the page
 
ADezii
post Jul 11 2018, 11:23 AM
Post#11



Posts: 2,114
Joined: 4-February 07
From: USA, Florida, Delray Beach


Kindly Post the Code that:
  1. Opens the *.pdf.
  2. Any Code in the Timer() Event.
  3. Call to the Function to MINIMIZE the *.pdf Window.

Go to the top of the page
 
justair07
post Jul 11 2018, 11:30 AM
Post#12



Posts: 759
Joined: 22-August 13



QUOTE
Opens the *.pdf.

CODE
Dim strFolder As String
Dim strFile   As String
Dim strLink   As String
Dim intSlash As Integer

strFile = "\\s-aug-file-1\augfiles\Quality Shared\Procedures\EHS (Safety&Environ)\EHS-100 (Safety)\EHS-114-FLOW\EHS-114-FLOW*.pdf"
strlook = Dir(strFile)
  
If strlook = "" Then
    strfile2 = "\\s-aug-file-1\augfiles\Quality Shared\Procedures\EHS (Safety&Environ)\EHS-100 (Safety)\EHS-114-FLOW\EHS-114-FLOW*.pdf"
    strlook = Dir(strfile2)
End If

strFolder = "\\s-aug-file-1\augfiles\Quality Shared\Procedures\EHS (Safety&Environ)\EHS-100 (Safety)\EHS-114-FLOW\"

    
    start_doc = ShellExecute(dbwnd, "open", strFolder & strlook, 0, 0, SW_NORMAL)


QUOTE
Any Code in the Timer() Event.

CODE
Public Sub Timeout(how_many_seconds As Double)

Starting_Time = Timer

Do
DoEvents
Loop Until (Timer - Starting_Time) >= how_many_seconds

End Sub

CODE
Public Sub CloseWinTimerSetting()

Timeout (20)

End Sub


QUOTE
Call to the Function to MINIMIZE the *.pdf Window.

CODE
Public Declare Function ShowWindow Lib "user32.dll" ( _
     ByVal hwnd As Long, _
     ByVal nCmdShow As Long) As Long

CODE
Public Const SW_MINIMIZE As Long = 6

CODE
'************** USER DEFINED **************
Const conPARTIAL_TITLE As String = "EHS-114-FLOW"
'******************************************
Dim lhWndP As Long

If GetHandleFromPartialCaption(lhWndP, conPARTIAL_TITLE) = True Then
        Call ShowWindow(lhWndP, SW_MINIMIZE)
    Else
End If

This post has been edited by justair07: Jul 11 2018, 11:34 AM
Go to the top of the page
 
justair07
post Jul 11 2018, 12:51 PM
Post#13



Posts: 759
Joined: 22-August 13



Got it. Couldn't figure out what the dealwas with hte SW_MINIMIZE but I got the WM_CLOSE to work great:

CODE
Dim strFolder As String
Dim strFile   As String
Dim strLink   As String
Dim intSlash As Integer

strFile = "\\mypath\EHS-114-FLOW*.pdf"
strlook = Dir(strFile)
  
If strlook = "" Then
    strfile2 = "\\mypath\EHS-114-FLOW*.pdf"
    strlook = Dir(strfile2)
End If

strFolder = "\\mypath\"
  
    start_doc = ShellExecute(dbwnd, "open", strFolder & strlook, 0, 0, SW_NORMAL)
  
  Call CloseWinTimerSetting
  
'****************** USER DEFINED ******************
Const conPARTIAL_TITLE As String = "EHS-114-FLOW"
'**************************************************
Dim lhWndP As Long

If GetHandleFromPartialCaption(lhWndP, conPARTIAL_TITLE) = True Then
  Call PostMessage(lhWndP, WM_CLOSE, 0&, 0&)
Else
End If


Thank you so much for the help!
This post has been edited by justair07: Jul 11 2018, 12:51 PM
Go to the top of the page
 
ADezii
post Jul 11 2018, 01:02 PM
Post#14



Posts: 2,114
Joined: 4-February 07
From: USA, Florida, Delray Beach


I don't see how you are calling the Code Block to Minimize the *.pdf Window. I am in work now, and unfortunately we do not have Access installed, so I simulating a Delay (pseudo Timer) of 20 secs. If the *.pdf is Open, the Code will Minimize it, if not the *.pdf will be Opened, there will be a 20 second Delay, then the *.pdf Window will be MINIMIZED. Hope this helps.
CODE
'****************** USER DEFINED ******************
Const conPARTIAL_TITLE As String = "EHS-114-FLOW"
Const conFOLDER As String = "C:\Test\"
Const conFILE_SPEC As String = "EHS-114-FLOW*"
'**************************************************
Dim lhWndP As Long
Dim strFile As String
Dim lngRetVal As Long
Dim lngCtr As Long

If GetHandleFromPartialCaption(lhWndP, conPARTIAL_TITLE) = True Then
  Call ShowWindow(lhWndP, SW_MINIMIZE)
Else
  strFile = Dir$(conFOLDER & conFILE_SPEC, vbNormal)
  
  'Open the File
  lngRetVal = ShellExecute(0, "open", conFOLDER & strFile, "", conFOLDER, SW_MINIMIZE)
  
  'Waste 20 seconds (approx.)
  For lngCtr = 1 To 1250000
    Debug.Print lngCtr / 2
  Next
  
  'Minimize the Window, need it's Handle first
  If GetHandleFromPartialCaption(lhWndP, conPARTIAL_TITLE) = True Then
    Call ShowWindow(lhWndP, SW_MINIMIZE)
  End If
End If

This post has been edited by ADezii: Jul 11 2018, 01:03 PM
Go to the top of the page
 
justair07
post Jul 11 2018, 02:46 PM
Post#15



Posts: 759
Joined: 22-August 13



I really appreciate the help. Still not minimizing unless it's already open. Basically everything works except SW_Minimize after the file opens and the timer does runs its course. It's very strange because
CODE
Call PostMessage(lhWndP, WM_CLOSE, 0&, 0&)
works great when
I replace
CODE
Call ShowWindow(lhWndP, SW_MINIMIZE)
with it.

Here is my deceleration and constant:

CODE
Public Declare Function ShowWindow Lib "user32.dll" ( _
     ByVal hwnd As Long, _
     ByVal nCmdShow As Long) As Long

CODE
Public Const SW_MINIMIZE As Long = 6


Than I call from the sub using:
CODE
Call ShowWindow(lhWndP, SW_MINIMIZE)


Your help is much appreciated. At least I have a workaround. I prefer the pdf to close anyways, but it's still a head scratcher iconfused.gif

-Justin
Go to the top of the page
 
ADezii
post Jul 12 2018, 07:14 AM
Post#16



Posts: 2,114
Joined: 4-February 07
From: USA, Florida, Delray Beach


Just out of curiosity, create a Test Folder on Drive C: (C:\Test), place a EHS-114-FLOW* File in it, and Run the following Code. It works like a charm on my end.
CODE
'****************** USER DEFINED ******************
Const conPARTIAL_TITLE As String = "EHS-114-FLOW"
Const conFOLDER As String = "C:\Test\"
Const conFILE_SPEC As String = "EHS-114-FLOW*"
'**************************************************
Dim lhWndP As Long
Dim strFile As String
Dim lngRetVal As Long
Dim lngCtr As Long

If GetHandleFromPartialCaption(lhWndP, conPARTIAL_TITLE) = True Then
  Call ShowWindow(lhWndP, SW_MINIMIZE)
Else
  strFile = Dir$(conFOLDER & conFILE_SPEC, vbNormal)
  
  'Open the File
  lngRetVal = ShellExecute(0, "open", conFOLDER & strFile, "", conFOLDER, SW_MINIMIZE)
  
  'Waste 20 seconds (approx.)
  For lngCtr = 1 To 1250000
    Debug.Print lngCtr / 2
  Next
  
  'Minimize the Window, need it's Handle first
  If GetHandleFromPartialCaption(lhWndP, conPARTIAL_TITLE) = True Then
    Call ShowWindow(lhWndP, SW_MINIMIZE)
  End If
End If
Go to the top of the page
 
justair07
post Jul 12 2018, 12:17 PM
Post#17



Posts: 759
Joined: 22-August 13



My company doesn't let me save stuff to the C: drive thumbdn.gif

I will give it shot when I'm not at work.

Go to the top of the page
 
ADezii
post Jul 12 2018, 12:19 PM
Post#18



Posts: 2,114
Joined: 4-February 07
From: USA, Florida, Delray Beach


The actual Drive and Folder are not relevant.
Go to the top of the page
 
justair07
post Jul 17 2018, 06:57 AM
Post#19



Posts: 759
Joined: 22-August 13



Weird. Still no luck.
Go to the top of the page
 
JonSmith
post Jul 17 2018, 07:03 AM
Post#20



Posts: 3,908
Joined: 19-October 10



QUOTE
My company doesn't let me save stuff to the C: drive


Surely C:\Users\Public works. If the entire C drive was locked down the computer wouldn't work.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th October 2018 - 03:51 PM