Solved

MS Access embed an Excel downloaded template file  on form

Posted on 2016-10-02
7
31 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

759 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

20 Experts available now in Live!

Get 1:1 Help Now