Transferring Excel Spreadsheets into Access
 

This site might help you.

http://www.exceltip.com/se/subtotal.html

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub


--
ca1358


"Nancy" wrote:

I work in a real estate brokerage firm. We need to transfer our
current excel based spreadsheet into Access. Our goal is to utilize
Access for superior Database Management while maintaining Excel's
calculator functions. Is this feasible? Also can this be done in an
HTML environment?

Transferring Excel Spreadsheets into Access
 

If you don't have any coding experience and just want to move some dat
from Excel into an Access table...(if this is too elementary, forgiv
me...I don't know what kind of experience you've had)

Save your Excel workbook.
Open Access.
Create a blank database, or if you already have one, open it.
Under the File menu, select Get External Data.
Then select Import.
An Import window opens up.
At the bottom of the window is a drop-down box labeled Files Of Type.
Select Microsoft Excel.
Navigate to the location of your Excel file on your hard drive, selec
it then select the Import button.
A wizard will import your data and ask you various questions about ho
you want to name the table, etc.
If your first row in your spreadsheet contains headings, select that o
the first screen of the wizard. Select Next.
You get the option to create a new table or add the data to an alread
existing table. Click Next.
The next screen lets you specify information about the fields in th
table. Select the fields you want to change the information, like i
you have a field that represents an account number and you don't wan
any duplicates, select that. Select Next.
The next screen allows you to add a primary key. This makes eac
record in the table unique if you let the auto-number function do it
or if you have a field that has a unique number, use it. Click Next.
The next screen lets you name the table. Click Finish.

If there is anything wrong with any of the fields, Access will tell yo
it can't import them and will put the details about the errors i
another table for you to view.

Again...if this is too elementary, I apologize, but this is the easies
and quickest way to do it if you're new to Access. I help administe
several huge SQL databases and I've created local Access databases fo
some of our users so they can manage smaller amounts of data and creat
queries with the Access graphics without having to learn SQL.

If you're going to have huge amounts of data, Access won't be the bes
solution, but if you're managing a relatively small amount of data
it's an inexpensive, user-friendly solution.

Good luck

--
DataCollecto
-----------------------------------------------------------------------
DataCollector's Profile: http://www.hightechtalks.com/m36
View this thread: http://www.hightechtalks.com/t229729

Transferring Excel Spreadsheets into Access
 

thank you