Write Excel data to Access Table 1

[SarahMH]

Please can anyone help.

Data is input onto a worksheet, on closure of the spreadsheet I would like to loop through the data and append it to the bottom of an access db table. Has anyone got any example code?

Many thanks

 

[Chance1234]

quick example , written blind so might need some debugging

dim mydb as database 
dim myrec as recordset 
dim xl_book as excel.application
dim xl_sheet as worksheet
dim intR, IntC 'these will move through the sheet 
dim intB ' number of rows of data to import ' note u might want a function to work this out 

set mydb = currentdb ()
set myrec = mydb.openrecordset("tbl_whatever") 

set xl_book = getobject("path of excel book") 
set xl_sheet = xlbook.worksheets("Whatevername of sheet") 

intR = 2 'first row where the data appears 
intC = 2 ' first column of where data appears 
intB = 22 we have 22 rows for arguments sake

Do until intR = IntB 

with myrec 

.add 
![field1] = xl_sheet.cells(intR,intC) 
![field2] = xl_sheet.cells(intR,intc + 1) 
![field3] = xl_sheet.cells(intR,intC + 2) 
.update

end with 

intr = intr + 1 

loop

dont forget to clean up afterwards!


Filmmaker, gentleman and i have a new site half working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[Chance1234]

sorry that was if you were access, if you are in excel then mydb needs to be the path of the database you are writing too and xlbook is the current work book



Filmmaker, gentleman and i have a new site half working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK