TransferSpreadsheet and NamedRange; not working!

[ryguy7272]

The Microsoft Access database engine could not find the object
‘TransposedSheet$RyanRange’. Make sure the object exists and that you spell
it and spell the path name correctly.

Here is the code that produces the error:
Function ImportFctn()
On Error GoTo ImportRVP_Err

DoCmd.SetWarnings False

DoCmd.RunSQL ("DELETE * FROM [SharePrices];")
DoCmd.TransferSpreadsheet acImport, 8, "SharePrices", "C:\Documents and
Settings\ThinkPad\Desktop\Historical Stock Prices.xlsm", True,
"TransposedSheet!RyanRange"

DoCmd.SetWarnings True
'DoCmd.CloseForm "frmImport"

ImportRVP_Exit:
Exit Function

ImportRVP_Err:
MsgBox Error$
Resume ImportRVP_Exit

End Function

I’ve never worked with a named ranges (in Excel) before, but I’ve done this
many times with hard-coded ranges.

Can someone please tell me what’s going on here?

Thanks!
Ryan---

 

[Ken Snell]

I assume that TransferSpreadsheet!RyanRange is a combination of the sheet
name and the range name. Range names are unique in an EXCEL file, so you do
not need the sheet name. Use just the range name.

 

[ryguy7272]

Holy cow!! You are totally right!! I just tied it and it works!! Thanks so
much Ken!! I can't believe it; you are totally right. I have to admit, I've
never heard if this before. With a named range, the sheet name is totally
unnecessary. I've been using Excel and Access for over 12 years and I've
never heard of this method before, but it totally works. I learn a new thing
every day!! Thanks so much!!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

I assume that TransferSpreadsheet!RyanRange is a combination of the sheet
name and the range name. Range names are unique in an EXCEL file, so you do
not need the sheet name. Use just the range name.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

The Microsoft Access database engine could not find the object
'TransposedSheet$RyanRange'. Make sure the object exists and that you
spell
it and spell the path name correctly.

Here is the code that produces the error:
Function ImportFctn()
On Error GoTo ImportRVP_Err

DoCmd.SetWarnings False

DoCmd.RunSQL ("DELETE * FROM [SharePrices];")
DoCmd.TransferSpreadsheet acImport, 8, "SharePrices", "C:\Documents and
Settings\ThinkPad\Desktop\Historical Stock Prices.xlsm", True,
"TransposedSheet!RyanRange"

DoCmd.SetWarnings True
'DoCmd.CloseForm "frmImport"

ImportRVP_Exit:
Exit Function

ImportRVP_Err:
MsgBox Error$
Resume ImportRVP_Exit

End Function

I've never worked with a named ranges (in Excel) before, but I've done
this
many times with hard-coded ranges.

Can someone please tell me what's going on here?

Thanks!
Ryan---

.