Macro creation - Transfer spreadsheet command

[Treeny]

I'm using the 'Transfer Spreadsheet' command within a macro. The command imports data from an Excel spreadsheet. The data I require is not on the first sheet within the spreadsheet. It's in a sheet called 'Pre Term'. I'm trying to import from the 'Pre Term' sheet using the Range argument but cannot get the syttax correct. I'm typing:

Pre term!a1:e65536

in the Range argument but am getting an error message saying that the object Pre term cannot be found.

Where am I going wrong. I'm using Access 97 BTW.

Thanks in advance

 

[Norie]

Is there definitely a space in the sheet name?

If so surround it with single quotes:

'Pre term'!a1:e65536

BTW do you really want to import all those rows?

 

[Treeny]

Thanks, I'll try that.

The amount of data I'm importing varies. It has been as high as 40,000 rows of data. I just use 65536 to ensure I capture everything. Blank rows don't appear to get imported anyway.

 

[Norie]

Are you doing this import on a regular basis?

Why not create a link to the spreadsheet rather than importing it?

You could then use append queries if the data from the spreadsheet needs to be added to a table in Access.