How to import excel forms into access tables ?

[Guest]

The import function in Microsoft Access works best for importing excel files
when data is formatted in columns.

I do however have a excel file where data is formatted like a form, i.e.
data to be imported is found in different rows and columns. This file is to
be filled in by 100 users and then the idea is to import this file into
access database.

How do I do that?

 

[Arvin Meyer [MVP]]

One method, perhaps the easiest, is to use automation. You may want to have
a sheet with references to the particular cells that you need to read the
data from. Then import that sheet. Have a look at the following article on
the Access Web:

http://www.mvps.org/access/general/gen0008.htm

The next article shows how to write data to a particular cell. You need to
read it on a cell by cell basis. The following code should give you a good
idea how to do that:

http://www.mvps.org/access/modules/mdl0006.htm
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Ron2005]

Although it is not necessarily a lot of fun, AND it depends on
what/where the needed information is stored in the spread sheet, you
could import it and then use VBA to go through that import and create
you data file from that.

I had the requirement to import an report from another system that had
Employee ID on one row/column
hours worked on another row/column and there could be multiple of
these.

I was able to do it because there was a unique identifier in one of the
columns on the employee ID row AND a unique identifier on the hours
rows.

1) I imported (not linked) the spreadsheet.
2) Using queries I deleted all of the rows that did not have infomation
that I needed.
3) Using VBA I looped through the resulting table
a. save emplid to hold field whenever the empid identifier was
found.
b. wrote a detail record to real table whenever a time id was
found using the stored empid and time.


Something like that can be done but it depends on how this "form" is
arranged.
And how are 100 people updating it? different Tabs? different sets of
repeading data vertically on same tab?
Good luck in getting 100 people into the same spreadsheet......

Ron
Hope this gave you some ideas.

 

[Guest]

Thanks for the ideas and article references. I will read them now.

As to Ron2005's question, I was thinking that each user can create a copy of
the excel file (acts like a template) and the I run some kind of automation
import function that transfer data to required access tables.

/Angela

 

[Ron2005]

It looks like you will have your work cut out for you.

Getting 100 people into a single spreadsheet would be a hassle on their
side and will take some work getting the repetition of the form so that
you can automate it.

But then again

Getting 100 spreadsheets named in a consistant manner so that you can
automate reading each and everyone of them, and then making sure you
don't read/import any of them twice, etc will also be a hassle.

IF everyone of those 100 had access to Access you could create a simple
mdb with a backend table and defaulted to open the form that they would
fill out and you could make sure they didn't enter twice and you could
keep track of who you have converted/imported. Assuming you have a
common network drive that all can get to.

If you could set up an email address (or use your own) you could maybe
send a formated email out, asking them to reply and fill it out and
then you could have access parse out the responses to get the
information. (Access can read the messagebody text etc from Outlook
email if you are using it.) That might be easier than saving 100
spreadsheets with consistent names for automated reading.

Just some ideas/approaches.

Good Luck,

Ron