asked on

redefining a range in excel that is controlled via access...

I have a range in excel that I upload into Access vai this code

DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:="tblCurvePoint", _
filename:=strCurvePath & "cvUKGas.xls", _
HasFieldNames:=True, _
Range:="CurvePointPrompt"

The size of the range changes and if I upload blank cells the tables in Access reject the upload and I don't want null data in there anyway! Is there anyway that I can redefine the range before upload. A simple way of determining the size of the range is to select the top left cell then hold shift and hit end and right arrow then hit the down arrow....how can I put this kind of action into the VBA code and redefine the range with the same name? thanks

Rey Obrero (Capricorn1)

you will be better off importing the whole sheet and delete the null records from the Access table

Function ImportXL()
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"TestTable", "C:\T.XLS", True, "Sheet5!"
'-------------------------------------------^^^ specify the sheet name followed by a BANG {! } will import the whole sheet

End Function

Delete * from NameOFtable
where recID is Null

Rey Obrero (Capricorn1)

< Is there anyway that I can redefine the range before upload>
you will need codes to
* open the Excel file hidden
* determine the range

do you want to do this ?

ASKER CERTIFIED SOLUTION

stevbe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DaveMatthews76

ASKER

ok looks good will try that later today.....thanks

DaveMatthews76

ASKER

that solution is the nuts, nice one :-)

stevbe

thanks, I figured this out about 9 years ago and kept my write up ... makes it easy to post a clear explaination :-)

<I could have used B2:B65636 but I like to be generic enough that when Microsoft changes the number of rows (again) I will not have to change my function.>
Excel 2007 has LOTS more rows and I still don't need to change it :-)

Steve

DaveMatthews76

ASKER

One more point.....that solution works great! BUT when I type the name in and add it under "define names" it never appears in the range name drop down tool in the top left hand corner of the excel window. When I go back into names/define I can see the range there and if I type the name into that box in the top left it selects the correct range ...BUT when I refer to this range from ACCESS it says it does not exist??? Has this got something to do with the ListFillRangeProperty which I don't what that is??

thanks!