CODEWITHSUNDEEP
vbaexcel
DZZ
DZZ

Reputation: 35

Excel VBA - Close workbook

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.

Upvotes: 3

Views: 11860

Answers (3)

trs11
trs11

Reputation: 23

Workbooks(strTargetFileName).Close SaveChanges:=False

Upvotes: -1

DisplayName
DisplayName

Reputation: 13386

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

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152450

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

Upvotes: 1

Related Questions