I'm trying to merge multiple excel spreadsheets saved under different file
names into one Access worksheet. All the columns are identical. I'm able to
import the first file into Access but then when I try to go thru the same
process with the next spreadsheet and I choose the existing dabase it gives
me an error message that just says it did not go through? What do I do?

Thanks!

Please note -- an Access table may look like a spreadsheet, but it is not
one. Access doesn't have "worksheets".

Is this a one-time "loading" of Access from Excel, or does your situation
call for this process to happen repeatedly (e.g., every month)?

Before you plan on 'stuffing' your Excel data into a table in Access and
walking away, there's a couple of considerations. First, why bother? If
you already have the data in Excel, what will having it in Access do for
you?

Second, Access expects (and works best with) well-normalized data. If you
simply stuff your Excel data into an Access table, odds are very high that
it is NOT well-normalized. Both you and Access have to work overtime to
overcome 'sheet data.

That said, a common approach in similar situations is to load the Excel data
into Access but treat it as 'raw data'. You'll need to revisit the data
you're working with and design a well-normalized table structure first.
Then you can use queries to "parse" the raw data into the well-normalized
table structure.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"lz" <(E-Mail Removed)> wrote in message
news:0B60B5DB-C9DD-4108-8EF4-(E-Mail Removed)...
> I'm trying to merge multiple excel spreadsheets saved under different file
> names into one Access worksheet. All the columns are identical. I'm able
> to
> import the first file into Access but then when I try to go thru the same
> process with the next spreadsheet and I choose the existing dabase it
> gives
> me an error message that just says it did not go through? What do I do?
>
> Thanks!

Thanks for your response back Jeff.

The reason I'm trying to load the data into access is to create one main
database because I'm afraid at one point I'm going to be running out of space
in Excel. Every week I will be adding about 16000 rows of data so I thought
maybe Access would be the best choice in keeping the data and to manipulate?

"Jeff Boyce" wrote:

> Please note -- an Access table may look like a spreadsheet, but it is not
> one. Access doesn't have "worksheets".
>
> Is this a one-time "loading" of Access from Excel, or does your situation
> call for this process to happen repeatedly (e.g., every month)?
>
> Before you plan on 'stuffing' your Excel data into a table in Access and
> walking away, there's a couple of considerations. First, why bother? If
> you already have the data in Excel, what will having it in Access do for
> you?
>
> Second, Access expects (and works best with) well-normalized data. If you
> simply stuff your Excel data into an Access table, odds are very high that
> it is NOT well-normalized. Both you and Access have to work overtime to
> overcome 'sheet data.
>
> That said, a common approach in similar situations is to load the Excel data
> into Access but treat it as 'raw data'. You'll need to revisit the data
> you're working with and design a well-normalized table structure first.
> Then you can use queries to "parse" the raw data into the well-normalized
> table structure.
>
> Good luck!
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
>
> "lz" <(E-Mail Removed)> wrote in message
> news:0B60B5DB-C9DD-4108-8EF4-(E-Mail Removed)...
> > I'm trying to merge multiple excel spreadsheets saved under different file
> > names into one Access worksheet. All the columns are identical. I'm able
> > to
> > import the first file into Access but then when I try to go thru the same
> > process with the next spreadsheet and I choose the existing dabase it
> > gives
> > me an error message that just says it did not go through? What do I do?
> >
> > Thanks!
>
>
>

You know your situation better than we do...

16,000 rows of data per week describes the general situation. Now, how many
characters in each row?

So this is NOT a one-time situation. You're looking for a way to handle a
weekly update...?

Which version of the data is the "right" version? The version that's in
Excel, or the version that's in Access? What happens when the data in Excel
that's already been loaded to Access is changed in Excel?

There are a few more issues to resolve before getting down to the "which
button do I push" stage.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"lz" <(E-Mail Removed)> wrote in message
news:555BBFFC-676D-4456-948E-(E-Mail Removed)...
> Thanks for your response back Jeff.
>
> The reason I'm trying to load the data into access is to create one main
> database because I'm afraid at one point I'm going to be running out of
> space
> in Excel. Every week I will be adding about 16000 rows of data so I
> thought
> maybe Access would be the best choice in keeping the data and to
> manipulate?
>
> "Jeff Boyce" wrote:
>
>> Please note -- an Access table may look like a spreadsheet, but it is not
>> one. Access doesn't have "worksheets".
>>
>> Is this a one-time "loading" of Access from Excel, or does your situation
>> call for this process to happen repeatedly (e.g., every month)?
>>
>> Before you plan on 'stuffing' your Excel data into a table in Access and
>> walking away, there's a couple of considerations. First, why bother? If
>> you already have the data in Excel, what will having it in Access do for
>> you?
>>
>> Second, Access expects (and works best with) well-normalized data. If
>> you
>> simply stuff your Excel data into an Access table, odds are very high
>> that
>> it is NOT well-normalized. Both you and Access have to work overtime to
>> overcome 'sheet data.
>>
>> That said, a common approach in similar situations is to load the Excel
>> data
>> into Access but treat it as 'raw data'. You'll need to revisit the data
>> you're working with and design a well-normalized table structure first.
>> Then you can use queries to "parse" the raw data into the well-normalized
>> table structure.
>>
>> Good luck!
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>>
>> "lz" <(E-Mail Removed)> wrote in message
>> news:0B60B5DB-C9DD-4108-8EF4-(E-Mail Removed)...
>> > I'm trying to merge multiple excel spreadsheets saved under different
>> > file
>> > names into one Access worksheet. All the columns are identical. I'm
>> > able
>> > to
>> > import the first file into Access but then when I try to go thru the
>> > same
>> > process with the next spreadsheet and I choose the existing dabase it
>> > gives
>> > me an error message that just says it did not go through? What do I
>> > do?
>> >
>> > Thanks!
>>
>>
>>

