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

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?
timamartinAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
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
 
peter57rCommented:
Which version of Access?
0
 
NorieVBA ExpertCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
NorieVBA ExpertCommented:
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
 
timamartinAuthor Commented:
I am currently using version 2010.
0
 
timamartinAuthor Commented:
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
 
timamartinAuthor Commented:
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
 
NorieVBA ExpertCommented:
Could you please clarify what you want to do?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.