creating database from excel spreadsheet

[trjdba]

Ok, I hope what I post doesn't seem "stupid". I'm really new t
manipulating excel spreadsheets.... what I'm trying to do is: create a
Access Database from an Excel Spreadsheet. The problem w/spreadshee
is, the first row does not contain the headers(there's a legend an
blank row above the header). Secondly, I'm trying to purge data fro
the spreadsheet (example, it shows the top exec names in one cell an
all the employees and their information below that. Then, there's
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 th
employees information based on the headers that span from B16 an
across. If you are wondering, there are empty columns separating eac
column

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


Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Acces
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

trjdb

 

[Guest]

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.