Find+Update+add lines

[Sportman1975]

Dear Wizzards,

I have a question:

I have list(invoice) in Access which I want to update from Excel every day. This list changes every day
Column 1: Name item
Column 2: Number item
Column 3: Invoice

Is it possible to make a macro which import this daily list and:
1) if invoice is in old list and new list ---> column 4: Status=Open
2) if invoice is in new list and not in old list ---> add line in old list
3) if invoice is in old list and not in new list ----> column 4: Status=Closed

If need more information: do not hesitate to ask me.

All help is very appreciated.......

[OBP]

Sportman, welcome to the Forum.
If the updating is being done in Excel, you may be better off asking this question on the Excel forum.
I am not sure why you want the updating done in Excel, are you looking for an Excel Macro?
However have you considered "Linking" to the Access list to avoid importing it?

[Imdabaum]

You can create a linked table in Access that directly pulls the data from Excel. You cannot add data to Excel via Access. But if you do all your data entry in Excel, then when you open Access you should have the current data. If the data doesn't refresh, run the macro to transferSpreadsheet using a saved Import Spec.

Once the spreadsheet is linked into Access you can query it as you would any other table.

So it would then just be a matter of configuring probably 3 Append/Update Queries that meet those conditions.

Test them one by one and when you're comfortable knowing they do what you want. Throw them in a macro. When you have the macro, place a button on a form or in your custom toolbar that calls the macro... or just run the macro manually.

It is possible though.
If you want to send dummy data, I could have something as I have a long weekend to toy around.

Now if you are wanting Access to change the Excel column4 value, that will be tougher.