vba 在 Excel FROM word 宏中获取 ActiveCell 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17803063/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting ActiveCell value in excel FROM word macro
提问by Arno Van Waeyenberg
I am trying to manipulate an excel workbook from word. Everything was working quite well until all of a sudden I couldn't get a response from ActiveCell.Value
我正在尝试从 Word 操作 Excel 工作簿。一切都很好，直到突然我无法从 ActiveCell.Value 得到响应
The problem is happening all the way at the bottom of this block of code where I try to show the value in a msgbox but no msgbox comes up.
问题一直发生在此代码块的底部，我尝试在 msgbox 中显示该值，但没有出现 msgbox。
The previous commands such as:
之前的命令如：
worksheetJobs.Activate
and
和
.Cells(1500, fichierColumn).Select
are working fine.
工作正常。
The weird thing is that I was working on this (there is a whole block of code that comes AFTER what I have posted) and then all of a sudden I couldn't get even this very basic thing to respond...
奇怪的是，我正在处理这个问题（在我发布的内容之后有一整段代码），然后突然间我什至无法回应这个非常基本的事情......
Dim excelObj As Excel.Application
Dim oWB As Excel.Workbook
'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set excelObj = GetObject(, "Excel.Application")
If Err Then
MsgBox "excel is not running"
Set excelObj = New Excel.Application
excelObj.Visible = True
On Error GoTo Err_Handler
Set oWB = excelObj.Workbooks.Open(Filename:=todoWorkbook)
Else
MsgBox "excel is running"
Dim wb As Workbook
For Each wb In excelObj.Workbooks
If wb.Name = toDoName Then
Set oWB = wb
Exit For
End If
Next wb
End If
MsgBox oWB.Name
Dim fichierColumn As Integer
Dim outMots As Integer
Dim outLignes As Integer
Dim langOut As Integer
fichierColumn = 5
outMots = 17
outLignes = 18
langOut = 9
Dim worksheetJobs As Excel.Worksheet
Set worksheetJobs = oWB.Worksheets("Liste jobs")
worksheetJobs.Activate
With worksheetJobs
.Cells(1500, fichierColumn).Select
Dim tempStr As String
tempStr = ActiveCell.Value
MsgBox tempStr
End With
'Set excelObj = Nothing
'Set oWB = Nothing
Exit Sub
Err_Handler:
MsgBox todoWorkbook & " caused a problem. " & Err.Description, vbCritical, _
"Error: " & Err.Number
End Sub
回答by CuberChase
You aren't getting a value in the message box for a couple of reasons. You're almost there with your cell referencing but ActiveCellis a property of the Excel.Application. VBA thinks ActiveCellcomes from the Word object model, not Excel's because you don't explicitly specify it's from Excel.
由于几个原因，您没有在消息框中获得值。您的单元格引用几乎就在那里，但它ActiveCell是Excel.Application. VBA 认为ActiveCell来自 Word 对象模型，而不是 Excel 的，因为您没有明确指定它来自 Excel。
Since one of your first lines is On Error Resume Nextall your errors are being surpressed and you aren't hitting the error of the unqualified reference to ActiveCell. Consequently word is passing the blank string of tempStrto your message box.
由于您的第一行之一是您的On Error Resume Next所有错误都被抑制了，并且您没有遇到对ActiveCell. 因此 word 将空字符串传递tempStr给您的消息框。
The following will work for you
以下内容对您有用
tempStr = excelObj.ActiveCell
It should be noted that you do not need to Selectcells in order to get their values. The following will produce the same result:
应该注意的是，您不需要Select单元格来获取它们的值。以下将产生相同的结果：
tempStr = worksheetJobs.Cells(1500, fichierColumn).Value
