Importing Excel spreadsheets

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a number of Excel workbooks that all contain at least 4 worksheets.
The information in each worksheet is the same layout just different values
of data from different companies. The worksheet looks something like this
.....................Type1................Type2.....................Type3...................Type4
Category1.....(Value)............(Value)....................(Value)..................(Value)
Category2.....(Value)............(Value).....................(Value)..................(Value)
Category3.....(Value).............(Value).....................(Value)..................(Value)

.....................Type5................Type6.....................Type7...................Type8
Category4.....(Value)............(Value)....................(Value)..................(Value)
Category5.....(Value)............(Value).....................(Value)..................(Value)
Category6.....(Value).............(Value).....................(Value)..................(Value)


The (Value)'s correspond to fields in my table. How can I import the
(Value)'s as data into my fields? What do I have to do to the worksheets to
only import the (Value)'s not the headings ie Type and category

Thanks for any help
Tony
 
If you are importing into an existing spreadsheet, you don't need to worry
about the header rows. Since your table already has it's own field names,
only the values will be imported.
 
Thanks Klatuu. I realise that when I'm importing a spreadsheet the heading
rows will not be imported but as you will see from my example some of the
headings are actually partway down the spreadsheet, they aren't all in line
1!
The spreadsheet is designed as a form that external companies complete with
numerical data and is currently printed out and the data input from a hard
copy. I don't know much about Excel and wondered if individual cells of the
spreadsheet could be "attached" to the field names in my table?
Hope I've explained that. This is not how I would have designed this input
but I'm working on a legacy administration system.
Cheers
Tony
 
Sorry, Tony, I did not look far enough down to notice the additional header
rows. If there is some consistency to the values that will come in, it could
be done with a query. That is, import the spreadsheet into an intermediate
table that would include those header rows. The create an append query that
would filter out the header rows and append the data to the production table.
If the new data is replacing the old, you would also have to delete the data
in the production table before doing the append. Or, rather than importing,
you could link to the spreadsheet and run the append query with the linked
spreadsheet as the source and the production table as the destination.

The only other way I know would be to use COM to programmatically read the
data from the spreadsheet and write it to the table. You would still have to
know which rows to omit, so this way is no better and takes a lot more work.
 
Back
Top