excel - Is there bulletproof way to copy another workbook using vba?

Question

This is bothering me for a years now, I would like to open another workbook using VBA and copy worksheet to the current workbook and close it. Another workbook can be also macro enabled workbook. My method is following:

Application.DisplayAlerts = False
Dim workBookName1 As String
Application.AutomationSecurity = msoAutomationSecurityForceDisable
Application.AutomationSecurity = msoAutomationSecurityLow
Workbooks.Open fileName:="C:est.xlsm", ReadOnly:=True
workBookName1 = ActiveWorkbook.Name

'some work with copying, filtering, etc.

With Workbooks(workBookName1)
    .Close
End With

BUT the issue is that is not bulletproof -> sometimes this procedure closes all opened workbooks.

is there any other way to deal with this, maybe another method to try it?

question from:https://stackoverflow.com/questions/65916865/is-there-bulletproof-way-to-copy-another-workbook-using-vba

Answer 1

If you declare and use objects properly, you will not face this problem as you will be working with only those objects. Here is an example

Option Explicit

Dim wbThis As Workbook, wbThat As Workbook

Set wbThis = ThisWorkbook
Set wbThat = Workbooks.Open(Filename:="C:est.xlsm", ReadOnly:=True)

'
' Do some copying. For example see how I am using the objects below
' wbThat.Sheets("Sheet1").Copy After:=wbThis.Sheets(wbThis.Sheets.Count)
'

'~~> Close specific workbook when you are done
wbThat.Close (False)