Excel to close trailing window

huangapple
go评论167阅读模式
英文:

Excel to close trailing window

问题

这是我用于打开文件的代码：

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

这是我用于实际复制粘贴的代码：

  1. Sub get_data()
  2.  Dim wb As Workbook
  3.  Dim ws As Worksheet
  4.  Dim wn As Worksheet
  5.  Dim lastrow
  6.  Dim supercopy As Range
  8. Application.ScreenUpdating = False
  10. ' 打开其他工作簿
  11. testOpenWBOneDrive
  12. Set ws = openwb.Worksheets("data")
  13. Set wn = ThisWorkbook.Worksheets("Helper Sheet")
  15. With ws
  16.     lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
  17.     wn.Unprotect Password:=pass
  19.     .Range(.Cells(1, 1), .Cells(lastrow, 5)).Copy
  20.     wn.Activate
  21.     wn.Range("A1").Select
  22.     wn.Paste
  23.     
  24.     wn.Protect Password:=pass
  25. End With
  27. openwb.Save
  29. ' 关闭我们刚刚获取数据的工作簿，并确保不保存它（以防意外更改）
  30. openwb.Activate
  31. openwb.Close
  32. ActiveWindow.Close ' 这应该关闭新窗口，但关闭整个Excel
  34. Application.ScreenUpdating = True
  36. End Sub

我的问题是，当我关闭辅助工作簿后，仍然会有一个残留的窗口保持打开。

我尝试在代码中激活它并关闭，如您所见（已注释），我的主工作簿的窗口关闭（以及工作簿），而不是残留的窗口（data.xlsx工作簿关闭，但不关闭其窗口）。

非常感谢您的帮助。

英文:

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

This is my code to open the file:

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

And my code to run the actual copy paste:

  1. Sub get_data()
  2.  Dim wb As Workbook
  3.  Dim ws As Worksheet
  4.  Dim wn As Worksheet
  5.  Dim lastrow
  6.  Dim supercopy As Range
  8. Application.ScreenUpdating = False
  11. 'Open the other workbook
  12. testOpenWBOneDrive
  13. Set ws = openwb.Worksheets("data")
  14. Set wn = ThisWorkbook.Worksheets("Helper Sheet")
  16. With ws
  17.     
  18.     lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
  19.     wn.Unprotect Password:=pass
  21.     .Range(.Cells(1, 1), .Cells(lastrow, 5)).Copy
  22.     wn.Activate
  23.     wn.Range("A1").Select
  24.     wn.Paste
  25.     
  26.     
  28.     wn.Protect Password:=pass
  30. End With
  32. openwb.Save
  34. 'Close the workbook from which we just got some data and make sure not to save it in case
  35. 'accidental changes were made to it.
  36. openwb.Activate
  37. openwb.Close
  38. ActiveWindow.Close 'This should close the new window but closes Excel altogether
  40. Application.ScreenUpdating = True
  42. 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

答案1

得分: 1

你的代码打开了一个新的Excel实例：

  1. Set objLogExcel = CreateObject("Excel.Application")

在关闭工作簿之后，你也必须使用 Quit 来关闭实例，所以解决方案应该是：

  1. objLogExcel.Quit

问题在于你在 testOpenWBOneDrive 中声明了 objLogExcel，所以你不能从子程序 get_data 中关闭它。

简单的解决方案就是将变量声明为公共变量：

  1. Public objLogExcel As Object

但说实话，我认为这样更好：

  1. Sub get_data()
  2.     Dim wb As Workbook
  3.     Dim ws As Worksheet
  4.     Dim wn As Worksheet
  5.     Dim lastrow As Long
  6.     Dim supercopy As Range
  7.     Dim wbFullName As String
  8.     Dim objLogExcel As Object
  10.     Application.ScreenUpdating = False
  12.     '打开另一个工作簿
  13.     Set objLogExcel = CreateObject("Excel.Application")
  14.     objLogExcel.Visible = True
  15.     wbFullName = "https://mysmartplace-my.sharepoint.com/personal/Misc/data.xlsx"
  16.     Set openwb = objLogExcel.Workbooks.Open(wbFullName)
  18.     Set ws = openwb.Worksheets("data")
  19.     Set wn = ThisWorkbook.Worksheets("Helper Sheet")
  21.     With ws
  22.         lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
  23.         wn.Unprotect Password:=pass
  25.         .Range(.Cells(1, 1), .Cells(lastrow, 5)).Copy
  26.         wn.Activate
  27.         wn.Range("A1").Select
  28.         wn.Paste
  29.         wn.Protect Password:=pass
  30.     End With
  32.     openwb.Save
  34.     '关闭刚刚获取数据的工作簿，并确保不保存它，以防意外更改。
  35.     openwb.Activate
  36.     openwb.Close
  37.     ActiveWindow.Close '这应该关闭新窗口，但会关闭整个Excel
  39.     '关闭Excel对象
  40.     objLogExcel.Quit
  42.     Application.ScreenUpdating = True
  43. End Sub

请注意，这段代码将你的变量 openwb 视为先前在其他地方声明为公共的变量。

我强烈建议阅读了解范围和可见性。

英文:

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:

  1. Sub get_data()
  2. Dim wb As Workbook
  3. Dim ws As Worksheet
  4. Dim wn As Worksheet
  5. Dim lastrow As Long
  6. Dim supercopy As Range
  7. Dim wbFullName As String
  8. Dim objLogExcel As Object
  11. Application.ScreenUpdating = False
  13. 'Open the other workbook
  14. Set objLogExcel = CreateObject("Excel.Application")
  15. objLogExcel.Visible = True
  16. wbFullName = "https://mysmartplace-my.sharepoint.com/personal/Misc/data.xlsx"
  17. Set openwb = objLogExcel.Workbooks.Open(wbFullName)
  19. Set ws = openwb.Worksheets("data")
  20. Set wn = ThisWorkbook.Worksheets("Helper Sheet")
  22. With ws
  23.     lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
  24.     wn.Unprotect Password:=pass
  26.     .Range(.Cells(1, 1), .Cells(lastrow, 5)).Copy
  27.     wn.Activate
  28.     wn.Range("A1").Select
  29.     wn.Paste
  30.    wn.Protect Password:=pass
  31. End With
  33. openwb.Save
  35. 'Close the workbook from which we just got some data and make sure not to save it in case
  36. 'accidental changes were made to it.
  37. openwb.Activate
  38. openwb.Close
  39. ActiveWindow.Close 'This should close the new window but closes Excel altogether
  41. 'close Excel object
  42. objLogExcel.Quit
  44. Application.ScreenUpdating = True
  46. 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

huangapple
  • 本文由 发表于 2023年3月15日 19:13:34
  • 转载请务必保留本文链接：https://go.coder-hub.com/75743937.html
  • excel
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定