Importing Multiple Excel fiels to one Access Database

G

Guest

Hi - I've seen this question before, but not answered.

I have three excel spreadsheets like this:

alpha.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

beta.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

gamma.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

as you can see, all spreadsheets have identical colums. the data within the
spreadsheets differs, but the fieldnames and data types are identical.

my access table is like this:

t_MyTable
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

again, exact same field names and data types.

i can successfully import the first spreadsheet; let's use alpha.xls for
this example. i can then open the table and see, that yes, all my data is
there and correct. then, when i try to import a second spreadsheet, let's use
beta.xls, access errors out saying "An error occurred trying to import file
'beta.xls'. The file was not imported.

now, i've tried saving a copy of alpha.xls under the name omega.xls
(identical EVERYTHING), and still received this error.

how do i import multiple files in to the same access table?

thanks!
/amelia
 
G

Guest

I'd like to add some complication to the issue:

my actual fields are like so:

who, what, 1-NOV, 2-NOV, 3-NOV....30-NOV

i'm realising that the fact that my field names are dates (and in dd-mmm
format in the excel spreadsheet) is important.

carry on :)
 
J

John Nurick

You say the field names are identical, so I'll assume that all the
worksheets refer to the same month.

The first thing I'd try would be to change the field names in the
worksheet from dates to text (a date value in Excel is of course a
number).



I'd like to add some complication to the issue:

my actual fields are like so:

who, what, 1-NOV, 2-NOV, 3-NOV....30-NOV

i'm realising that the fact that my field names are dates (and in dd-mmm
format in the excel spreadsheet) is important.

carry on :)



aaearhart said:
Hi - I've seen this question before, but not answered.

I have three excel spreadsheets like this:

alpha.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

beta.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

gamma.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

as you can see, all spreadsheets have identical colums. the data within the
spreadsheets differs, but the fieldnames and data types are identical.

my access table is like this:

t_MyTable
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

again, exact same field names and data types.

i can successfully import the first spreadsheet; let's use alpha.xls for
this example. i can then open the table and see, that yes, all my data is
there and correct. then, when i try to import a second spreadsheet, let's use
beta.xls, access errors out saying "An error occurred trying to import file
'beta.xls'. The file was not imported.

now, i've tried saving a copy of alpha.xls under the name omega.xls
(identical EVERYTHING), and still received this error.

how do i import multiple files in to the same access table?

thanks!
/amelia
Click to expand...
Click to expand...
 
G

Guest

I could change the Excel dates in to text no problem. but then the field name
is a number, not a date. then when i work in reports to analyse the data,
grouping by week and whatnot, it's impossible since i no longer have dates
for field names, but numbers.

if only ms would produce a timesheet or calendar wizard. it would help out
so much.



John Nurick said:
You say the field names are identical, so I'll assume that all the
worksheets refer to the same month.

The first thing I'd try would be to change the field names in the
worksheet from dates to text (a date value in Excel is of course a
number).



I'd like to add some complication to the issue:

my actual fields are like so:

who, what, 1-NOV, 2-NOV, 3-NOV....30-NOV

i'm realising that the fact that my field names are dates (and in dd-mmm
format in the excel spreadsheet) is important.

carry on :)



aaearhart said:
Hi - I've seen this question before, but not answered.

I have three excel spreadsheets like this:

alpha.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

beta.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

gamma.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

as you can see, all spreadsheets have identical colums. the data within the
spreadsheets differs, but the fieldnames and data types are identical.

my access table is like this:

t_MyTable
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

again, exact same field names and data types.

i can successfully import the first spreadsheet; let's use alpha.xls for
this example. i can then open the table and see, that yes, all my data is
there and correct. then, when i try to import a second spreadsheet, let's use
beta.xls, access errors out saying "An error occurred trying to import file
'beta.xls'. The file was not imported.

now, i've tried saving a copy of alpha.xls under the name omega.xls
(identical EVERYTHING), and still received this error.

how do i import multiple files in to the same access table?

thanks!
/amelia
Click to expand...
Click to expand...
Click to expand...
 
J

John Nurick

I think we must be at cross purposes. I was suggesting that you replace
(e.g.) the date value 38674 (today) formatted as 18-NOV with the text
value "18-NOV".




I could change the Excel dates in to text no problem. but then the field name
is a number, not a date. then when i work in reports to analyse the data,
grouping by week and whatnot, it's impossible since i no longer have dates
for field names, but numbers.

if only ms would produce a timesheet or calendar wizard. it would help out
so much.



John Nurick said:
You say the field names are identical, so I'll assume that all the
worksheets refer to the same month.

The first thing I'd try would be to change the field names in the
worksheet from dates to text (a date value in Excel is of course a
number).



I'd like to add some complication to the issue:

my actual fields are like so:

who, what, 1-NOV, 2-NOV, 3-NOV....30-NOV

i'm realising that the fact that my field names are dates (and in dd-mmm
format in the excel spreadsheet) is important.

carry on :)



:

Hi - I've seen this question before, but not answered.

I have three excel spreadsheets like this:

alpha.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

beta.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

gamma.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

as you can see, all spreadsheets have identical colums. the data within the
spreadsheets differs, but the fieldnames and data types are identical.

my access table is like this:

t_MyTable
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]

again, exact same field names and data types.

i can successfully import the first spreadsheet; let's use alpha.xls for
this example. i can then open the table and see, that yes, all my data is
there and correct. then, when i try to import a second spreadsheet, let's use
beta.xls, access errors out saying "An error occurred trying to import file
'beta.xls'. The file was not imported.

now, i've tried saving a copy of alpha.xls under the name omega.xls
(identical EVERYTHING), and still received this error.

how do i import multiple files in to the same access table?

thanks!
/amelia
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Importing/Table 1
New to Access, need some help with a query 1
Query to order lines a certain way 3
import multiple csv files (>100 at a time) to Access or MS SQL 1
Three tables to one 5
Exporting data to multiple textfiles 1
Import from Excel to Access 2
Macro to Export to a Fixed Width txt file 5

Top