Export field names to certain cells in Excel Template

[Sharkiness]

Hi all who wish to help,

I have created a database that contains five different tables. Each linking to a main table through an ID.

The database opens asking for an ID. When ID is input and the relevant command button is clicked a form opens spplying details for this individual as well as any other data from the other tables in subforms.

The overall form and subforms are based on queries.

I want to export the data to excel, on seperate spreadsheets for each query which i have completed successfully using the 'Transferspreadsheet' function.

The problem here is I cannot reformat the column headings.

I would also liek the data to be exported into excel using a template so that each query has it's own worksheet but then each field is exported into a particular cell.

I have used the advice on the following link at tektips:

http://www.tek-tips.com/faqs.cfm?fid=7171

which works only for the data in the overall form. Does anybody know a way that I could use this to export the additional queries to worksheets within the same workbook and in their own cells,

Any help appreciated.

Thanks

 

[Guus2005]

The example in the link only need some slight modification, see below.
No, i didn't test the code.

Public Function ExportXLS(strSpreadsheet As String, intSheet As Integer, strQuery As String)
'Write the result from a query to a spreadsheet and a certain sheet.

On Error GoTo Err
    
    Dim fso
    Dim oExcel      As Excel.Application
    Dim oBook       As Workbook
    Dim oSheet      As Worksheet
    Dim intNumSheet As Integer
    Dim rst         As Recordset

    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(strSpreadsheet) = False Then
        MsgBox ("File not found" & vbCrLf & strSpreadsheet)
        Set fso = Nothing
        Exit Function
    End If
        
    DoCmd.Hourglass True
    
    'Open excel, go to sheet
    Set oExcel = Excel.Application
    Set oBook = oExcel.Workbooks.Open(strSpreadsheet)
    intNumSheet = oBook.Sheets.Count
    If [intSheet] > intNumSheet Then
        MsgBox "You specified sheet " & [intSheet] & ", but there are only " & [intNumSheet] & " sheets in the workbook."
        oExcel.Quit
        Set fso = Nothing
        Exit Function
    End If
    
    Set rst = CurrentDb.OpenRecordset(strQuery)
    
    Set oSheet = oBook.Worksheets([intSheet])
    oSheet.Range("A1").CopyFromRecordset rst
    
    oBook.Save
    oExcel.Visible = True
    
    DoCmd.Hourglass False

Exit Function
Err:
    oExcel.Quit
    Set fso = Nothing
    DoCmd.Hourglass False
    MsgBox Err.Description
End Function

Using the Excel.Application object instead of CreateObject("Excel.Application") has the advantage that you can use intellisense to complete the code. It requires a reference to the Excel object.

HTH