Import from Excel - cell by cell ??

[RTC]

We are setting up automatic importing of data from excel sheets to access database. I want to code something in access that at the click of a button will find the correct spreadsheet (excel sheets will be numbered corresponding to the current Access record), will append data from specific cells in the excel sheet to specific columns in an access table.
How do you import from excel to access specifying by cell etc?
thanks

 

[namliam]

Try searching the VBA forum for that one. Been done plenty of times....

Regards

 

[RTC]

I did a search but couldn't see anything specific enough.. ?

 

[namliam]

I did a little quick search, and this thread should get you started. Tho i am sure there are more out there

Regards

 

[RTC]

jeez, my vb is a little em "rusty"... How would I go about adapting that to what I want? Do you write code yourself? Could you put something on here that would do it?

 

[namliam]

its pretty much all there....

How to use variables to create the name of the excel file to open
how to reference the excel file using OLE objects
how to reference cells in the excel file

Only thing missing is how to put things into/draw thing from a recordset from VBA (handling the table)
But there are like tonsssssszzzz and tonz of sample out there on recordsets.... both DAO and ADO guess i could make a sample for you but not now, as i am about to leave....

GL

Regards

 

[RTC]

well if you get a minute that would be great, I've tried using the following which is built in but it doesn't want to work:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, [test], "I:\testfolder\1234567.xls", , "D1921"

 

[RTC]

no idea where that smiley came from - it's a :

 

[namliam]

transferspreadsheet will import the entire sheet, not just selected cells.

 

[namliam]

Try spending a little time working on a sample for me. Create some dummy excel file and an access file showing just about what your trying to do. Zip and post it on the forum....

Regards

 

[RTC]

ok I'll try, but am a bit struggling for time and my VB is pretty lame. That transferspreadsheet says you can specify cell ranges though...

 

[RTC]

ok I give up, I tried bits of the code from the link above but am getting nowhere. All I want is to extract from a specified excel sheet (say C:\testexcel.xls) into an existing table. For instance cell A1 goes to

.column1, cell C8 goes to

.column 3 etc.
Can someone please supply some code for the onclick of a button?

 

[namliam]

specifying stuff like you just did above is different from the link i gave you. Doing an import like that is doable with an import spec and transferspreadsheet.

Once again, post your problem with example. 1 excel sheet (dummy data) 1 DB with table (like you have allready, but no data) and some general idea "i want to import column C from excel to field bla in the table in access etc..."

Regards