Import Excel spreadsheet to Recordset

[Matt Weyland]

Does anyone know how I would go about importing a range of
data from excel into a recordset in vba.

Thanks.

 

[Frank Stone]

hi,
you will have to name the range in excel. high lite the
range.
on the menu bar insert>name>define.
in access
on the menu bar file>get external data> import (or link)
I usually link to save space. if imported it will go into
a access table. if linked it will so up in the tables as
an excel icon. in the wizard click show named ranges.

 

[Guest]

Thanks for the response but I was looking to be able to do
this through VBA rather than a manual process.

 

[PC Datasheet]

Link to the worksheet to create an Access table. Create a query that only
returns the data in the range you want. Create a recordset based on the query.

 

[Jamie Collins]

...

you will have to name the range in excel.

That advise is incorrect.

An Excel table can be:

1) A worksheet e.g.

SELECT MyCol FROM [Sheet1$];

2) A workbook-level defined Name e.g.

SELECT MyCol FROM [BookLevelName];

3) A worksheet-level defined Name e.g.

SELECT MyCol FROM [Sheet1$SheetLevelName];

4) A range address e.g.

SELECT MyCol FROM [Sheet1$A2:C200];

Jamie.

--