What causes "Subscript out of range" during Access import?

  • Thread starter Thread starter RMcgee
  • Start date Start date
R

RMcgee

While trying to import an Excel spreadsheet (XLSX) into Acess 2007, I receive
the "Subscript out of range" message. What does it mean? How do I avoid it?
 
I would first look at numbers. Are there any number fields in Excel that are
not set to number datatypes in Access? Are there scientific notation numbers
in Excel, because I think Excel and Access handle those differently. Are
there large field sizes in Excel and field sizes too small in Access. You can
reset those in table design view.
 
I've experienced this on many occasions. No amount of editing or re-formatting of columns would fix it.
I had a spreadsheet linked to the Access database that I would use for post-processing (pivot tables, charts, macros, etc). The raw import of new data would go smoothly until I started refreshing the data from the linked spreadsheet. Then, the Subscript out of Range error would start upon the next import of new raw data (during the same session). Only by closing both Access and Excel and re-starting them (not just the files, but the whole program) would the error stop. I think there is a connection between a data-link and the import process such that Access can't update a table if it is actively linked to some other source such as a spreadsheet.
 
Back
Top