How to unlock Excel file after opening it trought VBA Word

Question

I'm working on a VBA Word macro to create a personalized agenda. This is done trought the steps:

1. From VBA Word, open an Excel Spreadsheet
2. Copy a pre-defined range from the spreadsheet, into an array in Word
3. Create new Word document, inserting every row from the array on an individual page.

Thanks to the help I found here and on a few websites, I wrote a piece of code that executes these three steps.

After running the code and even closing Word, I get a message everytime I open the spreadsheet in Excel. The message is:

File.xlsm is blocked for edition by 'User' ; (my user)

I think it might be related to:

1. Calling Excel from VBA Word: I call Excel by creating an ApplicationObject (app_Excel) and using CreateObject method; to finish the application, I used Set app_Excel = Nothing, and then app_Excel.Quit, as seen in the code; Or

2. Opening a spreadsheet from VBA Word: In the first attempts I was using Excel.Workbooks.Open(,,,ReadOnly = False), then I changed it to True, with the same result.

Sub main()

    'Main procedure
    Word.Application.ScreenUpdating = False
    
    Dim app_Excel As Excel.Application
    Set app_Excel = CreateObject("Excel.Application")
        
    Dim wbk_srce As Workbook
    Set wbk_srce = app_Excel.Workbooks.Open("C:\0_portolon\Dias.xlsm", , True)
    
    Dim wsh_srce As Worksheet
    Set wsh_srce = wbk_srce.Worksheets(3)
    wsh_srce.Activate
    
    cell_1 = CStr("A1")
    cell_2 = CStr("D216")
    
    Dim header_range As Excel.Range
    wsh_srce.Range(cell_1, cell_2).Select
    Set header_range = Excel.Selection
    
    Dim header_array() As Variant
    header_array = header_range.Value
    
    Set header_range = Nothing
    Set wsh_srce = Nothing
    Set sbk_srce = Nothing
    app_Excel.Quit
    
    'Creates Word document
    Call create_agenda(header_array)

End Sub

How can I unlock the spreadsheet after copying its data into Word?

Thanks in advance, Tiago

Answer 1

Following the suggestion given in the comments, I changed Set wbk = Nothing to wbk.Close; the code works correctly now.

Thanks.

Sub main()

    'Main procedure
    Word.Application.ScreenUpdating = False
    
    Dim app_Excel As Excel.Application
    Set app_Excel = CreateObject("Excel.Application")
        
    Dim wbk_srce As Workbook
    Set wbk_srce = app_Excel.Workbooks.Open("C:\0_portolon\Dias.xlsm", , True)
    
    Dim wsh_srce As Worksheet
    Set wsh_srce = wbk_srce.Worksheets(3)
    wsh_srce.Activate
    
    cell_1 = CStr("A1")
    cell_2 = CStr("D216")
    
    Dim header_range As Excel.Range
    wsh_srce.Range(cell_1, cell_2).Select
    Set header_range = Excel.Selection
    
    Dim header_array() As Variant
    header_array = header_range.Value
    
    Set header_range = Nothing
    Set wsh_srce = Nothing
    
    wbk_srce.Close 'change
    app_Excel.Quit
    
    'Creates Word document
    Call create_agenda(header_array)

End Sub