Import data from Excel into an Access form

[m.young999]

I have searched the net for the answer to this and tried different
applications but I've had no luck so far.

At work we have an Access Database that was designed by another
company.
One of the jobs of our office is to transfer data from an Excel
spreadsheet on a network into our database.
At the moment we print off the spreadsheet and enter it manually.

I KNOW there is a quicker way of doing this.
A few things that I have encountered trying to solve this:

The form in the database that we enter the data in is linked to many
tables, not just one.
The column headers do not match the field names in the form.
The database is professionally (apparently) made and thus I cannot
alter the coding or design of it.
It is not possible to alter the Excel spreadsheet either!
We use Access 2000 and Excel 2000 with Windows 98!

The idea solution would be to create some sort of macro/script/code
that assigned each row a different record as well as worked on co-
ordinates as opposed to column names i.e. import cell "E3" into field
"Date", cell "E4" into "Time" etc

I reckon it is possible to do this with a bit of VB coding however I
have done any since I was at school and I'm not sure how I would apply
it either. Other solutions would be preferred if possible.
Help would be appreciated greatly.

 

[pietlinden]

I have searched the net for the answer to this and tried different
applications but I've had no luck so far.

At work we have an Access Database that was designed by another
company.
One of the jobs of our office is to transfer data from an Excel
spreadsheet on a network into our database.
At the moment we print off the spreadsheet and enter it manually.

I KNOW there is a quicker way of doing this.
A few things that I have encountered trying to solve this:

The form in the database that we enter the data in is linked to many
tables, not just one.
The column headers do not match the field names in the form.
The database is professionally (apparently) made and thus I cannot
alter the coding or design of it.
It is not possible to alter the Excel spreadsheet either!
We use Access 2000 and Excel 2000 with Windows 98!

The idea solution would be to create some sort of macro/script/code
that assigned each row a different record as well as worked on co-
ordinates as opposed to column names i.e. import cell "E3" into field
"Date", cell "E4" into "Time" etc

I reckon it is possible to do this with a bit of VB coding however I
have done any since I was at school and I'm not sure how I would apply
it either. Other solutions would be preferred if possible.
Help would be appreciated greatly.

attach the table and then write append queries to put the data in the
correct tables/fields.

 

[Guest]

This might not be a good enough answer, but maybe something to consider: you
can import the spreadsheet into an Access Table then write queries to
incorporate the data into your DB.

 

[John Nurick]

This should get you started. It will run in Excel if you set a
reference to the Microsoft Activex Data Objects Library (probably
version 2.8 or 2.6), or in VB (with references to both ADO and Excel).


Sub DemoXL()

Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oConn As New ADODB.Connection
Dim SQL As String
Dim FirstName As String
Dim LastName As String
Dim Amount As Currency


Set oBook = GetObject("C:\Temp\Access\Table2.xls")

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=C:\Temp\Access\db1.mdb;"

With oBook.Worksheets("Sheet1")
FirstName = .Range("B5").Value
LastName = .Range("C5").Value
Amount = .Range("E4").Value
End With

'Construct SQL statement to append one record
SQL = "INSERT INTO Table1 (Name, Address, Amount) VALUES ( '" _
& FirstName & "','" & LastName & "'," & Amount & ");"
'take a look at the SQL
Debug.Print SQL
'append the record
oConn.Execute SQL

'repeat for other tables and records...
'...

oConn.Close
oBook.Close False