trying to close a workbook after it gets opened, but the loop failes to do it

Through a sub i download an excel file from a website. when the download button is clicked, it both opens the excel file and also let the file get downloaded. i dont need it to get opened so as soon as it gets opened and before srart of next subs, i need to close it. By some codes i tried to do it but I failed.

The problem is that the Do Loop i wrote, can not catch the file and hangs. It just works well when i debug it through F8. Then i thought maybe by application.wait method I can let the sub to wait till the workbook appears, like what happens in debug mode but it didnt help as well.

Also i need to add that since everytime the file gets downloaded its name changes by the webiste then i used Like operator.

Sub Test()    
    Dim wb As Workbook
    Dim wbName As String
    Dim Cnt As Integer
    
    wbName = "transactions_history_"
    
    'Application.Wait Now + TimeValue("00:00:10") ' it didnt help so i commented it
    Do
        Application.Wait Now + TimeValue("00:00:01")
        For Each wb In Application.Workbooks
            If wb.Name Like wbName & "*" Then
                Cnt = 1
                Exit Do
            End If
        Next wb
    Loop Until Cnt = 1 
    wb.Close
End Sub

Anybody has any idea? thanks.

FaneDuru, I copy here the code which downloads the workbook from the website. it worked very well until the website changed something and when the file gets downloaded it gets opened too. I need to close it to let the rest of the programms work without problem but i coudlnt manage it so far.

Sub TBC()

    ' declerations
    Dim myBrowser                             As Selenium.ChromeDriver
    Dim FindBy                                As New Selenium.by
    Dim objFSO                                As Object
    Dim objFolder                             As Object
    Dim objFile                               As Object
    Dim A, I                                  As Integer
    Dim FileName, BankFolderAddress           As String
    Dim N                                     As Byte
  
    ' initializations
    BankFolderAddress = "D:\Projects\Excel\Main Program\Bank Statements\"
    Set FindBy = New Selenium.by
    Set myBrowser = New WebDriver
    I = 0
    A = 0
        
    Sheet2.Cells.ClearContents
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(BankFolderAddress)
    For Each objFile In objFolder.Files 
        Sheet2.Cells(I + 1, 1) = objFile.Name
        Sheet2.Cells(I + 1, 2) = objFile.Path
        I = I + 1
    Next objFile
    If Sheet2.Cells(1, 1) <> "" Then
        For N = 1 To I
            Kill Sheet2.Cells(N, 2).value
        Next N
    End If
   
Start:
   
    myBrowser.SetProfile Environ("LOCALAPPDATA") & "\GOOGLE\CHROME\USER DATA"
    myBrowser.AddArgument "profile-directory=Default"
    myBrowser.Start "chrome"
    
    Application.DisplayAlerts = False
   
    
   'Address e website
    myBrowser.Get "https://tbconline.ge/tbcrd/login?t=false"
    myBrowser.Window.Maximize
    
    A = 0
    Do
        Application.Wait Now + TimeValue("00:00:01")
        A = A + 1
        If A = 10 Then GoTo Finish
    Loop Until myBrowser.IsElementPresent(FindBy.XPath("//button"))
      
    If myBrowser.IsElementPresent(FindBy.Css("input[formcontrolname='username']")) Then
        myBrowser.FindElementByXPath("//button").Click
    Else
        GoTo JMP
    End If

JMP:
   
   'For removing PopUps
    If myBrowser.IsElementPresent(FindBy.XPath("//div[@id='mainLoadingLayer']/ui-view/ui-view/div/div[2]/div/div/div/div/div[3]/button")) Then
        myBrowser.FindElementByXPath("//div[@id='mainLoadingLayer']/ui-view/ui-view/div/div[2]/div/div/div/div/div[3]/button").Click
    End If
   
   ' Choosing Transaction Menu
    A = 0
    Do
        Application.Wait Now + TimeValue("00:00:01")
        A = A + 1
        If A = 10 Then GoTo Finish
    Loop Until myBrowser.IsElementPresent(FindBy.XPath("//a[contains(text(),'Transactions')]"))

    If myBrowser.IsElementPresent(FindBy.XPath("//a[contains(text(),'Transactions')]")) Then
        myBrowser.FindElementByXPath("//a[contains(text(),'Transactions')]").Click
    End If

   'choosing Transaction submenu
    A = 0
    Do
        Application.Wait Now + TimeValue("00:00:01")
        A = A + 1
        If A = 10 Then GoTo Finish
    Loop Until myBrowser.IsElementPresent(FindBy.XPath("//span[contains(.,'Transactions')]"))

    If myBrowser.IsElementPresent(FindBy.XPath("//span[contains(.,'Transactions')]")) Then
        myBrowser.FindElementByXPath("//span[contains(.,'Transactions')]").Click
    End If
                
   'Clicking on Download icon
    Do
        Application.Wait Now + TimeValue("00:00:01")
    Loop Until myBrowser.IsElementPresent(FindBy.XPath("//ib-controls/div/div[2]/div[2]"))

    If myBrowser.IsElementPresent(FindBy.XPath("//ib-controls/div/div[2]/div[2]")) Then
        myBrowser.FindElementByXPath("//ib-controls/div/div[2]/div[2]").Click
    End If
              
   ' clicking on excel option to download it
    Do
        Application.Wait Now + TimeValue("00:00:01")
    Loop Until myBrowser.IsElementPresent(FindBy.XPath("//a[contains(.,'Excel')]"))

    If myBrowser.IsElementPresent(FindBy.XPath("//a[contains(.,'Excel')]")) Then
        myBrowser.FindElementByXPath("//a[contains(.,'Excel')]").Click
    End If
              
             
   'checking if the file is downloaded
    Do
        Application.Wait Now + TimeValue("00:00:02") 
    Loop Until Dir(BankFolderAddress & "transactions_history_*.xlsx") <> ""
  
   ' get the file name
    FileName = Dir(BankFolderAddress & "transactions_history_*.xlsx", vbDirectory)
    
   ' check if the downloaded file size
    Do
        Application.Wait Now + TimeValue("00:00:05") '03 bood
    Loop Until FileLen(BankFolderAddress & FileName) > 10000
  
