Importing Multiple Excel spreadsheets into Access

[Martin ©¿©¬]

Hi
Using Office 2007

This is my first attempt at using Access
I want to import & link an Excel Workbook with 19 worksheets
5 contain the same fields
14 contain slightly different but similar fields
How would I do that please?

I'm only thinking of linking while learning Access, so that any Excel
updates are mirrored. Does this make sense?

I have been trying this out with one sheet & got #Num! error, which I
think I have fixed by formating the offending excel column to text

However, I am still getting 0's (zeros) in Access from blank cells in
Excel. How do I fix that please?

Any help, advice or tip will be most welcome

 

[Ken Snell [MVP]]

See the various code examples on my "EXCEL import" page:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

 

[Martin ©¿©¬]

Solved my Zeros problem
The Excel sheet was a paste special links, so lots of the cells had
='D5'!A3 and the like
Once I removed them the linked access file follow suite

 

[Martin ©¿©¬]

See the various code examples on my "EXCEL import" page:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

Thank you Ken
Should I import my data into one table or seperate tables?

 

[Martin ©¿©¬]

See the various code examples on my "EXCEL import" page:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

Ken, how do I use the chosen code?

 

[Ken Snell [MVP]]

You said that some worksheets have the same data structure, and the other
spreadsheets have a different data structure. Each data structure means a
separate table, so at least two separate tables would be needed. But, if you
want each spreadsheet's data to be kept separate, then you would import into
separate tables. You can always combine them together by using UNION queries
for the tables with the same structure.

As for how to use the examples that I suggested, the VBA code would go into
a VBA event procedure that you're running in order to do the imports -- such
as the click action of a command button. Is VBA something with which you're
not familiar? How are you wanting to "trigger" the import process?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Ken Snell [MVP]]

ACCESS has forms, which usually are used for displaying/editing data, and
for initiating various actions that run via macros or VBA code. If you just
want to use the data structure of ACCESS, then you can create a macro or VBA
procedure that you then can run manually, where the macro or procedure will
do something that you want.

If you want to import numerous worksheets, a macro will be more complicated
to set up than VBA code. But, it'll be good for you to become more familiar
with the various objects in ACCESS so that you can identify how you may want
to use your database. Then we can better discuss what to do in your database
for automating the import process.

There are many books available for ACCESS. See www.viescas.com for some
suggestions.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/