Solved

MS Access embed an Excel downloaded template file  on form

Posted on 2016-10-02
7
59 Views
Last Modified: 2016-10-03
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.
0
Comment
Question by:shogun5
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 20
ID: 41825754
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.
0
 

Author Comment

by:shogun5
ID: 41825761
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
0
 
LVL 20
ID: 41825774
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.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41825916
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
0
 

Author Comment

by:shogun5
ID: 41826252
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

0
 

Author Closing Comment

by:shogun5
ID: 41826276
Thanks!
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41826284
You are welcome!

/gustav
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

735 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