Solved

MS Access embed an Excel downloaded template file  on form

Posted on 2016-10-02
7
72 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 21
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 21
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 51

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 51

Expert Comment

by:Gustav Brock
ID: 41826284
You are welcome!

/gustav
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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