Solved

MS Access embed an Excel downloaded template file  on form

Posted on 2016-10-02
7
38 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 19
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 19
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 49

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 49

Expert Comment

by:Gustav Brock
ID: 41826284
You are welcome!

/gustav
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

930 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

11 Experts available now in Live!

Get 1:1 Help Now