Specifying an Excel 'Range' when importing data to access

[Duracell]

Hi,

I have a spreadsheet with multiple worksheets that I import into MS Access.

I'm new to VB but I can get the import to work fine using the 'TransferSpreadsheet' expression i.e. (TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA) (I've left out the TableName etc detail)

However, as the data range in the sheets can vary e.g A1:H24 or A1:F37, I would like this element to be deternined by user input. The only way that I can get the import to work is if the 'range' is hard coded as below:

TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Imports!A1:G34, UseOA)



To make it flexible, I have tried using data=Inputbox"Enter the range in the spreadsheet" and then changing the code to:

TransferSpreadsheet(TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Imports!data, UseOA)


I get prompted for the range and it enters OK but then I get an error message saying the range is not recognised and the data doesn't import.

Any suggestions or guidance as to whether this is possible is much appreciated.