Code to Update Excel from Access

[portmancp]

Hi, i've looked everywhere for an example of how to populate an Excel template from an Access database table and can't find this anywhere. Is this possible to do? I basically have a table in Access and would like to update an Excel template and then email it to a recipient. Please help!!!

[Stressed]

Hi, i've looked everywhere for an example of how to populate an Excel template from an Access database table and can't find this anywhere. Is this possible to do? I basically have a table in Access and would like to update an Excel template and then email it to a recipient. Please help!!!

here is some code that may help you.

you need to create a template and then update the code to suit your needs.

Public Function CreateBOCReport() As Integer

Dim rst As DAO.Recordset
Dim strSQL As String
Dim strFileName As String
Dim strSaveFile As String
Dim intRowStart As Integer
Dim intRowStartA As Integer
Dim intRowA As Integer
Dim intRow As Integer
Dim textdateFrom As String
Dim textdateTO As String
Dim textDateWORDING As String
Dim intNumber1 As Long
Dim intNumber2 As Long
Dim intNumber3 As Long
Dim intNumber4 As String
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

If Forms!frmweeklystats!txtDateFROM = "" Then
    MsgBox "Please enter a valid date range."
Else
    textdateFrom = Forms!frmweeklystats!txtDateFROM
    textdateTO = Forms!frmweeklystats!txtDateTO
End If

'open recordset
strSQL = "select * from tblBOC"
Set rst = CurrentDb.OpenRecordset(strSQL)
'check if there are stats for the date range
If rst.EOF And rst.BOF Then
    MsgBox "No daily stats for this day.  Try again.", vbOKOnly + vbInformation, "No Data Found"
    Exit Function
End If

strFileName = "\Management Reporting\boc.xlt"
'open workbook in memory
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Add(strFileName)
Set xlSheet = xlBook.Worksheets(1)
'where you are going to save the file
strSaveFile = Application.CurrentProject.Path & "\Enrolment Stats for " & textdateFrom & " to " & textdateTO & ".xls"
   
Do While Not rst.EOF
    'determine where you are going to start inserting stats in the template
    If rst.Fields("Type") = "Removes" Then
        intRowStart = 23 'this is the row number
    Else
        intRowStart = 7 'this is the row number
    End If
            
    Select Case rst.Fields("Network")
        Case "SOME"
            intRow = intRowStart    'this is the row number
        Case "ALL"
            intRow = intRowStart + 1 'this is the row number + 1 (moves down the row)
        Case "MORE"
            intRow = intRowStart + 2 'continue this based on your case
    End Select
    
    textDateWORDING = "From " & textdateFrom & " to " & textdateTO 'this is for the title row
        
        'set the values in the query into the column/row based on the intRow variable,column#
        intNumber1 = rst.Fields("ReceivedE")
        xlSheet.Cells(intRow, 2) = intNumber1
        intNumber1 = 0
        intNumber2 = rst.Fields("ProcessedE")
        xlSheet.Cells(intRow, 3) = intNumber2
        intNumber2 = 0
        intNumber3 = rst.Fields("TOBEE")
        xlSheet.Cells(intRow, 4) = intNumber3
        intNumber3 = 0
        intNumber4 = rst.Fields("ReceiveddateE")
        xlSheet.Cells(intRow, 5) = intNumber4
    
    'where you can individually assign row,column
    xlSheet.Cells(5, 1) = textDateWORDING
    xlSheet.Cells(21, 1) = textDateWORDING
    
    rst.MoveNext
Loop

'saving the file
xlBook.SaveAs strSaveFile, xlExcel9795

xlBook.Close
xlApp.Quit
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
rst.Close
Set rst = Nothing

MsgBox "Report has been created and placed on your desktop.", , "Report Complete"
    
    
End Function

[portmancp]

Thanks! That's great. Thanks for your help.