R
ryguy7272
This is partly an Access question, but partly an Excel question, so I’m
posting here (already posted at the Access group).
I’m 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
I’ve never worked with a named ranges (in Excel) before, but I’ve 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 I’m doing is perfectly fine. So, I’m
thinking that maybe something in Excel is causing the error when the named
range is created, so Access can’t 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. I’m working in Office 2007.
Can someone please tell me what’s going on here?
Thanks!
Ryan---
posting here (already posted at the Access group).
I’m 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
I’ve never worked with a named ranges (in Excel) before, but I’ve 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 I’m doing is perfectly fine. So, I’m
thinking that maybe something in Excel is causing the error when the named
range is created, so Access can’t 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. I’m working in Office 2007.
Can someone please tell me what’s going on here?
Thanks!
Ryan---