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