Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    42

    Unanswered: Export to Excel Template

    Is there a way to export a query in Access 97 to an Excel template? I know you can export the query to Excel, but what I want is to used an existing Excel template with built in calculations and functions into it already. I posted this late yesterday and just trying it again. Any help would be greatly appreciated.

    Thanks,

    ESM

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    can't help you with "query to template", but i regularly do recordset (from a query) to XLS. i'm filling only specific cells.

    it goes something like this (using a DAO recordset, so it should also fly with A97):
    Code:
        '...all the usual Dim, on error, etc
        Set aXLS = CreateObject("Excel.Application")
        aXLS.Workbooks.Open XLSpath '   "c:\whatever\myFile.XLS"
        numS = 0
        For Each sXLS In aXLS.Worksheets
            numS = numS + 1
            ' i expect only 1 worksheet & many graphs - check
            If sXLS.Name <> "DATA" Then 
                MsgBox "Unexpected XLS worksheet name!", vbCritical, "blah"
                GoTo exit_butMakeReport
            End If
        Next
        If Not numS = 1 Then
            MsgBox "Unexpected number of XLS worksheets: " & numS, vbCritical, "blah"
            GoTo exit_butMakeReport
        End If
        Set recs = dabs.OpenRecordset("XLSfiller", dbOpenSnapshot)
        With recs
            Do While Not .EOF
                aXLS.Cells(!xlsRow, !xlsCol).Value = !ValueForRowCol
                .MoveNext
            Loop
        End With
        aXLS.Workbooks.Close
    ...and it goes on to mail the XLS report. i don't bother with XLT because i overwrite the data cells each time.

    as you can probably tell from the line
    aXLS.Cells(!xlsRow, !xlsCol).Value = !ValueForRowCol
    the recordset returned by the query "XLSfiller" contains Row, Column, and Value data.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2004
    Posts
    42
    Izy,

    Thank you for your reply. It will give me an starting point and I will play around with it. I will let you know of the result.

    Thanks,

    ESM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •