Solved

Need a worksheet/spreadsheet to be created with each new record

Posted on 2011-09-09
9
310 Views
Last Modified: 2012-05-12
I currently have a database I am working on and I need to have a spreadsheet or something similar attached to each record. I need the spreadsheet to be created when the record is created. Any ideas how I can do this or something similar?
0
Comment
Question by:timamartin
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 36512193
Which version of Access?
0
 
LVL 33

Expert Comment

by:Norie
ID: 36512223
How wil the records be created?

Is it via a form?

The following will create a new, single sheet Excel workbook in the same directory of the database with the name taken from Field1 on the form.
Option Compare Database
Option Explicit

Const xlWBATWorksheet = -4167


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim xlApp As Object
Dim xlWB As Object
Dim strName As String
Dim strPath As String


    If Me.NewRecord Then
    
        strPath = CurrentProject.Path
        strName = Me.Field1
        Set xlApp = CreateObject("Excel.Application")
        
       Set xlWB = xlApp.Workbooks.Add(xlWBATWorksheet)
        
        xlWB.SaveAs strPath & "/" & strName
        
        xlWB.Close
        
        xlApp.Quit
        
        Set xlWB = Nothing
        Set xlApp = Nothing
        
    End If
    

End Sub

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36512246
<have a spreadsheet or something similar attached to each record.>
"Or something similar"?
What exactly are you asking for? what would "Something Similar" be?


<I need the spreadsheet to be created when the record is created.>
And what is in this spreadsheet?

Creating a new File for each record seems a bit extreme...

Can you perhaps First take a moment and explain the need for this...
What is your ultimate goal here?

Perhaps there is a simpler approach...
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 33

Expert Comment

by:Norie
ID: 36512280
Even though I posted code, I've got to agree here - creating something for each record seems a bit much.

I could maybe understand a new worksheet in an existing workbook but even that's a bit much.
0
 

Author Comment

by:timamartin
ID: 36512323
I am currently using version 2010.
0
 

Author Comment

by:timamartin
ID: 36512334
The database is to track all of the company's departmental projects. Along with the pertinent info like what the project is, why dates and costs there is a button that will open up a WorkPlan for the project. The workplan could be an Excel spreadsheet that I already have. I suppose this would be ideal because I could set it up as a template.
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 36512394
The code I posted will create a new workbook and can be adapted to add a worksheet to an existing workbook or create the workbooks from a template.
Option Compare Database
Option Explicit
Const xlWBATWorksheet = -4167

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object
Dim strName As String
Dim strPath As String

    If Me.NewRecord Then
    
        strPath = "C:\" ' path to workbplan spreadsheet

        strName = Me.Field1

        Set xlApp = CreateObject("Excel.Application")
        
       Set xlWB = xlApp.Workbooks.Open(strPath & "\Workplan.xlsx")
        
       Set xlWS = xlWB.Worksheets.Add
 
       xlWS.Name = strName


        xlWB.Close True
        
        xlApp.Quit
        
        Set xlWB = Nothing
        Set xlApp = Nothing
        
    End If
    

End Sub

Open in new window

0
 

Author Comment

by:timamartin
ID: 36512504
When you attach a document to a record via a form does it just link to the doc or does it somehow embed it?
0
 
LVL 33

Expert Comment

by:Norie
ID: 36512749
Could you please clarify what you want to do?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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