Read Excel Values into Access form

[chris-uk-lad]

Hi,

Im going to create a form in access that pulls in the data i have stored in an excel file and piece together. Eventually hope for it to display a list of tasks relating to each user aswell as enter new details.

Firstly im trying to read in my excel document into access 'without' importing the data into a table (i dont wish to go back and forth updating, i want to update the excel doc via setting the recordset). Ive not been able to find how to read in the data allowing me to have each users record on a seperate page of the form without all fields being imported. can anyone guide me?

Thanks

 

[llkhoutx]

Link the Excel sheet to Access and treat it like a table.

 

[Waheed2008]

The best way is to link you excell sheet with your access as mentioned by llkhoutx. In this way you make changes into excel and can see them into access.

 

[chris-uk-lad]

Ill give it a try though im concerned over the excel file having several worksheets, not sure how id handle them.

 

[Waheed2008]

Your last reply has some imbiguity. It is better if you explain your problem little bit.
I have supposed that you want to import one sheet into acces.
As far as I know, you can import 1 sheet at a time into access. You can import any sheet from an excel file. But if you want to access fields from multiple sheets at a time, you realy have to work around on excel side. You can make a separate sheet and link data from other sheets and then feed this sheet into access.

 

[chris-uk-lad]

Thanks for input, im working on a linked table atm and its working great so far .

As a slight side question, im also doing similar for an Access table into a form, but my Access table has tick boxes. How would i translate a form Tick Box into an Access tables tick box?

Thanks

 

[llkhoutx]

When using docmd.transferspreadsheet ione can specify the desired sheet, even rows and columns.

If your data is in sheet "XYZ", row 3 and columns a through AZ. Link XYZ!A3:IV5000, Access will see exactly what's there. Null rows may result from cells which previously held data. Always specifiy a significantly larger space than you actually have and you'll not have to frequently change the program.

This method won't work with irregularily shaped sheets, i.e. sheets which are not rectangular. In that case, you'll have to judiciously pick your range.

Access "Help" is pretty good on TransferSpreadsheet. It's quite powerful.