Solved Update Excel From Access (1 Viewer)

[tmyers]

I am creating an Excel file from a query built within the module and need to update a field ("MFR") after the file is created so it changes the manufacturers name to the matching code within our sales system it is being import into. I have a table that I store all my manufacturers in and it stores the code that is shown on the vendors quote, the full name of the vendor I convert the code to when it is imported into the DB and the corresponding code we have in our sales system.

This is the query I run to convert the vendor code to the full name:

UPDATE tblNameMapping INNER JOIN tblTempFixture ON tblNameMapping.OldName = tblTempFixture.Manufacturer SET tblTempFixture.Manufacturer = [tblNameMapping].[NewName];

It is possible to run this same kind of query against an Excel file on a specific field/column? If not, I may have to change my process and involve a temp table to append all the data to and update it there before exporting to an Excel file.

 

[ebs17]

UPDATE
   tblNameMapping AS M
      INNER JOIN [excel 12.0 xml;hdr=yes;imex=1;DATABASE=X:\AnyWhere\YourWorkbook.xlsx].[SheetName$] AS E
      ON M.OldName = E.Manufacturer
SET
   E.Manufacturer = M.NewName

Alternative: You create an equivalent selection query and copy a recordset from it to the same area in the worksheet using CopyFromRecordset.

 

[tmyers]

That did the trick. I tend to have a bad habit at using temp tables so really didnt want to use one this time around.