Excel VBA - Close workbook

Question

I am importing a worksheet from another workbook to my current workbook. After I complete importing the worksheet, I want to close that other workbook. The code I am using gives the error Run-time error 9': Subscript out of range.

Sub ImportWorksheet(MyPath As String, wbName As String)

ControlFile = ActiveWorkbook.Name
Workbooks.Open Filename:=MyPath
Sheets(1).Copy After:=Workbooks(ControlFile).Sheets(1)
ActiveSheet.Name = wbName
Workbooks(MyPath).Close SaveChanges:=False
Windows(ControlFile).Activate

End Sub

I also tried using

Windows(MyPath).Activate
ActiveWorkbook.Close SaveChanges:=False

But I get the same error.

I'm guessing MyPath is a file path, which works fine when you're opening a workbook by it's Filename, but when using Workbooks().Close you need to list the workbook's name, not it's path. Try using Workbooks(Dir(MyPath)).Close SaveChanges:=False. — dwirony
– dwirony, Commented Jul 24, 2018 at 20:50

DisplayName · Accepted Answer · 2018-07-24 22:36:23Z

2

Since Open method of Workbooks object returns a Workbook object you can reference the opened workbook:

Sub ImportWorksheet(MyPath As String, wbName As String)
    ControlFile = ActiveWorkbook.Name
    With Workbooks.Open(Filename:=MyPath)
        .Sheets(1).Copy After:=Workbooks(ControlFile).Sheets(1)
        .Sheets(2).Name = wbName
        .Close SaveChanges:=False 
    End With 
End Sub

answered Jul 24, 2018 at 22:36

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott Craner · Accepted Answer · 2018-07-24 20:58:42Z

1

I like to assign variable, it removes any confusion.

Sub ImportWorksheet(MyPath As String, wbName As String)


Dim Owb As Workbook
Dim Nwb As Workbook

Set Owb = ThisWorkbook
Set Nwb = Workbooks.Open(Filename:=MyPath)

Nwb.Sheets(1).Copy after:=Owb.Sheets(1)
Owb.Sheets(2).Name = wbName

Nwb.Close False
Owb.Activate

End Sub

answered Jul 24, 2018 at 20:58

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Comments

trs11 · Accepted Answer · 2021-11-18 18:45:12Z

-1

Workbooks(strTargetFileName).Close SaveChanges:=False

answered Nov 18, 2021 at 18:45

trs11

336 bronze badges

Collectives™ on Stack Overflow

Excel VBA - Close workbook

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related