我正在使用VBA Word宏来创建个性化议程。这是通过以下步骤完成的:
- 从VBA Word打开Excel电子表格
- 将预定义的范围从电子表格复制到Word中的数组中
- 创建新的Word文档,在单个页面上插入数组中的每一行
感谢我在这里和一些网站上找到的帮助,我编写了一段执行这三个步骤的代码。
在运行代码甚至关闭Word之后,每次在Excel中打开电子表格时,我都会收到一条消息。消息是:
File.xlsm is blocked for edition by 'User'
;(我的用户(
我认为这可能与有关
-
从VBA Word调用Excel:我通过创建
ApplicationObject
(app_Excel(并使用CreateObject
方法来调用Excel
;为了完成应用程序,我使用了Set app_Excel = Nothing
,然后是app_Excel.Quit
,如代码所示;或 -
从VBA Word打开电子表格:在第一次尝试中,我使用了
Excel.Workbooks.Open(,,,ReadOnly = False)
,然后我将其更改为True
,结果相同。
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: _portolonDias.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
将电子表格的数据复制到Word后,如何解锁电子表格?
提前感谢,Tiago
根据评论中给出的建议,我将Set wbk = Nothing
更改为wbk.Close
;代码现在工作正常。
谢谢。
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: _portolonDias.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