MS Access embed an Excel downloaded template file on form

Dear Experts,
My Access for has an import button to import records from Excel spreadsheet. The process works fine but what I need to do is to make an Excel file template available for users to 'download' and use to fill in there records into the template and 'use' that template to import the records. Is there a way to 'embed' the spreadsheet file so when I commercialize the application any user can 'download' the template from the application without having to 'include' the template excel file with the Access Database file. Somewhere down the line the excel file might get lost if I simply 'link' to the file. I'd like to embed if possible.
If not, I suppose the only thing I could do is to create a little help form that provides the user information on 'how to setup' up their excel file for import.
shogun5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
in your distribution package, how about putting the Excel template in the same directory as the database, or a known folder below.  Then have a button to create an Excel file to fill and make it using the template.

There is no way, that I know of, to include an Excel Template IN the Access file unless it was an object -- at that point it may as well be external as there would be gymnastics to use it as a template.  You could, however, embed all the code it takes to make it. Again, the file to fill would have to be created using Access.
shogun5Author Commented:
Crystal,

I have an idea. I have a table called 'tblSettings'. This table holds one record and is unrelated to any other records. Bascially one record to hold the various user display settings. I created an attachment field in that tblSettings records called DownloadTemplate. i uploaded the Excel Template as an attachment to that record field and it works great. Now here is where I am stuck.
I want to place that attachment field on the frmClass form so users can simply double click on he attachment and download he Excel file. The problem I am having is that tblSettings does not relate to any records on the form (bound to tblClass) so I can't seem to drop the field onto the form. I tried a dlookup function on the attachment control hoping to grab the value from the tblSettings.DownloadTemplate field but it's not working (no file shows up).

Here is my Dlookup on the attachment control: =DLookUp("[tblSettings]","[DownloadTemplate]")

Thoughts?

Mike
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
use a subform and bind it to the table (or a query that just gets one record) and has the attachment control -- however you may wish to use an OLE field since an attachment can have multiple values -- not sure as I wouldn't do it this way so I have never tried.  DLookup can't return an object.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Gustav BrockCIOCommented:
You need to mobilize object types Recordset2 and Field2:
Private Sub SaveAttachment_Click()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset2
    Dim rsA As DAO.Recordset2
    Dim fld As DAO.Field2
    Dim strPath As String
    
    'Get the database, recordset, and attachment field
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Example")
    Set fld = rst("Master")
    
    strPath = "C:\Test"
    
    'Navigate through the table
    Do While Not rst.EOF
    
        'Get the recordset for the Attachments field
        Set rsA = fld.Value
        
        'Save all attachments in the field
        Do While Not rsA.EOF
            strPath = strPath & "\" & rsA("FileName")
            
            'Make sure the file does not exist and save
            If Dir(strPath) = "" Then
                rsA("FileData").SaveToFile strPath
            End If
                       
            'Next attachment
            rsA.MoveNext
        Loop
        rsA.Close
        
        'Next record
        rst.MoveNext
    Loop
    
    rst.Close
    dbs.Close
    
    Set fld = Nothing
    Set rsA = Nothing
    Set rst = Nothing
    Set dbs = Nothing

End Sub

Open in new window

See the attached demo.
For the demo to work, first create the folder C:\Test

/gustav
Example.accdb

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
shogun5Author Commented:
Gustav,

This is great! Works like a charm! Actually I modified your code to grab the user's path to the desktop and just save there for now:
  
   
   Dim dbs As DAO.Database
    Dim rst As DAO.Recordset2
    Dim rsA As DAO.Recordset2
    Dim fld As DAO.Field2
    Dim strPath As String
    Dim strUserName As String
    
    'Get the database, recordset, and attachment field
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblSettings")
    Set fld = rst("DownloadTemplate")
    
     strUserName = Environ("UserName")

     strPath = "C:\documents and settings\" & strUserName & "\Desktop"
    
    
    'Navigate through the table
    Do While Not rst.EOF
    
        'Get the recordset for the Attachments field
        Set rsA = fld.Value
        
        'Save all attachments in the field
        Do While Not rsA.EOF
            strPath = strPath & "\" & rsA("FileName")
            
            'Make sure the file does not exist and save
            If Dir(strPath) = "" Then
                rsA("FileData").SaveToFile strPath
            End If
                       
            'Next attachment
            rsA.MoveNext
        Loop
        rsA.Close
        
        'Next record
        rst.MoveNext
    Loop
    
    rst.Close
    dbs.Close
    
    Set fld = Nothing
    Set rsA = Nothing
    Set rst = Nothing
    Set dbs = Nothing
   
   

Open in new window

shogun5Author Commented:
Thanks!
Gustav BrockCIOCommented:
You are welcome!

/gustav
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.