使用Excel VBA更改已关闭工作簿中单元格的值? [英] Use Excel VBA to change the value of a cell in a closed workbook?

问题描述

我目前在VBA中使用此功能来获取封闭工作簿中单元格的值.我想使用类似的过程将单元格的值设置为我想要的任何值，而无需打开文件.有可能吗?

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?

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

推荐答案

是的，只有在Excel工作表排列在类似数据库的结构中时，您才能使用ADO，如Gary所评论.
意味着您有有效的字段排列在列中(带或不带标题).
例如:

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:

现在，您看到ID号为 12345 的名称为 John John ，并且要将其更新为 John Knight .
使用ADO可以尝试:

Now, you see that ID number 12345 have a name John John and you want to update it to John Knight.
Using ADO you can try:

实际上，您无需使用 Recordset 即可执行此操作.请参阅下面的更新.

You can actually do this without using Recordset. See below update.

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

结果:

我不确定这是否是您想要的，但不是HTH.

I'm not entirely sure if this is what you want but HTH.

注释:

1. 您需要添加对 Microsoft ActiveX数据对象X.X库(早期绑定)的引用.
2. 但是您也可以使用后期绑定(无参考)来做到这一点.
3. 使用的连接字符串适用于Excel 2007及更高版本.
4. Sheet1 是要将值更新为的目标工作表的名称.

1. You need to add reference to Microsoft ActiveX Data Objects X.X Library (early bind).
2. But you can also do this using late bind (no reference).
3. Connection string used is for Excel 2007 and up.
4. Sheet1 is the name of the target sheet you want to update the value to.

:如果文件没有标题，这就是该方法

This is how to do it if your file have no header

首先将连接字符串 HDR 参数更改为否:.

First change the connection string HDR argument to NO:.

sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
           "Data Source=" & datasource & ";" & _
           "Extended Properties=""Excel 12.0;HDR=NO"";"

然后将您的SQL字符串调整为:

Then adjust your SQL string to:

sqlstr = "UPDATE [Sheet1$] SET F2 = ""John Knight"" WHERE F1 = 12345"

对于字段1 来说是F1，对于字段2 来说是F2等.

So that is F1 for field 1, F2 for field 2 etc.

这篇关于使用Excel VBA更改已关闭工作簿中单元格的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！