24 columns with each cell containing about 8 characters so about 192
characters per row.

Yes, new data is uploaded weekly but the old data is not erased; so in
other words I just add new data to the already historical data so the
database will continue to grow. My plan is not to save the excel files
because in reality I'm actually downloading the data into excel from another
program so then the only place I would be storing the data would be Access.

I was thinking of creating 2-3 different tables for items that repeat
themselves so to decrease the amount of data.

"Jeff Boyce" wrote:

> You know your situation better than we do...
>
> 16,000 rows of data per week describes the general situation. Now, how many
> characters in each row?
>
> So this is NOT a one-time situation. You're looking for a way to handle a
> weekly update...?
>
> Which version of the data is the "right" version? The version that's in
> Excel, or the version that's in Access? What happens when the data in Excel
> that's already been loaded to Access is changed in Excel?
>
> There are a few more issues to resolve before getting down to the "which
> button do I push" stage.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
>
> "lz" <(E-Mail Removed)> wrote in message
> news:555BBFFC-676D-4456-948E-(E-Mail Removed)...
> > Thanks for your response back Jeff.
> >
> > The reason I'm trying to load the data into access is to create one main
> > database because I'm afraid at one point I'm going to be running out of
> > space
> > in Excel. Every week I will be adding about 16000 rows of data so I
> > thought
> > maybe Access would be the best choice in keeping the data and to
> > manipulate?
> >
> > "Jeff Boyce" wrote:
> >
> >> Please note -- an Access table may look like a spreadsheet, but it is not
> >> one. Access doesn't have "worksheets".
> >>
> >> Is this a one-time "loading" of Access from Excel, or does your situation
> >> call for this process to happen repeatedly (e.g., every month)?
> >>
> >> Before you plan on 'stuffing' your Excel data into a table in Access and
> >> walking away, there's a couple of considerations. First, why bother? If
> >> you already have the data in Excel, what will having it in Access do for
> >> you?
> >>
> >> Second, Access expects (and works best with) well-normalized data. If
> >> you
> >> simply stuff your Excel data into an Access table, odds are very high
> >> that
> >> it is NOT well-normalized. Both you and Access have to work overtime to
> >> overcome 'sheet data.
> >>
> >> That said, a common approach in similar situations is to load the Excel
> >> data
> >> into Access but treat it as 'raw data'. You'll need to revisit the data
> >> you're working with and design a well-normalized table structure first.
> >> Then you can use queries to "parse" the raw data into the well-normalized
> >> table structure.
> >>
> >> Good luck!
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
> >>
> >>
> >> "lz" <(E-Mail Removed)> wrote in message
> >> news:0B60B5DB-C9DD-4108-8EF4-(E-Mail Removed)...
> >> > I'm trying to merge multiple excel spreadsheets saved under different
> >> > file
> >> > names into one Access worksheet. All the columns are identical. I'm
> >> > able
> >> > to
> >> > import the first file into Access but then when I try to go thru the
> >> > same
> >> > process with the next spreadsheet and I choose the existing dabase it
> >> > gives
> >> > me an error message that just says it did not go through? What do I
> >> > do?
> >> >
> >> > Thanks!
> >>
> >>
> >>
>
>
>

See these articles for example code for how to do this in ACCESS:

Import Data from Specific Worksheets in All EXCEL Files in a single Folder
via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXC...ImpFldWrkFiles

Import Data from A Specific Worksheet in All EXCEL Files in a single Folder
into Separate Tables via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXC...ktFilesSepTbls

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"lz" <(E-Mail Removed)> wrote in message
news:0B60B5DB-C9DD-4108-8EF4-(E-Mail Removed)...
> I'm trying to merge multiple excel spreadsheets saved under different file
> names into one Access worksheet. All the columns are identical. I'm able
> to
> import the first file into Access but then when I try to go thru the same
> process with the next spreadsheet and I choose the existing dabase it
> gives
> me an error message that just says it did not go through? What do I do?
>
> Thanks!

