Access to Excel template

[Stigmundfreud]

Sorry for such a first post!

I've been searching for a while now on a method to export to Excel using a preformatted template. I'm quite basic with my VB skills but quick to pick up once pointed in the right direction. I've successfully outputted to Excel without problem but I'd like to use an existing template for the formatting.

The searches I've had results for all talk about being able to do it but not with actual code/guide on how to. I appreciate this is something that may have been covered to death but my search for "excel template" pulls back so many I thought I would ask again.

I am running Office 2003 so Access and Excel 2003.

Thanks in advance for any help

 

[boblarson]

You can do it by doing something SIMILAR to my code on my website which exports a table or query to Excel. Instead of using the CopyFromRecordset rst part you would iterate through the recordset and place things in the appropriate cells that you need.

So, something like this:

[FONT=courier new]Public Function SendTQ2Excel(strTQName As String, Optional strSheetName As String)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to name it to[/FONT]
[FONT=courier new]    
    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As Field[/FONT]
[FONT=courier new]
    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107
[/FONT] 
[FONT=courier new]    On Error GoTo err_handler[/FONT]
[FONT=courier new][/FONT] 
[FONT=courier new]    Set rst = CurrentDb.OpenRecordset(strTQName)[/FONT]
[FONT=courier new][/FONT] 
[FONT=courier new]    Set ApXL = CreateObject("Excel.Application")[/FONT]
[FONT=courier new]
    Set xlWBk = ApXL.Workbooks.Add

    ApXL.Visible = True
        
    Set xlWSh = xlWBk.Worksheets("Sheet1")

    If Len(strSheetName) > 0 Then
        xlWSh.Name = Left(strSheetName, 34)
    End If

[/FONT][FONT=courier new]    xlWSh.Range("A2").Value = rst!MyFieldNameHere[/FONT]
[FONT=courier new]    xlWSh.Range("D4").Value = rst!MyFieldNameforDataToD4Here[/FONT]
[FONT=courier new][FONT=courier new]    xlWSh.Range("G34").Value = rst!MyFieldNameforDataToG34Here[/FONT]
[FONT=courier new][FONT=courier new]    xlWSh.Range("A14").Value = rst!MyFieldNameforDataToA14Here[/FONT]
[/FONT][/FONT][FONT=courier new] 

    
    ' selects all of the cells
    ApXL.ActiveSheet.Cells.Select

    ' does the "autofit" for all columns
    ApXL.ActiveSheet.Cells.EntireColumn.AutoFit

    ' selects the first cell to unselect all cells
    xlWSh.Range("A1").Select
[/FONT] 
[FONT=courier new]    rst.Close
    Set rst = Nothing[/FONT]
[FONT=courier new][/FONT] 
[FONT=courier new]    Exit Function[/FONT]
[FONT=courier new]
err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Exit Function[/FONT]
[FONT=courier new]End Function[/FONT]

 

[Stigmundfreud]

Thanks Bob I'll start playing with this tomorrow when I am back in the office.

 

[Stigmundfreud]

Bob just to say thanks for your pointers, got it all working nicely and even moved all the excel macros into the access code so do all the formatting there now.

 

[shieriel]

Hi, may i just ask how to modify the code such that the excel file name will be the name of my query, also with the sheet name? tia