VBA error when trying to break link in Excel Workbook

[Will Sidwell]

Hi,
Am using VBA code in Access 2010 to create a new Excel Workbook, copy a sheet to it from another Workbook, break links and save:

'   Create new spreadsheet for final report
    Set oXLNewBook = oXLApp.Workbooks.Add
    oXLBook.Sheets("Report").Copy Before:=oXLNewBook.Sheets(1)

'   Define file name for saving Stars Trust MSD Excel Spreadsheets
    txtPath = Application.GetOption("Default database directory")
    txtFullReport = txtPath & "\StarsTrustMSDFullReport" & Format(Me.dteReportEnd, "yyyymmdd") & ".xlsx"
    txtFinalReport = txtPath & "\StarsTrustMSDFinalReport" & Format(Me.dteReportEnd, "yyyymmdd") & ".xlsx"

'   Save, Break Links and Close Spreadsheets
    oXLBook.SaveAs FileName:=txtFullReport
    With oXLNewBook
        .SaveAs FileName:=txtFinalReport
        varLinks = .LinkSources(Type:=xlLinkTypeExcelLinks)
        If Not IsEmpty(varLinks) Then
            For i = 1 To UBound(varLinks)
                .BreakLink _
                    Name:=varLinks(i), _
                    Type:=xlLinkTypeExcelLinks
            Next i
        End If
        .Close Savechanges:=True
    End With
    oXLBook.Close

When it executes the line:

.Close Savechanges:=True

I get the error mesage:

Application-defined error or Object-defined error

If I then open the new spreadsheet in Excel and select Data>Edit Links the "Break Links" button is greyed out and not an option.
Any Ideas?
Cheers,
Will

 

[Cronk]

If you type in the Immediate window
? Application.GetOption("Default database directory")

it will return something like this
C:\databases\myDatabase\

You then add another back slash
& "\StarsTrustMSDFul.......