Easy Excel and Access Question

[adamr99]

Hey,

basically what I need to do is copy over some data (not much) from an excell sheet into a access table.

does anyone have any idea how to do this??

Here is the code I am using:

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
'some objects to refer to Excel


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field

Dim x As Integer
'Just a counter

Set db = CurrentDb
Set rs = db.OpenRecordset("sampletable&quot
'open our recordset


Set objXL = CreateObject("Excel.Application&quot
Set xlWB = objXL.workbooks.Open("C:\adam\sample.xls&quot
Set xlWS = xlWB.worksheets("52&quot
'set references to our excel worksheet


'Set qd = db.TableDefs("sampletable&quot
'db.QueryDefs ("QrySelectDepartment&quot


'this is the manipulation with the excel table
With xlWS
.range("B2&quot.select
For x = 0 To 2
rs.AddNew
rs(x) = .ActiveCell.Offset(0, x) 'IT DOESN"T LIKE THIS LINE
rs.Update
Next x
'move down a row on spreadsheet

End With


xlWB.Close
objXL.Close

Set xlWS = Nothing
Set xlWB = Nothing
Set objXL = Nothing

rs.Close
Set rs = Nothing
Set db = Nothing
'tidy up time

 

[VicM]

adamr99:

As I see it, the rs(x) is meaningless to Access. First off, the expression rs(x) implies an array. But you've already defined rs as a recordset. If you were to substitute "sampletable" for rs, the logic would appear as, "sampletable"(x).

x what: record? field? index?

Access doesn't know what you want. If you are attempting to put the Excel data into a field in the table, then the code should look something like:

rs.field(either the field name in quotes, or the field's index number in the table).value = .ActiveCell.Offset(0, x)

This will ensure that the sequence of the Excel data is preserved in the Access table, i.e., one value for each record in the same column.

Hope this helps,

Vic

 

[scking]

Your writeup seems to imply this is a one-time or short time requirement. If so then merely link to the Excel sheet and use and update/append query to update the data tables. For a one-time requirement don't spend the time to resolve these issues. Also, you are using automation rather than a simple link to an Excel file.




-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[adamr99]

Yes, you are right, it is a very short term assigment.. But, I figured it out another way.

thanks for your post.

adam