TransferSpreadsheet error

[Guest]

Hi,

I am trying to import a spreadsheet into an Access table. This is the code I
am using:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblData",
Reports!rptTest![Source Data Location], Yes, "a17:f100"

However, when I try to execute this code I get the following error message:

"This table contains cells that are outside the range of cells defined in
this spreadsheet"

The problem possibly lies in the fact that the data to import starts on row
18, but I have no idea how many records will have to be imported. I just put
100 to try and test it.

Thanks in advance for your help,

Sam

 

[Alex Dybenko]

Hi,
look like that your table tblData has less fields then you try to import,
try to check this

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

If you don't know how many rows there will be, then I would overestimate (by
a long way) rather than underestimate. Then, provided the excess rows in your
spreadsheet are truly empty (Ctrl-End does not take you beyond the end of
your data), Access will ignore the blank rows. If you cannont guarantee that
the excess rows are empty, then run a delete query after the import to delete
rows where all the fields are Null or empty strings (you may have to
experiment with this bit).

Hope this helps,

Jon.