CODEWITHSUNDEEP
vbaexcel
Jakob Busk Sørensen
Jakob Busk Sørensen

Reputation: 6101

Workbook.Close() causes "Run-time error 9"

I am somewhat new to VBA, and I am trying to make a Sub() which reads data from a different Excel Workbook. It works by opening the workbook, reading from it, and then I would like to close the workbook again. The last part is what causes the following error:

Run-time error '9':

Subscript out of range

I fail to see why I get this error, when trying to close a file. I hope you guys can assits. It works if I comment out the Close() part...

Code

Option Explicit

Sub Test()
    Dim Path, Filename
    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, transmitWorkbook As Workbook, revieveWorkbook As Workbook

    'Define a variable for the workbook which is to recieve data
    Set revieveWorkbook = ActiveWorkbook

    Path = "C:\Test Folder\"
    Filename = "FileToReadFrom.xlsx"

    'Open workbook which is to transmit, if its not already open
    If Is_WorkBook_Open(Path & Filename) Then
        Set transmitWorkbook = Workbooks(Path & Filename)
    Else
        Set transmitWorkbook = Workbooks.Open(Path & Filename)
    End If

    revieveWorkbook.Sheets(1).Range("A1").Value = transmitWorkbook.Sheets(2).Range("F9").Value
    revieveWorkbook.Sheets(1).Range("B1").Value = Month(transmitWorkbook.Sheets(2).Range("H9").Value)

    Workbooks(Path & Filename).Close SaveChanges:=False


End Sub

Upvotes: 0

Views: 9395

Answers (1)

Jakob Busk Sørensen
Jakob Busk Sørensen

Reputation: 6101

As mentioned in a comment by Jeeped, the path name is not used to close workbooks again. The correct code is 

Workbooks(Filename).Close SaveChanges:=False

Thanks you everyone for their help.

Upvotes: 3

Related Questions