Excel to close trailing window

Hi I use a code to copy paste from a one drive excel sheet to another

This is my code to open the file:

Public openwb As Workbook
Sub testOpenWBOneDrive()
   Dim wbFullName, objLogExcel As Object
   Set objLogExcel = CreateObject("Excel.Application")
   objLogExcel.Visible = True
   wbFullName = "https://mysmartplace-my.sharepoint.com/personal/Misc/data.xlsx"
   Set openwb = objLogExcel.Workbooks.Open(wbFullName)
   
End Sub

And my code to run the actual copy paste:

Sub get_data()
 Dim wb As Workbook
 Dim ws As Worksheet
 Dim wn As Worksheet
 Dim lastrow
 Dim supercopy As Range

Application.ScreenUpdating = False


'Open the other workbook
testOpenWBOneDrive
Set ws = openwb.Worksheets("data")
Set wn = ThisWorkbook.Worksheets("Helper Sheet")

With ws
    
    lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
    wn.Unprotect Password:=pass

    .Range(.Cells(1, 1), .Cells(lastrow, 5)).Copy
    wn.Activate
    wn.Range("A1").Select
    wn.Paste
    
    

    wn.Protect Password:=pass

End With

openwb.Save

'Close the workbook from which we just got some data and make sure not to save it in case
'accidental changes were made to it.
openwb.Activate
openwb.Close
ActiveWindow.Close 'This should close the new window but closes Excel altogether

Application.ScreenUpdating = True

End Sub

My issue is after I close the helper workbook, there is a trailing window that keeps open.

I tried activating it and closing as you can see in my code(as commented) the window of my main workbook closes(and the workbook) instead of the lingering window (data.xlsx workbook does close but not its window)

Thanks for your help in advance

there is a trailing window that keeps open

Your code opens a new instance of Excel here:

Set objLogExcel = CreateObject("Excel.Application")

After closing the workbook, you must also close the instance with Quit so the solution would be:

objLogExcel.Quit

The problem is that you declare objLogExcel inside testOpenWBOneDrive so you cannot close it from your sub get_data.

Easy solution is just declaring the variable as Public:

Public objLogExcel As Object

But to be honest, I think it would be better like this:

Sub get_data()
Dim wb As Workbook
Dim ws As Worksheet
Dim wn As Worksheet
Dim lastrow As Long
Dim supercopy As Range
Dim wbFullName As String
Dim objLogExcel As Object


Application.ScreenUpdating = False

'Open the other workbook
Set objLogExcel = CreateObject("Excel.Application")
objLogExcel.Visible = True
wbFullName = "https://mysmartplace-my.sharepoint.com/personal/Misc/data.xlsx"
Set openwb = objLogExcel.Workbooks.Open(wbFullName)

Set ws = openwb.Worksheets("data")
Set wn = ThisWorkbook.Worksheets("Helper Sheet")

With ws
    lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
    wn.Unprotect Password:=pass

    .Range(.Cells(1, 1), .Cells(lastrow, 5)).Copy
    wn.Activate
    wn.Range("A1").Select
    wn.Paste
   wn.Protect Password:=pass
End With

openwb.Save

'Close the workbook from which we just got some data and make sure not to save it in case
'accidental changes were made to it.
openwb.Activate
openwb.Close
ActiveWindow.Close 'This should close the new window but closes Excel altogether

'close Excel object
objLogExcel.Quit

Application.ScreenUpdating = True

End Sub

Please, note this code is considering your variable openwb as declared previously somewhere else as Public.

I strongly suggest to read Understanding scope and visibility