Subscript out of range messge while importing from Excel to append

[kccheers]

While trying to import from Excel spreadsheet to append Access table a
message comes up saying subscript out of range. All my Access field names
match the Excel column headings. Does anyone know what I need to do to fix
this?

 

[Ken Snell [MVP]]

Show us the VBA code or macro actions with arguments.

 

[texasgal]

I encountered the same problem. Read what I could on this site but found
nothing. I import and append from Excel quite a bit without problems. But I
could not make this one work. Because I was setting up a new database, I
started over and used the same Excel file to create the table and that worked
- no issues with import. Then I made a copy of the table and tested using it
with 'append' and the very same file and it worked. So, I don't have a clue
why the append didn't work in the first place.

 

[Art Thruston]

I'm having same problem right now, what was the answer? thanks

 

[Art Thruston]

Am having that problem now, trying to append an excel with 1000 records and
get Subscript out of range message. You say I should just make my table and
headings over again and retry? Thanks
Born in Texas too

 

[Ken Snell [MVP]]

Show us the VBA code or macro actions with arguments.
--

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

 

[Art Thruston]

You said the same thing to texasgal on this thread on 6/22, and I did not see
a reply from her, and I do not know what you mean so I cannot make an
intelligent reply. Today I am going to change the text style of two of the
columns in the excel sheet I am trying to import because they have a "custom"
style and the database has a text style in those same headings, hopefully
that will work, I know now that all the column headings have to be the same
text style
If you have any other explanations let me know, thanks

 

[Ken Snell [MVP]]

We need to know how you're doing the import. Are you using File | Get
External Data | Import menu? Or are you using a macro action of
TransferSpreadsheet? Or are you using VBA code of DoCmd.TransferSpreadsheet?
This error can occur for a large variety of reasons, and we need some
information from you to help you debug why you get the error.
--

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

 

[Art Thruston]

Was in Access 2007, on ribbon click External Data tab, click Excel button to
start wizard, search for file, hit append button, hit finish, then msg
appears. Ken, I just got it to work. seems I had two field headings on my
excel worksheet that had "custom" format and they would not import until I
changed the format to :general".. Thanks anyway for your reply and for being
here to help an amatuer like me, I really appreciate your time thanks
Art Thruston