embed excel file in access and email as PDF (AUTOMATE) Please help

Posted on 2006-04-11
Last Modified: 2008-01-09
Hi all,
    I'm a newbie here and a rookie programmer, for the past few days I've been breaking my head as to how to do this... I'm trying to save an excel file within my Access 2000 DB. in case the excel file gets deleted on the network It could still be opened from Access, but here's the tricky part.

I have a text box [quote] - where I type in a quote excel file name and an unbound object frame [quoteolefile] which searches for the excel file thru a path with this source code..

Private Sub quoteolefile_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
On Error GoTo Error_quoteolefile_MouseUp
With Me![quoteolefile]
.Enabled = True
.Locked = False
' Specify what kind of object can appear in the field.
.OLETypeAllowed = acOLECreateFromFile
' Class statement--optional for Excel worksheet.
.Class = ".xls"
' Specify the file to be linked.
' Type the correct path name.
.SourceDoc = "\\Srv-ndt\main\NDT_Transducers\JFCECNTEST\excelandvmx\" & Me![quote] & ".xls"
' Range statement--optional for Excel worksheet.
.SourceItem = ""
' Create the embedded object.
.action = acOLEembed
' Optional size adjustment.
.SizeMode = acOLESizeClip
End With
Exit Sub
MsgBox CStr(Err) & " " & Err.DESCRIPTION
Resume Exit_quoteolefile_MouseUp
End Sub

but I get error message 2777, I'm not sure why whether it's linked or embedded I get the same error code 2777.

My mission is to keep a different quote excel file for every record in my database, but because most people don't have access to my network drive I can't link the excel file so I need to have the quote excel file open in access in a way that they can view the file like an email attachment, so they can open the quote in excel.
but in the midst of things I need this automated so that I type the quote# in a text box and it searches thru my drive.
Then I need to send this file as an email attachment in outlook and an attachment of the report as "PDF"

Please help, is this even possible to do? maybe I'm just getting my hopes up. I would really appreciate your help.


Question by:Jeyc
    LVL 3

    Expert Comment

    You can save files in tables, choose the datatype OLE Object in table design view.

    You'll then be able to right click that field on a form and add a file of any type
    LVL 3

    Expert Comment

    You'll need a third party tool to do the pdf bit.

    google:  pdf995 or cutepdf
    LVL 65

    Expert Comment

    by:Jim Horn
    >I'm trying to save an excel file within my Access 2000 DB.
    I highly recommend against this.  Storing OLE Objects (Excel, Word, heavy graphics, etc.) causes considerable database bloat in Access, which means frequent compact-repairing.

    Most developers I know when faced with this situation will store the Excel spreadsheet in a specific drive:\path\file.xls, store that path in a text column of a table, and when they need the spreadsheet just retrieve the string, validate using FileSystemObject that the spreadsheet does exist in the string saved in your table, then open it using some kind of Shell command.

    Hope this helps.
    LVL 38

    Expert Comment

    by:Jim P.
    The other option is to save the data in the database and then generate the excel SS on the fly.

    Author Comment

    thanks for all your help but jimpen what would be the script to save the data and generate the excel ss on the fly?

    sorry but I'm not familiar with creating excel ss on the fly?...


    Author Comment

    Sorry to be more specific...

     I want to basically import an excel file into access, but because sales doesn't have access to my drive on the network, They can't open the excel file thru my unbound frame because it's no longer linked.

    so how can I open this excel file thru my MDB, so that they can fully view it in excel.

    LVL 38

    Expert Comment

    by:Jim P.
    This will generate just basic excel spread sheet that looks like a query.  
    Public Function OutputExcelFiles()

    Dim DB As Database
    Dim RS As Recordset
    Dim QDF As QueryDef
    Dim QryName As String
    Dim SQL As String

    SQL = "SELECT DeptCode " & _
        "FROM TableDept " & _
        "ORDER BY DeptCode"

    Set DB = CurrentDb()                'Use the current database
    Set RS = DB.OpenRecordset(SQL)      'actually open the recordset

    If RS.EOF = False Then
        MsgBox "No Data", vbExclamation, "Exiting Fuction"
        Set RS = Nothing
        Set DB = Nothing
        Exit Function
    End If

    Do Until RS.EOF

        SQL = "SELECT TableGL.GL & '-' & TableDept.DeptCode as Full_GL " & _
            "FROM TableGL, TableDept " & _
            "WHERE TableDept.DeptCode = '" & RS!DeptCode & "'" & _
            "ORDER BY TableDept.DeptCode"
        QryName = RS!DeptCode
        'Check if the old query is there
        If TableExistence(QryName) = True Then
            DoCmd.SetWarnings False
            DoCmd.DeleteObject acQuery, QryName
            DoCmd.SetWarnings True
        End If
        'This is where I actually create the query
        With DB
            Set QDF = .CreateQueryDef(QryName, SQL)
        End With
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, QryName, _
                "c:\MyPath\MyFile.xls", True
        DoCmd.SetWarnings False
        DoCmd.DeleteObject acQuery, QryName
        DoCmd.SetWarnings True


    Set RS = Nothing
    Set DB = Nothing

    End Function
    LVL 38

    Accepted Solution

    For formatted sheets it would be something like:
     Dim xl As Object
    Dim wb As Object

    Set xl = CreateObject("Excel.Application")
    Set wb = xl.workbooks.Add
    wb.sheets( 0).Range("A1").copyfromrecordset rs
    xl.Visible = True

    There are examples of how to do this all over the topic area.  The idea I'm trying to get at is that you link/import the data into permanent tables in the database and then export it to excel if need be or just create the reports you need and send them.
    LVL 38

    Expert Comment

    by:Jim P.
    Glad to be of assistance. May all your days get brighter and brighter.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now