Import to Access from a specific sheet in Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Al Doug - 02 Jun 2005 15:41 GMT

Hello all, I have an interesting error. My problemis similar to the one
asked in "TransferSpreadsheet trouble" by slmndr. (Type "TransferSpreadsheet
trouble slmndr" into the search box and press "Go" to see it.) When I type in
the "range" box " (this is a macro) "DataSheet$A2:N2", I get the following
error.

"Field 'F!' doesn't exist in destination table 'PMParts'." It goes on to
say that the destination fields and the source fields must be the same.

But they are! I did this fine before when I didn't have a coversheet in my
Excell file. I just want to specify which sheet to import from. Any ideas?

Thanks.

Signature

Al Doug

"For God so loved the world, that he gave his only begotten Son, that
whosoever believeth in him should not perish, but have everlasting life"
(John 3:16).

Reply to this Message

Chris Reveille - 02 Jun 2005 17:03 GMT

Try importing to a new table. You will probably find the F1 error. Usually
caused by and invalid field name.

Signature

Have a great Day

Chris

> Hello all, I have an interesting error. My problemis similar to the one
> asked in "TransferSpreadsheet trouble" by slmndr. (Type "TransferSpreadsheet
[quoted text clipped - 9 lines]
>
> Thanks.

Reply to this Message

Al Doug - 02 Jun 2005 18:08 GMT

Thank you Chris for your help, but...

I did import it to a new table, and I see that the data is being imported
with the fields "F1"-"F14". I do have fourteen fields per record, but it
worked BEFORE I gave the sheet a name and made it the second sheet in the
workbook. I have "Has Field Names" set to True. Maybe part of the problem
is that I have the first row (which has the field names) locked so the field
names cannot be edited by the person who fills out the form. Actually, the
only cells unlocked are the second row after the header row. As I said, it
did work before I changed the name of the sheet and made a cover sheet to
give instructions.

Any other ideas?

Thanks.

Signature

Al Doug

"For God so loved the world, that he gave his only begotten Son, that
whosoever believeth in him should not perish, but have everlasting life"
(John 3:16).

> Try importing to a new table. You will probably find the F1 error. Usually
> caused by and invalid field name.
[quoted text clipped - 12 lines]
> >
> > Thanks.

Reply to this Message

George Nicholson - 02 Jun 2005 19:19 GMT

Specifying a range can be very tricky. I have specified sheets in the past,
but not a range on the sheet, so the only advice I can give is to make sure
your sheet name ends in an exclamation point.

"DataSheet!$A2:N2"

if there are spaces in the sheet name, try:
"'Data Sheet!'$A2:N2"

I have a suspicion that if the value in the TransferSpreadsheet Range
argument is invalid (or can't be evaluated) it just ignores the argument
without raising an error and will try to read the first sheet, as if you had
left the argument blank. Maybe this explains confusing error messages
regarding matching field names or why it "worked before" (it was simply
being ignored?).

HTH,

Signature

George Nicholson

Remove 'Junk' from return address.

> Thank you Chris for your help, but...
>
[quoted text clipped - 40 lines]
>> >
>> > Thanks.

Reply to this Message

Al Doug - 06 Jun 2005 13:57 GMT

George,

You helped me in a round-about way. I realized that the range I had
specified was only the row of cells I was trying to import. I needed to also
include the header row! Now instead of a header sheet with instructions, I
put the instructions on the same sheet as the data to import. For the
"Range" argument, I put "DataSheet$A1:N2". Note that there is no exclamation
mark.

Thanks a lot!

Signature

Al Doug

"For God so loved the world, that he gave his only begotten Son, that
whosoever believeth in him should not perish, but have everlasting life"
(John 3:16).

> Specifying a range can be very tricky. I have specified sheets in the past,
> but not a range on the sheet, so the only advice I can give is to make sure
[quoted text clipped - 57 lines]
> >> >
> >> > Thanks.

Reply to this Message

George Nicholson - 06 Jun 2005 19:50 GMT

Glad to help. Thanks a lot for the Update.

Signature

George Nicholson

Remove 'Junk' from return address.

> George,
>
[quoted text clipped - 85 lines]
>> >> >
>> >> > Thanks.

Reply to this Message