|
Home > Archive > MS Access Database with External Data > September 2005 > Auto import data from spreadsheet
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Auto import data from spreadsheet
|
|
| kingkong316 2005-06-23, 8:25 pm |
| 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 2005-06-24, 3:24 am |
| On Thu, 23 Jun 2005 12:33:01 -0700, "kingkong316"
< kingkong316@discussi
ons.microsoft.com> wrote:
>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.
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
| |
| kingkong316 2005-06-24, 9:24 am |
| 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
"John Nurick" wrote:
> On Thu, 23 Jun 2005 12:33:01 -0700, "kingkong316"
> < kingkong316@discussi
ons.microsoft.com> wrote:
>
>
> 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.
>
>
> 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.
>
>
> 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.
>
>
> Think hard about your data structure. That's almost always good advice.
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
| |
| drive105 2005-07-04, 8:25 pm |
| 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
"John Nurick" wrote:
> On Thu, 23 Jun 2005 12:33:01 -0700, "kingkong316"
> < kingkong316@discussi
ons.microsoft.com> wrote:
>
>
> 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.
>
>
> 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.
>
>
> 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.
>
>
> Think hard about your data structure. That's almost always good advice.
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
| |
| drive105 2005-07-04, 8:25 pm |
| 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.
"drive105" wrote:
[color=darkred]
> 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
>
> "John Nurick" wrote:
>
| |
| John Nurick 2005-07-05, 3:24 am |
| No, you have to pass the actual sheet or range names each time. Search
http://groups.google.com for
< 4ofsq01pl9tnnejfjj17
ahipmp7u4ohul5@4ax.com>
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.
On Mon, 4 Jul 2005 14:33:01 -0700, "drive105"
< drive105@discussions
.microsoft.com> wrote:
[color=darkred]
>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
>
>"John Nurick" wrote:
>
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
| |
|
| 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.
"John Nurick" wrote:
> No, you have to pass the actual sheet or range names each time. Search
> http://groups.google.com for
>
> < 4ofsq01pl9tnnejfjj17
ahipmp7u4ohul5@4ax.com>
>
> 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.
>
>
> On Mon, 4 Jul 2005 14:33:01 -0700, "drive105"
> < drive105@discussions
.microsoft.com> wrote:
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
|
|
|
|
|