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.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.