many excel files into one access table

[digibit]

Hi.. I am new to this, and to some extent Access. I am using Access2003
I have a need where there are hundreds of Excel files made each month
(cnsider this a form they are filling out) and the data off those files (they
are all identical) needs to get itno an Access table for storage/manipulation
etc.
Unfortunatley the obvious is not do-able - that is, these users cannot be
given access to Access and use a Form to input the data... which is what the
proper way would be. these users are scattered and not always 'net' connected,
as such I need a type of simple form (I am assuming Excel to be the path of
least resistence... unless someone has a better way) that has approx 75
fields. I will collect all of these identical Excel files, and then somehow
perform a mass import of their contents into a table.
I apologize if this seems basic to proabbaly all of you.. but...., well, any
help would be appreciated.
Thanks!
-william

 

[Jeff Boyce]

William

One option might be to link to all these Excel files from within Access,
then use queries to 'parse' the data from each into your more permanent,
well-normalized table structure. I'll assume that an Excel spreadsheet with
"approx 75 fields" is not particularly well-normalized ...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[digibit]

Hmmm... well I can't link them if no opne is able to connect to Access, right?
I am talkng about 18 people scattered in office around the country; and they
are not connected to each other on a reliable basis. Therefore, the "form"
idea of letting newbie users just fill in a form in Excel (unless there is a
way to offer an Access form that is "portable"..?), then have all those forms
collected (emailed to one location for example).. and from there we can
scrape the data off the Excel files and populate a table. The "users" never
need (shouldn't) to see the data tables.
As an parallel question, if there were a way for me to get the users to be on
a common connection where they "could" access Access....what is the best way
to allow a user to only input data, but not have access to the tables and
queries. That is, they can enter, edit data, and see reports. Do I have to
issue additional passwords above their network logons or...?
As you can see, I am a little hampered in what they are willing to do and
commit to, so I have to make this super simple, but still allow us at this
level to have stable and trackable data.
Thanks!

William

One option might be to link to all these Excel files from within Access,
then use queries to 'parse' the data from each into your more permanent,
well-normalized table structure. I'll assume that an Excel spreadsheet with
"approx 75 fields" is not particularly well-normalized ...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi.. I am new to this, and to some extent Access. I am using Access2003
I have a need where there are hundreds of Excel files made each month

[quoted text clipped - 19 lines]

Thanks!
-william

 

[Jeff Boyce]

Sorry, I didn't explain that clearly.

All the users send their Excel spreadsheets to one central clearing place.

At that place, someone uses MS Access to "link" to the copies of the
spreadsheets, then runs the importing/parsing queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hmmm... well I can't link them if no opne is able to connect to Access,
right?
I am talkng about 18 people scattered in office around the country; and
they
are not connected to each other on a reliable basis. Therefore, the "form"
idea of letting newbie users just fill in a form in Excel (unless there is
a
way to offer an Access form that is "portable"..?), then have all those
forms
collected (emailed to one location for example).. and from there we can
scrape the data off the Excel files and populate a table. The "users"
never
need (shouldn't) to see the data tables.
As an parallel question, if there were a way for me to get the users to be
on
a common connection where they "could" access Access....what is the best
way
to allow a user to only input data, but not have access to the tables and
queries. That is, they can enter, edit data, and see reports. Do I have to
issue additional passwords above their network logons or...?
As you can see, I am a little hampered in what they are willing to do and
commit to, so I have to make this super simple, but still allow us at this
level to have stable and trackable data.
Thanks!

William

One option might be to link to all these Excel files from within Access,
then use queries to 'parse' the data from each into your more permanent,
well-normalized table structure. I'll assume that an Excel spreadsheet
with
"approx 75 fields" is not particularly well-normalized ...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi.. I am new to this, and to some extent Access. I am using Access2003
I have a need where there are hundreds of Excel files made each month

[quoted text clipped - 19 lines]

Thanks!
-william

 

[digibit]

OK.. that is effectivley the plan I had in mind.... so my (newbie) question
is:
How do I link to say... 125 spreadsheet files.

I assume linking a direct import. Is thelining process a quick action...
because I will be getting a few hundred of these files every few weeks and,
well the process to capture the data off these files, and then reset for the
next load, needs to be less of a nightmare than not...
(Too bad there is no way Access can have some sort of "portable" form that
could be filled in and linked back into a database in bulk when it "gets back
top the databse")

THANKS!

Sorry, I didn't explain that clearly.

All the users send their Excel spreadsheets to one central clearing place.

At that place, someone uses MS Access to "link" to the copies of the
spreadsheets, then runs the importing/parsing queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hmmm... well I can't link them if no opne is able to connect to Access,
right?

[quoted text clipped - 39 lines]

 

[Jeff Boyce]

Check Access HELP re: Linked tables. You don't actually 'import' them, just
look at them in place.

If the names of the 125 spreadsheets are always the same, and they are
always in the same folder/location, you can create the 'links' one time in
Access, then swap in new spreadsheets (same names, same location) and
Access won't even know you moved the old one out...

Regards

Jeff Boyce
Microsoft Office/Access MVP

OK.. that is effectivley the plan I had in mind.... so my (newbie)
question
is:
How do I link to say... 125 spreadsheet files.

I assume linking a direct import. Is thelining process a quick action...
because I will be getting a few hundred of these files every few weeks
and,
well the process to capture the data off these files, and then reset for
the
next load, needs to be less of a nightmare than not...
(Too bad there is no way Access can have some sort of "portable" form that
could be filled in and linked back into a database in bulk when it "gets
back
top the databse")

THANKS!

Sorry, I didn't explain that clearly.

All the users send their Excel spreadsheets to one central clearing place.

At that place, someone uses MS Access to "link" to the copies of the
spreadsheets, then runs the importing/parsing queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hmmm... well I can't link them if no opne is able to connect to Access,
right?

[quoted text clipped - 39 lines]

Thanks!
-william

 

[digibit via AccessMonster.com]

Hmm.. OK getting there...., what if the excel files do not have the same name.
. that is, I will get a file for each record... as such each file will have a
unique (though similar.. as in sequential for example - name)
I may have some control as to requiring a certain naming convention and such.
Can I use/link to tabs of worksheets within a workbook, or would that cause
more trouble than it is worth? Bottom line is there will be unique file names,
and there will be many.
(sorry for what must seem like basic questions...)

Thanks!

Check Access HELP re: Linked tables. You don't actually 'import' them, just
look at them in place.

If the names of the 125 spreadsheets are always the same, and they are
always in the same folder/location, you can create the 'links' one time in
Access, then swap in new spreadsheets (same names, same location) and
Access won't even know you moved the old one out...

Regards

Jeff Boyce
Microsoft Office/Access MVP

OK.. that is effectivley the plan I had in mind.... so my (newbie)
question

[quoted text clipped - 31 lines]

 

[Jeff Boyce]

Access is a big dumb computer program. If you want it to be that smart,
you'll have to develop procedures (subroutines, functions) to tell it how...

Yes, you can use (VBA) code in procedures to modify the "name" of the files
Access looks for, but if you want to use the concept of 'permanently linked
files', you need to keep the same names! If you get different names of the
files each time, you have to tell Access how/where to find the files, then
tell it to link to those. If you don't already understand programming in
VBA, you're about to learn how to!

Or maybe one of the other newsgroup readers has some code s/he can share
(but you will still need to understand enough to know when/how to alter it
to fit your situation).

I'm sure you can use Access/Excel automation to grab specific 'sheets from a
workbook ... and there may be simpler ways...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hmm.. OK getting there...., what if the excel files do not have the same
name.
that is, I will get a file for each record... as such each file will have
a
unique (though similar.. as in sequential for example - name)
I may have some control as to requiring a certain naming convention and
such.
Can I use/link to tabs of worksheets within a workbook, or would that
cause
more trouble than it is worth? Bottom line is there will be unique file
names,
and there will be many.
(sorry for what must seem like basic questions...)

Thanks!

Check Access HELP re: Linked tables. You don't actually 'import' them,
just
look at them in place.

If the names of the 125 spreadsheets are always the same, and they are
always in the same folder/location, you can create the 'links' one time in
Access, then swap in new spreadsheets (same names, same location) and
Access won't even know you moved the old one out...

Regards

Jeff Boyce
Microsoft Office/Access MVP

OK.. that is effectivley the plan I had in mind.... so my (newbie)
question

[quoted text clipped - 31 lines]

Thanks!
-william