MS Access macro that generates an Excel file from a template

[zzzjoshzzz]

Hi everyone, I don't yet have experience creating an automatic Excel sheet from an Excel template for a user that uses data from the database.

Essentially, I'm looking for a way to fill out the fields in the attached Excel sheet (and have the graph automatically change) and for the Excel sheet to pop up as a new file for the user (IE the template would stay untouched). You'll see in the Excel sheet that there are a number of cells that need to be filled from Access. I have a query in Access PER CELL that needs to be filled.

So, essentially I have 14 queries and I need the results of those queries to appear in the cells of a local version of an Excel template.

Thanks so much for your advice!

StatusReportTemplateClean.zip

[June7]

One option is code behind Excel that pulls data from Access in the form of a recordset object and then writes data from the recordset into cell. Other option is code behind Access that opens and manipulates Excel object in VBA to write data into cells.

Maybe this will help http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm

Example code from my Excel:

Dim rsData As DAO.Recordset
Dim intNCount As Integer
Dim intRecords As Integer
Dim j As Integer
Set rsData = DAOdb.OpenRecordset("SELECT * FROM GeoDataAll WHERE StateNum='" & gstrStateNum & "' AND ProjectName='" & Replace(strProjectName, "'", "''") & "';", dbOpenSnapshot)
rsData.MoveLast
intRecords = rsData.RecordCount
rsData.MoveFirst
If intRecords = 0 Then
    MsgBox "No samples found.  Check your entry and try again." & vbCrLf & "If this error repeats, click AboutDATAGRAB to get administrator contact info.", vbApplicationModal, "No Samples"
    Call Reset("All")
ElseIf intRecords > 1002 Then
    MsgBox "Number of records exceeds 1002.  Operation will terminate." & vbCrLf & "Notify administrator. Click AboutDATAGRAB to get administrator contact info.", vbCritical, "ExcessRecords"
    Call Reset("All")
Else
    With Worksheets("Samples")
        .Range("B16").CopyFromRecordset rsData, , 4
        .Range("F16:F" & 16 + rsData.RecordCount) = "Yes"
        .Range("C1").Value = gstrStateNum
        .Range("C3").Value = intRecords
        .Visible = True
        .Activate
        .EnableAutoFilter = True
        .Protect Contents:=True, userInterfaceOnly:=True
        Worksheets("Start").Visible = False
    End With
    With Worksheets("Results")
        j = 16
        Do While Worksheets("Samples").Cells(j, 1).Value & "" <> ""
            If Worksheets("Samples").Rows(j).Hidden = False And Worksheets("Samples").Cells(j, 6).Value = "Yes" Then
                rsData.FindFirst ("LabNum='" & Worksheets("Samples").Cells(j, 3).Value & "'")
                .Cells(intNCount, 1).Value = rsData.Fields("SampledFrom")
                .Cells(intNCount, 2).Value = rsData.Fields("LabNum")
                .Cells(intNCount, 7).Value = rsData.Fields("FieldNum")
                'Increment intNCount so the next time around it displays data in the row below this one
                intNCount = intNCount + 1
           End If
           j = j + 1
       Loop
   End With
End If