Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 597
  • Last Modified:

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

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.


  • 4
  • 2
  • 2
  • +1
1 Solution
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
You'll need a third party tool to do the pdf bit.

google:  pdf995 or cutepdf
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Jim P.Commented:
The other option is to save the data in the database and then generate the excel SS on the fly.
JeycAuthor Commented:
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?...

JeycAuthor Commented:
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.

Jim P.Commented:
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
Jim P.Commented:
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.
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now