使用 Excel VBA 更改已关闭工作簿中单元格的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27908561/
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
Use Excel VBA to change the value of a cell in a closed workbook?
提问by shampouya
I currently use this function in VBA to get the value of a cell in a closed workbook. I want to use a similar process to SET the value of the cell to whatever I want, without opening the file. Is that possible?
我目前在 VBA 中使用此函数来获取已关闭工作簿中单元格的值。我想使用类似的过程将单元格的值设置为我想要的任何值，而无需打开文件。那可能吗？
Private Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function
回答by L42
Yes you can do this using ADO as Gary commented but only if your Excel Worksheet is arranged in a Database like structure.
Meaning you have valid fields arranged in columns (with or without headers).
For example:
是的，您可以按照 Gary 的评论使用 ADO 执行此操作，但前提是您的 Excel 工作表排列在类似数据库的结构中。
这意味着您有按列排列的有效字段（带或不带标题）。
例如：
Now, you see that ID number 12345have a name John Johnand you want to update it to John Knight.
Using ADO you can try:
现在，您看到 ID 号12345有一个名字John John并且您想将其更新为John Knight。
使用 ADO，您可以尝试：
Edit1:You can actually do this without using Recordset. See below update.
Edit1：您实际上可以在不使用Recordset 的情况下执行此操作。见下文更新。
Sub conscious()
Dim con As ADODB.Connection
Dim sqlstr As String, datasource As String
Set con = New ADODB.Connection
datasource = "C:\Users\UserName\Desktop\TestDataBase.xlsx" 'change to suit
Dim sconnect As String
sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & datasource & ";" & _
"Extended Properties=""Excel 12.0;HDR=YES"";"
With con
.Open sconnect
sqlstr = "UPDATE [Sheet1$] SET [Name] = ""John Knight"" WHERE [ID Number] = 12345"
.Execute sqlstr
.Close
End With
Set con = Nothing
End Sub
Result:
结果：
I'm not entirely sure if this is what you want but HTH.
我不完全确定这是否是你想要的，但 HTH。
Notes:
笔记：
- You need to add reference to Microsoft ActiveX Data Objects X.X Library(early bind).
- But you can also do this using late bind (no reference).
- Connection string used is for Excel 2007 and up.
- Sheet1is the name of the target sheet you want to update the value to.
- 您需要添加对Microsoft ActiveX Data Objects XX Library（早期绑定）的引用。
- 但是您也可以使用后期绑定（无参考）来做到这一点。
- 使用的连接字符串适用于 Excel 2007 及更高版本。
- Sheet1是您要将值更新到的目标工作表的名称。
Edit1:This is how to do it if your file have no header
Edit1：如果您的文件没有标题，这是如何做到的
First change the connection string HDRargument to NO:.
首先将连接字符串HDR参数更改为NO:。
sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & datasource & ";" & _
"Extended Properties=""Excel 12.0;HDR=NO"";"
Then adjust your SQL string to:
然后将您的 SQL 字符串调整为：
sqlstr = "UPDATE [Sheet1$] SET F2 = ""John Knight"" WHERE F1 = 12345"
So that is F1for field 1, F2for field 2etc.
所以这是F1对于字段 1，F2对于字段 2等。
