TransferSpreadsheet and NamedRange; not working!

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

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---
 
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.
 
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''.


Ken Snell said:
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/


ryguy7272 said:
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---
Click to expand...


.
Click to expand...
 
Back
Top