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:\test.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?

A little confusing: Do you mean you want to open another workbook and copy a workSHEET from it? Copying a workbook into another workbook doesn't make a lot of sense. — braX
Open workbook A then save as workbook B. Then open B as the current workbook. — Solar Mike
Sorry for confusion, I meant copy worksheet from another workbook to current workbook. Thanks! — sandi srkoc

Siddharth Rout Siddharth Rout · Accepted Answer · 2021-01-27T11:00:20

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:\test.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)

Is there bulletproof way to copy another workbook using vba?

1 Answers