Update Excel Sheet Using SQL


I'm attempting to write a procedure that would use an ADODB connection to update records (rows) in a closed workbook.

What I can't seem to figure out is how to reference multiple tables (worksheets) and multiple fields (columns) in Excel...

My SQL statement looks like this:

SQLstr = "UPDATE [Location$] " & vbNewLine & _
        "SET [Location$].[City]=[Current Location$].[City] " & vbNewLine & _
        "WHERE [Location$].[Name]=[Current Location$].[Name]"

cn.Execute SQLstr

This statement's goal is to update the City field on the Location sheet using the City field on the Current Location sheet (in the same workbook).

The problem is that when I try to execute the above statement, I get a run-time error:

-2147217904: No value given for one or more required parameters.

It seems like this should be simple, but I'm missing something, and I haven't been able to find any good documentation on how to handle multiple sheets/fields/workbooks in SQL.

Also, I'm not very skilled with SQL already, so my SQL syntax could be wrong too...but I'm not sure.

Any help would be most welcome.

Just in case we missed something on the connection string or other peripheral issues, here is as good a source of information on most aspects of the topic as you could hope to find: http://www.xtremevbtalk.com/showthread.php?t=217783

But I think you need a JOIN for this job - It's been a while since I worked with EXCEL/ADODB, but you could give this a go:

SQLstr = "UPDATE [Location$] tLoc " & _
         "INNER JOIN [Current Location$] tCur ON tCur.Name = tLoc.Name " & _
         "SET tLoc.City = tCur.City"

EDIT: connection across 2 files

SQLstr = "UPDATE [Location$] IN 'D\Data\target.xls' tLoc " & _
         "INNER JOIN [Current Location$] tCur ON tCur.Name = tLoc.Name " & _
         "SET tLoc.City = tCur.City"

untested - might have to swap the table alias around