TransferSpreadsheet Error 3434 "Cannot expand named range"

[detrie]

Development in Access 2007 & Excel 2007
To be deployed on Access 2007 & Excel 2007 AND Access 2007 (runtime) & Excel 2003

All,

This code runs fine when it is executed on Access 2007 and Excel 2007. However, it fails on Access 2007 RUNTIME and Excel 2003 "Error 3434 "Cannot expand named range""

I have defined a Named Range in the Template (Data!A1:AC60000)

The Data tab is the basis for predefined calculations in the ensuing Excel file

Should I be transferring to "Data!A1" instead of a Named range?

Private Sub btnPureMatrix_Click()

On Error GoTo MatrixHandleError

Dim objXLApp As Object
Set objXLApp = CreateObject("Excel.Application")
Dim objXLBook As Object

DoCmd.Hourglass True

'Dim db As DAO.Database

    Set db = CurrentDb

    conPath = GetPath(db.Name)

    'delete the spreadsheet
    Kill conPath & "Pure Matrix.xls"

    ' create a workbook from the template
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open(conPath & "MatrixTemplate.xlt")
    'objXLApp.Visible = True

    objXLApp.DisplayAlerts = False
    objXLBook.SaveAs (conPath & "Pure Matrix.xls")
    objXLBook.Close

   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MatrixData", conPath & "Pure Matrix.xls", True



DoCmd.Hourglass False
DoCmd.SetWarnings True
        Dim strFile As String
        strFile = (conPath & "Pure Matrix.xls")
        Set objXLApp = CreateObject("Excel.Application")
        objXLApp.Visible = True
        Set objXLBook = objXLApp.Workbooks.Open(strFile)
        

ProcDone:
    On Error Resume Next

    ' Let's clean up our act
    
    Set qdf = Nothing
    Set db = Nothing
    Set rs = Nothing
    Set objResultsSheet = Nothing
    Set objXLBook = Nothing
    Set objXLApp = Nothing
objXLApp.DisplayAlerts = True


ExitHere:
    Exit Sub
MatrixHandleError:
    Select Case Err.Number
        Case 3265
            Resume Next
        Case 1004
            Set objXLBook = objXLApp.Workbooks.Open(conPath & "Generic.xlt")
            Resume Next
        Case 53
            Resume Next
        Case 75
            Resume Next
        Case Else
            MsgBox Err.Description, vbExclamation, _
             "Error " & Err.Number
    End Select
    DoCmd.Hourglass False
    Resume ProcDone
	End Sub

 

[detrie]

RESOLVED!

argh!
The problem was I had data in the Named Range of the Excel Template

 

[Robert62]

It could also be that there was data OUTSIDE the named range.

I managed to solve such an error today and my code worked again.
My range included rows 1 to 60, but there was data at row 300.
Thanks for your answer in 2006, it gave me a hint in 2015.

I had run-time error 3226 when I used a editable recordset (created with Docmd.OpenQuery acViewNormal, acEdit

When I changed that to Docmd.OpenQuery acViewNormal, acReadOnly
run-time error 3434 popped-up instead.