using an access application --> generate reports in Excel

or you could record a macro in excel,build the formatting on a blank sheet, stop the macro then open it copy and paste into an access module in conjunction with the copyfromrecordset code in the previous comment. sometimes I use templates and sometimes I build the formatting into the access module. I'd rather use access to develop with so I always push the data into excel.

almaha

<Somewhat off topic>
Just curious...

What are you formatting these values based on?
Can you give an example?

JeffCoachman

ok,
you guys are great help
but can you just tell me what to do as a first step?
coz all this is new to me
the code that matthewspatrick wrote
i would like to try it
but where shall i place it?
in a module?
in a button click macro?
i have a form that has a button << it is supposed to generate the report
even the excel template you guys talked about
how can i link it to access?

almaha,

You could certainly integrate the code I supplied into a OnClick event sub for a command button on your
form.  As for "linking" an Excel template to Access, you don't.  You could create your XLT in Excel,
and then I showed in my code sample how to tie into that template--instead of using a "generic"
workbook, the code can create a new workbook based on your template.

Regards,

Patrick

run time error 91
object variable or with block variable not set

debug:
this line is cozing the trouble:
Set xlWs = xlWb.Worksheets(1)

i changed the line to an *.xlt file
but still i got another error msg


run time error 5
invalid procedure call or argument

            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                "=LEFT($A2,8)=""Category"""

Private Sub Command17_Click()
 
    Dim xlApp As Object, xlWb As Object, xlWs As Object
    Dim rs As DAO.Recordset
    Dim Counter As Long
 
    Set rs = CurrentDb.OpenRecordset("qryCrstb")
    Set xlApp = CreateObject("Excel.Application")
    'Set xlWb = xlApp.ActiveWorkbook
    ' to use a template instead, replace above line with:
    Set xlWb = xlApp.Workbooks.Add("E:\rptQryCrstb.XLT")
    Set xlWs = xlWb.Worksheets(1)
 
    With xlWs
        For Counter = 1 To rs.Fields.Count
            .Cells(1, Counter) = rs.Fields(Counter - 1).Name
        Next
        .[a2].CopyFromRecordset rs
        With .[a2].Resize(.UsedRange.Rows.Count - 1, .UsedRange.Columns.Count)
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                "=LEFT($A2,8)=""Category"""
            With .FormatConditions(1)
                .Font.Bold = True
                .Font.Italic = True
                .Interior.ColorIndex = 6 'yellow
            End With
        End With
    End With
 
    xlWb.SaveAs "name and path to save file to"
    xlWb.Close False
    Set xlWs = Nothing
    Set xlWb = Nothing
    xlApp.Quit
    Set xlApp = Nothing
 
    MsgBox "Done"
 
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:

MikeToole said:
>>To make life easier you should add a reference to Excel in your Access project:

Maybe, maybe not.  If there is any possibility that >1 version of Office will have to be supported
(e.g., the developer is using Office 2007, but some users are still on 2003, or vice versa), then
late binding may be a better choice for some.

When I am doing automation across Office apps, I tend to do the initial development work using
early binding, but then switch to late binding for the code that actually gets pushed.

almaha,

That should have worked, but try replacing:

    Set xlWb = xlApp.Workbooks.Add("E:\rptQryCrstb.XLT")

with:

    xlApp.Workbooks.Add "E:\rptQryCrstb.XLT"
    Set xlWb = xlApp.ActiveWorkbook

Alternatively, you could try adding the statement DoEvents  on a line following the line where you
create the new workbook based on your template, like this:

    Set xlWb = xlApp.Workbooks.Add("E:\rptQryCrstb.XLT")
    DoEvents
    Set xlWs = xlWb.Worksheets(1)

Regards,

Patrick