'How to unlock Excel file after opening it trought VBA Word
I'm working on a VBA Word macro to create a personalized agenda. This is done trought the steps:
- From VBA Word, open an Excel Spreadsheet
- Copy a pre-defined range from the spreadsheet, into an array in Word
- 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:
Calling Excel from VBA Word: I call
Excelby creating anApplicationObject(app_Excel) and usingCreateObjectmethod; to finish the application, I usedSet app_Excel = Nothing, and thenapp_Excel.Quit, as seen in the code; OrOpening a spreadsheet from VBA Word: In the first attempts I was using
Excel.Workbooks.Open(,,,ReadOnly = False), then I changed it toTrue, 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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
