Auto import data from spreadsheet

[Guest]

Alright I am trying to set up a database for work for my coworkers to use.
That means I have to make it as simple as possible for them to understand how
to use it.

What I would like to do is this:
Import data from an excell sheet into an access data base. (Can I use a form
to do this instead of the file -> import external data -> etc ?)
Also is it possible to import all the worksheets of a excel spreadsheet at
once? (Each sheet will have its own database.)
Lastly when I import data from an excel spreadsheet can I have access add a
column onto the end with the date?

I might be making this harder than it needs to be so. Any advice/sudgestions
are appreciated.

 

[John Nurick]

Alright I am trying to set up a database for work for my coworkers to use.
That means I have to make it as simple as possible for them to understand how
to use it.

What I would like to do is this:
Import data from an excell sheet into an access data base. (Can I use a form
to do this instead of the file -> import external data -> etc ?)

Yes. Use the code at http://www.mvps.org/access/api/api0001.htm to
display the File|Open dialog so the users can select the file. Then pass
this name to the DoCmd.TransferSpreadsheet statement to import. If they
also need to select a sheet, there's a bit more work involved.

Also is it possible to import all the worksheets of a excel spreadsheet at
once? (Each sheet will have its own database.)

No and yes. You can't import multiple worksheets simultaneously, but it
is possible to write code that imports all the worksheets in a workbook
one after the other without user intervention.

When you say "each sheet will have its own database", do you mean that
your workbooks each contains the same set of worksheets, and that
there's a table in your database corresponding to each of these?

Or are you intending to import every single worksheet to a new table or
new .mdb file? If you are, you need to think about what you're doing,
because the result won't be a database so much as a mess of data that
will be no easier to work with than if you'd left it in Excel.

Lastly when I import data from an excel spreadsheet can I have access add a
column onto the end with the date?

Yes. Assuming you're importing from standard worksheets into tables you
have already created, add the timestamp column to each table. Then the
simplest thing to do is to import the data without adding a date, and
after each import run an update query to add the date to the new
records, e.g.

CurrentDB.Execute "UPDATE MyTable SET TimeStamp = Now() WHERE TimeStamp
IS NULL;", dbFailOnError

It's also possible to import the data and add the timestamp in one go,
by importing with a query instead of with the usual TransferSpreadsheet
command.

I might be making this harder than it needs to be so. Any advice/sudgestions
are appreciated.

Think hard about your data structure. That's almost always good advice.

 

[Guest]

Thanks for the reply John. I took your advice and thought about my data
structure again and decided to revise it. I was going to have a table for
each sheet (This way there would be minimal changes to each.) However I added
a new hidden coloumn to identify the name of each sheet. So I am going to
keep working on this and will probably be back for some more help.


Thanks

Kris

 

[Guest]

I am doing a similar task to KingKong, but I need to be able to import every
worksheet in the workbook. Is there some wildcard selection that can be used
for the range portion of transferSpreadsheet command? So far I've basically
combined the code found at the link mentioned below with the code @
http://www.mvps.org/access/general/gen0008.htm

Thanks in advance,
mike

 

[Guest]

just an update to the last post,

all worksheets are identicle in their layouts.
The data is field data from equipment that outputs to excel files. One
workbook represents all the field data for one year, with each worksheet
representing the data for each site visited.

 

[John Nurick]

No, you have to pass the actual sheet or range names each time. Search
http://groups.google.com for

<[email protected]>

and you'll find a posting from me with instructions on how to find a
function that returns a list of the worksheet names and how to use it.

 

[Guest]

Iam trying to create a link between an Excel spreadsheet and an Access table.
I would like it to update the Access database when information is entered in
the Excel spreadsheet. I have followed the instructions using the Wizard
but when I try to finish the link it says " Can't overwrite table or query
'Assets'. What does this error mean?

My problem is that my boss does not know how to use Access and wants to
update the assets in our company through excel but I want it in Access so
that I can run queries and reports for her. Please help.