Transfer Spreadsheet

[antrawson]

Hi,
I am not very good at MS access so would really appreciate some help on this function and how to do it.

Basically I have a spreadsheet which will have columns that match my database.

ItemID Colour Quantity

001 Red 4
002 Blue 22
003 Orange 31


I have a database which has all the products in. I want to be able to run a script that opens up the spreadsheet and pull only the quantity from the item ID

This would be simple however the spreadsheet comes with different products etc in so I cant just do it in excel


eg it comes like this

ItemID Colour Quantity

001 Red 4
004 Yellow 3
006 Black 2
002 Blue 22
003 Orange 31
005 Silver 44

What do I do to get the database to pull the information from a spreadsheet (or a link to one online that updates its a csv)

Idiot steps would be helpful as all the searching I have done have been put at a level of experience I do not have

Many thanks

 

[Ranman256]

attache the excel file as a table

you would run 2 queries
1. append query to add new recs
2. update query to update all recs by their ID (its keyed right?)

put all this in a macro.
run 1 macro.

 

[antrawson]

Hi
Many thanks - It is the "how" that i struggle with :banghead:

 

[antrawson]

Hi
Is anyone able to help please as I keep trying and failing
Thanks

 

[Ranman256]

what is wrong?
did you do the append, and the update?

 

[antrawson]

Hi
Sorry for the delay. No I have not managed to sort it, I do not know how to append

Thanks

 

[arnelgp]

strSQL = "Update tblFruits As A Inner Join [Excel 12.0 Xml;IMEX=2;HDR=YES;ACCDB=YES;Database=[COLOR=Green]E:\yourWorkbook.xlsx[/COLOR]].[[COLOR=Green]Sheet1[/COLOR]$] As B " & _
        "On A.ItemID = B.ItemID Set A.Quantity = Nz(A.Quantity,0) + nz(B.Quantity,0);"

DoCmd.RunSQL strSQL

replace the one's in green color with your actual path and name of workbook and sheet name.

 

[antrawson]

Hi
many thanks for this - I am just trying to suss out how I run the vba code
thanks