Import from Excel Question

  • Thread starter Thread starter Curious
  • Start date Start date
C

Curious

I really need some assistance as QUICKLY as possible...

I have a "form" in Excel that I want to import into Access. Actually I only
want to import specific cells/rows from this makeshift form into Access. It
would be the exact same cells/rows from each Excel document. Can this be
done? If so, can someone please walk me through it?
 
If "specific cells/rows" means a rectangular block of cells that are
in effect a table, you can use a SQL append query like this:

INSERT INTO ExistingTable
SELECT * FROM
[Excel 8.0;HDR=Yes;database=C:\XXX.xls;].[Sheet1$B5:G12]
;

You can select columns, e.g.
SELECT FirstName, LastName FROM ...
If the Excel sheet doesn't have column headers, use
HDR=No
and use the default fieldnames F1, F2... which you can alias to the
actual names in your table, e.g.
SELECT F3 As FirstName, F4 As LastName ...


If you need to get values from individual cells (e.g. Sheet1!H9) you
can do stuff like this:

Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim TheValue As Variant

Set dbD = CurrentDB()
Set rsR = dbD.OpenRecordset("SELECT F1 FROM " _
& "[Excel 8.0;HDR=No;database=C:\XXX.xls;].[Sheet1$H9:H9];",_
dbOpenSnapshot)
TheValue = rsR.Fields(0).Value
rsR.Close
 
Back
Top