Converting Excel '03 Spreadsheet to Access '03 Database

S

slinger

I have a spreadsheet, that is very complicated with some of the formulas and
conditional formatting. Unfortuantely it has also grown very large, and
would likely work better as a database as opposed to a spreadsheet,
especially for reporting functions. There are also some other features I
would like to add, but that can come later. I have tried to import the
spreadsheet using the access wizard, but that has proven fruitless. Does any
one have any recommendations on how I should proceed?
 
A

Arvin Meyer [MVP]

You should be able to import it OK if there are no spanned columns. The
first row, may contain field names, but every additional row must evaluate
to the same datatype.
 
S

slinger

Well, I did try that, and it seemed to work ok, but, over half of the columns
reported importing errors. I think that is from the formulas that are in
those columns.
 
J

John W. Vinson

Well, I did try that, and it seemed to work ok, but, over half of the columns
reported importing errors. I think that is from the formulas that are in
those columns.
Click to expand...

Well, of course Access *is not a spreadsheet*. It's not Excel on steroids;
it's a totally different programming and development environment.

In particular, tables cannot contain formulas. You will probably need to step
back, come up with a properly normalized data model for the information in
your application, and create a set of tables (NOT just one table, if the
spreadsheet is at all complex); then run a series of Append queries to migrate
the "real" (not computed) data into the tables. You would then create Queries
to do the calculations.

Bear in mind that an excellent spreadsheet design can be an abysmally bad
relational table design (and vice versa!!); be prepared to invest a
substantial amount of work in this conversion.

John W. Vinson [MVP]
 
P

Peter Hibbs

I have written an Access 2003 database application which can
automatically convert an Excel spreadsheet into properly normalised
tables in Access. If you would like to email me at
[email protected]_SPAM (omit the NO_SPAM part) I would be
happy to send you a copy or convert the file for you.

HTH

Peter Hibbs.
 
Top