Import from excel - click of a button

[Chimp8471]

i have a database that stores my stock for my business.

each month i have to go through the lengthy process of updating my product lists.

what i want to do is to be able to click a button and then the list is updated for me.

the tanle names and excel names are set up identically, and the excel spreadsheet is always in the same place.

can this be done???........if so how

cheers

Andy

 

[Chimp8471]

tanle names.........(I need sleep)

i meant TABLE names

 

[BukHix]

Ok there are a few things that I need to know to really make this answer useful but this may get you started:

' Turn the Access nags off
    DoCmd.SetWarnings False
    
    ' Import SpreadSheet and name the table ExcelImport
    DoCmd.TransferSpreadsheet acImport, 8, "ExcelImport", _
        "c:\Temp\mySheet.xls", False
    
    ' I use a query to then append the data to an already
    ' existing table. In your case you may want to delete
    ' it or do something else.
    DoCmd.OpenQuery "queAppendTable", acNormal, acEdit
    
    ' Now I delete the table that I imported cause it is no
    ' longer needed.
    DoCmd.DeleteObject acTable, "Jobs"
    
    ' Turn warnings back on
    DoCmd.SetWarnings True
    
    ' Do other stuff ...
End Sub

 

[Chimp8471]

ok thanks will have a look at that this morning,

you said there are a few things that I need to know to really make this answer useful

What ya need to know?

cheers

Andy

 

[BukHix]

Yea it was a little absent minded of me to say that without actualy asking for the info eh?

Basically I was wondering whether there needed to be any clean up pre or post import. I wasn't sure if you needed to maintain the data already in the table and append new data to it or if you wanted to overwrite existing data.

Try the code out and if you find you need to tweak it a little just let me know.

 

[Chimp8471]

right i have got this working so that i can import from excel, into my table,

but

when it goes into the table it imports the headings and also the sum at the bottom of the excel sheet.

i have highlighted in red the bits i don't want to be imported, how do i go about this

any ideas

 

[Dgar007]

looks like the 1st field always has a 1 in it. If so, you could use a delete query to delete all records where field1 <> 1

 

[Chimp8471]

thanks for the reply,

the list is generated at 10pm uk time every day, it is generated from a system called AS400 which my company stores it's info on, it is then exported into excel in that format.

thats where i come in.

hope this explains my need to import.

Andy

 

[Chasedog]

To avoid the headers and sum, select what needs to be imported and name the range.

DoCmd.TransferSpreadsheet [transfertype][, spreadsheettype], tablename, filename[, hasfieldnames][, range]

Just insert the name you gave it for "range."