Breaklink in Excel doc from Access module

[eoan]

Hi again - today's problem:

At a given point in a script I am left with an excel document that is linked to data in another excel document; I would like to remove the links and leave only values and to do this from my Access module. I've tried using:

objXLApp.ActiveWorkbook.BreakLink _
    Name:="V:\Information Services\Reporting Year 08 - 09\NCAs\Newham\Newham Live Workbook.xls", _
    Type:=xlLinkTypeExcelLinks

However, I understand that Access does not recognise Type:=xl... and I wondered if there was a numerical code I could use instead (had this problem with SaveAs until Chris suggested using -4143).

If not, could anyone tell me how would I "add a reference to Excel", Chris' other suggestion?

Many thanks...

 

[boblarson]

It's not that it doesn't recognize the Type:= syntax, but what it doesn't know is the Excel Constants (xlLinkTypeExcelLinks). You have to provide the actual value of that constant (or declare it as a constant in a standard module and assign the value there).

 

[ecawilkinson]

eoan,

to find out the value of a excel constant, go to the VBa editor in Excel, activate the Immediate window (CTRL-G), then key in (including the question mark):

?xlLinkTypeExcelLinks

then press enter. This will give you the value of the constant. (This technique is true for constants from all the Office programs, eg wdNormal in Word, acPrint from Access, etc.

HTH,
Chris

 

[eoan]

Done it again Chris - thanks so much, it works perfectly! And such a useful thing to know, too.

I will post the finished article, well, once it's finished...

Thanks again!

 

[eoan]

Okay, here's the working script:

Option Compare Database

'------------------------------------------------------------
' Newham_emailer
'
'------------------------------------------------------------
Function Newham_emailer()
On Error GoTo Newham_emailer_Err

Dim FileLoc As String
Dim DraftLoc As String
FileLoc = "V:\Information Services\Reporting Year 08 - 09\NCAs\Newham\Newham Live Workbook.xls"
DraftLoc = "V:\Information Services\Reporting Year 08 - 09\NCAs\Newham\"

' Run Queries, export to pre-formatted excel file.
    DoCmd.TransferSpreadsheet acExport, 8, "qry_frm_daterange", FileLoc, False, ""
'lots more of these...

' Open the excel doc.
    Dim objXLApp As Object
    Dim objXLBook As Object
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open(FileLoc)
    objXLApp.Application.Visible = False
    
' Grab the date range used to create report
    Dim BegDate As String
    Dim EndDate As String
    BegDate = [Forms]![frm_npct_main]![Text2]
    EndDate = [Forms]![frm_npct_main]![Text4]
    
' Copy the worksheets, close orignial (template) document without saving
    objXLBook.Sheets("Newham").Copy
    objXLBook.Close acSaveNo
    
' Break Links between new and old documents
    objXLApp.ActiveWorkbook.BreakLink _
        Name:="V:\Information Services\Reporting Year 08 - 09\NCAs\Newham\Newham Live Workbook.xls", _
        Type:=1

' SaveAs and Close
    objXLApp.ActiveWorkbook.SaveAs Filename:= _
    DraftLoc & "DRAFT Newham Summary, " & Format(BegDate, "dd.mm.yy") & "-" & Format(EndDate, "dd.mm.yy") & " ISSUED " & Format(date, "dd.mm.yy") & ".xls", _
        FileFormat:=-4143, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    objXLApp.ActiveWorkbook.Close
    
' Email
    Dim EmailApp, NameSpace, EmailSend As Object
    Set EmailApp = CreateObject("Outlook.Application")
    Set NameSpace = EmailApp.GetNamespace("MAPI")
    Set EmailSend = EmailApp.CreateItem(0)

    EmailSend.To = "name@address.com"
    EmailSend.Cc = "cc-name@address.com"
    EmailSend.Subject = "DRAFT Newham Report"
    EmailSend.Body = "Please find attached draft Newham report for the period " & BegDate & "-" & EndDate & "." & vbCrLf & vbCrLf & "Kind regards," & vbCrLf & " " & vbCrLf & " " & vbCrLf & "MyName" & vbCrLf & vbCrLf & "MyFullName" & vbCrLf & "MyJobTitle"
    EmailSend.Attachments.Add DraftLoc & "DRAFT Newham Summary, " & Format(BegDate, "dd.mm.yy") & "-" & Format(EndDate, "dd.mm.yy") & " ISSUED " & Format(date, "dd.mm.yy") & ".xls"
    EmailSend.Display
    EmailSend.Send

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing

Newham_emailer_Exit:
    Exit Function

Newham_emailer_Err:
    MsgBox Error$
    Resume Newham_emailer_Exit

End Function

This extracts data from access, puts it into a template document which has it's own summary page, it then creates a new document from just the summary page, closes the template doc without saving changes, saves the new doc with a filename incorporating the date range used for the report and today's date and emails it out.