Use Excel VBA to change the value of a cell in a closed workbook?

Question

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

L42 L42 · Accepted Answer · 2015-01-13T03:32:33

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:

enter image description here

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:

Edit1: 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

Result:

enter image description here

I'm not entirely sure if this is what you want but 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.
Sheet1 is the name of the target sheet you want to update the value to.

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

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"";"

Then adjust your SQL string to:

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

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

Use Excel VBA to change the value of a cell in a closed workbook?

1 Answers