hi,
your data in the excel file is set up all wrong.
you must have a header.
the excutive name must be on the row with the employee
name and other info. all on 1 row. the excutive name must
be on the row for each employee.
the legend will not import(this may be your header info if
i understand correctly.
delete all blank rows.
forget the code. once you have set up the excell file in a
importabel format, use the import wizard in access.

note: in excel you can make the sheet look pleasing and
colorful. but in an access table.....data be data. no
fancy. no color. no pleasing. no artistic. just a square
block of data.
you do fancy with forms and reports.


-----Original Message-----

Ok, I hope what I post doesn't seem "stupid". I'm really
new to
manipulating excel spreadsheets.... what I'm trying to do
is: create an
Access Database from an Excel Spreadsheet. The problem
w/spreadsheet
is, the first row does not contain the headers(there's a
legend and
blank row above the header). Secondly, I'm trying to
purge data from
the spreadsheet (example, it shows the top exec names in
one cell and
all the employees and their information below that.
Then, there's a
blank row and then the next exec and his employees.

Example : B16 (and across) displays Headers (exec, emp
last name,
etc.)
B18 displays Exec name but then D18, F18 displays the
employees information based on the headers that span from
B16 and
across. If you are wondering, there are empty columns
separating each
column

So anyway, I only want one particular exec (departments)
information
(not sure how to massage the data) but I thought, I would
work with the
following code to see if I could atleast dump the name
column in a
table. I keep getting a runtime error message. I set
the reference
and everything.... If anyone could help, I'll appreciate
it!


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:\xxx\xxx.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Test1", cn, adOpenKeyset, adLockOptimistic,
adCmdTable
' all records in a table
r = 18 ' the start row in the worksheet
Do While Len(Range("H" & r).Formula) 0
' repeat until first empty cell in column H
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("NAME") = Range("H" & 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

trjdba


--
trjdba
----------------------------------------------------------
--------------
trjdba's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=14989
View this thread:
http://www.excelforum.com/showthread...hreadid=266191

.