Import to Access from a specific sheet in Excel

[Guest]

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.
--
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).

 

[Guest]

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

 

[Guest]

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.
--
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).

 

[George Nicholson]

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,

 

[Guest]

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!
--
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).

 

[George Nicholson]

Glad to help. Thanks a lot for the Update.