Excel cell to Access database

  • Thread starter Thread starter Saud
  • Start date Start date
S

Saud

Okay here's the situation.....

Situation:
1) My wife's company uses an Excel document. This
document is not organized with column headings but rather
someone originally created it as an esthetic one.
2) For example. Column A row 6 is "Name of Person A",
Column A row 10 has "Name of Person B", Column A row 11
has "Email address of Person B". And then in Column B
identical rows are the answers to the above. Top this
mess with Column C being an empty column which they
reduced the width for Column D row 10 "Phone Number of
Person B"..... etc.
3) They refuse to depart with the document?!?!

Goal:
1) Use the existing Excel document and link specific cells
to tables within an Access database.

Is there a way to specifically choose an individual cell
field and have that cells information exported/linked to
an Access database table?
Or have the Excel document exported to some other file
format and import into Access that way. Keep in mind
there are no Column headings, tab delimited, space
delimited formating I can use to export to .csv etc.

Please help my brain is starting to hurt!!!
Tell me there's an add-in for this sort of thing?
 
Hi Saud,

There's no easy way of doing this.

You can only link to a block of contiguous cells laid out as a table.
The nearest you can get to linking an Access table to individual,
non-contiguous cells in an Excel sheet is to have the table store the
locations of the cells, and then use VBA and Automation to go get the
values from the cells when they're needed.

If the Excel sheet is regularly arranged (i.e. even if the layout is
unusual there is a 100% consistent pattern to it), it is possible to
write VBA code that picks out all the values relating to each person,
assembles them into a SQL append query to add a new reocrd to a table.
Repeat this for every person and you have an Access table corresponding
to the worksheet - but it's not linked to it.

If the Excel sheet isn't absolutely consistent in its layout, the task
becomes more difficult and perhaps impossible.

This is a useful article giving the basics of controlling Excel from
Access:
Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476

There's a white paper and code samples including tutorials available
from Microsoft at
http://support.microsoft.com/?id=253235

Also, search www.mvps.org/access for Excel Automation
 
Back
Top