DoCmd.TransferSpreadsheet-Range and Filename problem

Originally Posted by mazatlanmexico

Hi:
When using the tranferSpreadsheet method to import a range from excel into access, is there a way to change the filename and range dinamically. For example: instead of transfering range A1:A24 when I only have data from A1:A10, And instead of hardcoding the filename maybe using the currentProject.path. (Access file and excel file reside in the same folder)
I have used something like this:

dim rangeAddress
RangeAddress=Cells(1,1).currentRegion

DoCmd.TransferSpreadsheet transfertype:=acImport, _
spreadsheettype:=acspreadsheettypeexcel8, _
tablename:="AverageWeightFromExcel", _
filename:="CurrentProject.path\WeightData.xls", _
hasfieldnames:=True, _
Range:="forecast!" & RangeAddress.Address

But crashes when trying to use the range address and filename. Any help is appreciated.

Hi,

the site http://www.mvps.org/access/general/gen0008.htm may help you, but the DoCmd is an Access item, this is an Excel forum and thus Access commands are not covered here.

hth
---