This is partly an Access question, but partly an Excel question, so Im
posting here (already posted at the Access group).

Im trying to import a range from an Excel sheet into an Access table. The
error I get is as follows, €˜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 (this is running from Access):
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

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

I looked at this MS support article:
http://support.microsoft.com/kb/209924

That seems to confirm that what Im doing is perfectly fine. So, Im
thinking that maybe something in Excel is causing the error when the named
range is created, so Access cant do the import. Here is my VBA code (runs
in Excel) that copies data from a worksheet into a format that is suitable
for Access.

Sub PopulateMacro()

Dim wb As Workbook
Dim wsStock As Worksheet
Dim wsTrans As Worksheet
Dim lngLastCol As Long
Dim lngLastRow As Long
Dim lngRow As Long
Dim lngCol As Long
Dim lngNewRow As Long

Set wb = ThisWorkbook

'Delete the sheet "TransposedSheet" if it exist
Application.DisplayAlerts = False: On Error Resume Next
wb.Sheets("TransposedSheet").Delete
On Error GoTo 0: Application.DisplayAlerts = True

Set wsTrans = wb.Worksheets.Add
wsTrans.Name = "TransposedSheet"
Set wsStock = wb.Sheets("Stocks")

'Assume Row 3 is header
lngLastRow = wsStock.Cells(Rows.Count, "A").End(xlUp).Row - 1
lngLastCol = wsStock.Cells(3, Columns.Count).End(xlToLeft).Column
lngNewRow = 1
For lngRow = 4 To lngLastRow
For lngCol = 2 To lngLastCol

lngNewRow = lngNewRow + 1
wsTrans.Range("A" & lngNewRow).Value = wsStock.Cells(lngRow, 1)
wsTrans.Range("B" & lngNewRow).Value = wsStock.Cells(3, lngCol)
wsTrans.Range("C" & lngNewRow).Value = wsStock.Cells(lngRow, lngCol)
Next
Next

Range("A1").Select
ActiveCell.FormulaR1C1 = "DateTime"
Range("B1").Select
ActiveCell.FormulaR1C1 = "StockSymbol"
Range("C1").Select
ActiveCell.FormulaR1C1 = "StockPrice"

Columns("A:A").Select
Selection.NumberFormat = "m/d/yyyy"
Columns("C:C").Select
Selection.NumberFormat = "$#,##0.00"
Range("A1").Select

Dim nmRange As Name
For Each nmRange In ActiveWorkbook.Names
If nmRange = RyanRange Then nmRange.Delete
Next

Sheets("TransposedSheet").Select
Dim LstRow As Long
LstRow = wsTrans.Cells(Rows.Count, "A").End(xlUp).Row

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("TransposedSheet").Range ("A1").Resize(LstRow,
3).Name = "RyanRange"

End Sub

Everything seems to be fine there. Macro runs, copies data, names the
range. Seems like that is working, but something here is definitely NOT
working. Im working in Office 2007.

Can someone please tell me whats going on here?

Thanks!
Ryan---


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

The sheet names in excel when you use the ADO method (or any SQL)
requires you to add a dollar sign at the end of the sheet name


So acImport becomes acImport$


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=177803

Microsoft Office Help

Thanks Joel. I tried that; didn't work. I've done this many times before,
but with ranges like A1:K5000; never with a named range. That seems to be
quite a problem now. I've seen examples online that do this though. Maybe
it is a reference in Access? I don't really know what to make of this. I'd
appreciate any input though!!


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


"joel" wrote:


The sheet names in excel when you use the ADO method (or any SQL)
requires you to add a dollar sign at the end of the sheet name


So acImport becomes acImport$


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=177803

Microsoft Office Help

.

I just found out (from Ken Snell); range names are unique in an EXCEL file,
so you do not need the sheet name. Use just the range name.

DoCmd.TransferSpreadsheet acImport, 8, "SharePrices", "C:\Documents and
Settings\ThinkPad\Desktop\Historical Stock Prices.xlsm", True,
"RyanRange"
....works!!!
....assume "RyanRange" is the named range...
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:

Thanks Joel. I tried that; didn't work. I've done this many times before,
but with ranges like A1:K5000; never with a named range. That seems to be
quite a problem now. I've seen examples online that do this though. Maybe
it is a reference in Access? I don't really know what to make of this. I'd
appreciate any input though!!


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


"joel" wrote:


The sheet names in excel when you use the ADO method (or any SQL)
requires you to add a dollar sign at the end of the sheet name


So acImport becomes acImport$


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=177803

Microsoft Office Help

.