Finish:
  
    ' close the Browser
    myBrowser.close

    ' ## I added this code to close the workbook but failed  
    ' call Test()
    
    Dim wb As Workbook
    Dim wbName As String
    Dim Cnt As Integer

wbName = FileName

Do
    Application.Wait Now + TimeValue("00:00:01")
    For Each wb In Application.Workbooks
        If wb.Name=FileName Then
            Cnt = 1
            wb.Close
            Exit Do
        End If
    Next wb
Loop Until Cnt = 1

call BankDataExtraction()    

End Sub

the problem is that if i do not stop the sub before the loop in debug mode, the downloaded workbook neither appears on the screen nor in task manager, and the loop can not catch it and close it, it hangs then. wait methode doesnt help too. I tried you code but it gave me automation error invalid syntax, while executing Set sessEx = GetObject(wbFullName).Application I passed the complete file address to it, If Not sameExSession(FileName).

Solution 1:

If you are sure that the wbName is the name of the workbook searching for, your code must do what you need, but only if the workbook in discussion is open IN THE SAME EXCEL SESSION. I asked about the code opening it, but no clarification received. Please, use the next function to check if the workbook is open in the same session (with the workbook keeping the checking code). If its second parameter is True, it closes the workbook, even in a different session and quit that session. Many codes searches for an existing Excel session and use it, but open a new session if no such a session has been found. Others, uses a new session:

Function sameExSession(wbFullName As String, Optional boolClose As Boolean) As Boolean
   Dim sessEx As Excel.Application, wb As Workbook
  
   Set sessEx = GetObject(wbFullName).Application
   If sessEx.hwnd = Application.hwnd Then
        sameExSession = True
   Else
        sameExSession = False
        If boolClose Then
            sessEx.Workbooks(Right(wbFullName, Len(wbFullName) - InStrRev(wbFullName, "\"))).Close False
            sessEx.Quit: Set sessEx = Nothing
        End If
   End If
End Function

It can be called from your existing code in this way:

Sub Test()    
    Dim wb As Workbook, wbName As String, Cnt As Integer
    
    wbName = "transactions_history_"
    If Not sameExSession Then Exit Sub 'take care to use the workbook FULL NAME!
    Do
        Application.Wait Now + TimeValue("00:00:01")
        For Each wb In Application.Workbooks
            If wb.Name Like wbName & "*" Then
                Cnt = 1
                Exit Do
            End If
        Next wb
    Loop Until Cnt = 1 
    wb.Close
End Sub

If you do not know the workbook extension (since you do not use it in your above code), you can obtain the workbook name using:

    Dim strFullName As String, foldName As String
    foldName = "path to the folder where the workbook is downloaded"
    strFullName = dir(foldName & "\" & vbname & "*.*")
    If strFullName <> "" Then
       If Not sameExSession Then Exit Sub
    Else
        MsgBox "Strange...": Stop 'Just in case. It must be found, if foldName and vbName are correct...
    End If

It may fail if in the same folder there are more then one such a workbook having a name containing the used string. It will return the first of them, in alphabetical order. But, if such a case, you are the one who is needed to bring some clarifications... Of course, if opening in the other session is the correct assumption. In such a case a piece of code can iterate between all those workbooks having the same string in their name, to determine the last saved one.

You can be sure about this aspect (other session), manually searching for the workbook, copying its Full name and create a testing Sub which only calls the function I supplied, using the determined its full name.