"thinking of creating 2-3 different tables" is not something to undertake
lightly. Access is a relational database ... and "expects" well-normalized
data. If you feed it 'sheet data, both you and Access will have to work
overtime to overcome the lack of well-normalized data.

If "normalization" and "relational" are unfamiliar, plan on spending some
time learning your way up this curve. Even if you decide not to use Access,
you might still decide to use a relational database (e.g., SQL-Server) to
store your data, so the lessons apply.

Here's a back-of-the-envelope calculation ...

You have approx. 200 characters X 16,000 / week, or about 3 Mbytes per
week. In a year, you'd have over 150 Mbytes. If your table structure is
well-normalized, and your tables properly indexed, Access and you can handle
this volume for several years before approaching limits. You could consider
doing this in Access to start/learn, then migrate the data (but not the
front-end/application) to something more robust (e.g., SQL-Server) after
performance becomes an issue...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"lz" <(E-Mail Removed)> wrote in message
news:7BACBABD-75A6-4D9F-A4A2-(E-Mail Removed)...
> 24 columns with each cell containing about 8 characters so about 192
> characters per row.
>
> Yes, new data is uploaded weekly but the old data is not erased; so in
> other words I just add new data to the already historical data so the
> database will continue to grow. My plan is not to save the excel files
> because in reality I'm actually downloading the data into excel from
> another
> program so then the only place I would be storing the data would be
> Access.
>
> I was thinking of creating 2-3 different tables for items that repeat
> themselves so to decrease the amount of data.
>
> "Jeff Boyce" wrote:
>
>> You know your situation better than we do...
>>
>> 16,000 rows of data per week describes the general situation. Now, how
>> many
>> characters in each row?
>>
>> So this is NOT a one-time situation. You're looking for a way to handle
>> a
>> weekly update...?
>>
>> Which version of the data is the "right" version? The version that's in
>> Excel, or the version that's in Access? What happens when the data in
>> Excel
>> that's already been loaded to Access is changed in Excel?
>>
>> There are a few more issues to resolve before getting down to the "which
>> button do I push" stage.
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>>
>> "lz" <(E-Mail Removed)> wrote in message
>> news:555BBFFC-676D-4456-948E-(E-Mail Removed)...
>> > Thanks for your response back Jeff.
>> >
>> > The reason I'm trying to load the data into access is to create one
>> > main
>> > database because I'm afraid at one point I'm going to be running out of
>> > space
>> > in Excel. Every week I will be adding about 16000 rows of data so I
>> > thought
>> > maybe Access would be the best choice in keeping the data and to
>> > manipulate?
>> >
>> > "Jeff Boyce" wrote:
>> >
>> >> Please note -- an Access table may look like a spreadsheet, but it is
>> >> not
>> >> one. Access doesn't have "worksheets".
>> >>
>> >> Is this a one-time "loading" of Access from Excel, or does your
>> >> situation
>> >> call for this process to happen repeatedly (e.g., every month)?
>> >>
>> >> Before you plan on 'stuffing' your Excel data into a table in Access
>> >> and
>> >> walking away, there's a couple of considerations. First, why bother?
>> >> If
>> >> you already have the data in Excel, what will having it in Access do
>> >> for
>> >> you?
>> >>
>> >> Second, Access expects (and works best with) well-normalized data. If
>> >> you
>> >> simply stuff your Excel data into an Access table, odds are very high
>> >> that
>> >> it is NOT well-normalized. Both you and Access have to work overtime
>> >> to
>> >> overcome 'sheet data.
>> >>
>> >> That said, a common approach in similar situations is to load the
>> >> Excel
>> >> data
>> >> into Access but treat it as 'raw data'. You'll need to revisit the
>> >> data
>> >> you're working with and design a well-normalized table structure
>> >> first.
>> >> Then you can use queries to "parse" the raw data into the
>> >> well-normalized
>> >> table structure.
>> >>
>> >> Good luck!
>> >>
>> >> Regards
>> >>
>> >> Jeff Boyce
>> >> Microsoft Office/Access MVP
>> >>
>> >>
>> >> "lz" <(E-Mail Removed)> wrote in message
>> >> news:0B60B5DB-C9DD-4108-8EF4-(E-Mail Removed)...
>> >> > I'm trying to merge multiple excel spreadsheets saved under
>> >> > different
>> >> > file
>> >> > names into one Access worksheet. All the columns are identical.
>> >> > I'm
>> >> > able
>> >> > to
>> >> > import the first file into Access but then when I try to go thru the
>> >> > same
>> >> > process with the next spreadsheet and I choose the existing dabase
>> >> > it
>> >> > gives
>> >> > me an error message that just says it did not go through? What do I
>> >> > do?
>> >> >
>> >> > Thanks!
>> >>
>> >>
>> >>
>>
>>
>>