Use Excel VBA to change cell value in closed book?

Question

I am currently using 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 cell value to any place convenient for me without opening the file. Is it 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 

Answer 1

Yes, you can do this with ADO, as Gary commented, but only if your Excel worksheet is located in the database structure.
This means that you have valid fields located in columns (with or without headers).
For instance:

Now you see that 12345 is named John John and you want to upgrade it to John Knight.
Using ADO, you can try:

Edit1: You can do this without using a Recordset. See update below.

 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 quite sure that this is what you want, but HTH.

Notes:

• You need to add a link to the XX Microsoft ActiveX Data Objects library (early binding).
• But you can also do this with late binding (no link).
• The connection string used is for Excel 2007 and higher.
• Sheet1 is the name of the target sheet that you want to update.

Edit1: Here's how to do it if your file has no header

First change the HDR argument of the connection string to NO :.

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

Then adjust your SQL string:

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

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