Export to Excel

[Groundrush]

Does anyone know how to keep the report design when exporting to excel.

I need to have the headings on top instead of in its own column.

If that makes any sense...

see attached screen shot.


thanks to any helpers out there.

cheers

 

[IMO]

See if you can use something like this. Instead of exporting a report to Excel, try exporting the query or table the report is based on.

Private Sub YourButton_Click()

Dim XL, XLW, I
Dim rs As DAO.Recordset

    Set XL = CreateObject("Excel.Application.9")
    Set XLW = XL.Workbooks.Open("C:\TEST.xls")        'The Path and Name of your workbook
    XL.Visible = True
    Set rs = CurrentDb.OpenRecordset("tblOutput")     'The table or query to output
    XLW.Sheets("sheet1").Select                       'The sheet Name of your workbook
    I = 2
    
        rs.MoveFirst
        Do Until rs.EOF
        
            XLW.Application.Cells(I, 1).Value = rs.Fields("ID") 'Here's where you put the field Names
            I = I + 1
            XLW.Application.Cells(I, 1).Value = rs.Fields("Field1")
            I = I + 1
            XLW.Application.Cells(I, 1).Value = rs.Fields("Field2")
            I = I + 1
            XLW.Application.Cells(I, 1).Value = rs.Fields("Field3")
            I = I + 1
            XLW.Application.Cells(I, 1).Value = rs.Fields("Field4")
            I = I + 1
            XLW.Application.Cells(I, 1).Value = rs.Fields("Field5")
            I = I + 1
    
        rs.MoveNext
            I = I + 1
        Loop
        rs.Close

    Set rs = Nothing
    Set XL = Nothing
    Set XLW = Nothing

End Sub

IMO

 

[Groundrush]

Hi IMO

Are you following me?


Thanks for that interesting solution

I will let you know how I get on.


cheers

 

[Groundrush]

IMO

For some reason I still a compile error, even when I use your example db and import my tables and qry into it.

The reason why I am trying export into excel in the first place is because I am running reports for someone who is on leave, and they are usually produced on Crystal reports software not Access.

I noticed that it took a long time to complete, because they had to be exported to excel and then the workbook had to be altered to suit the client, i.e. remove blank columns/rows and insert a logo. and then email out, which took him a long time to do.

This was no fault of Crystal reports, just a lack of know how from the user, including my self. (Not being trained on Crystal).

Anyway I have been able to achieve what I needed using acces and export the report to excel and email at the same time using "DoCmd.SendObject"
Which is much quicker than using Crystal.

All works well until I tried to replicate the original layout of the spreadsheet, by using a macro in excel.

I had problems trying to insert company logo and change position of field names.



Cheers

 

[IMO]

For some reason I still a compile error, even when I use your example db and import my tables and qry into it.

I think there will be a reference missing. Open the main form in design view, goto View >> Code......Tools >> References and check that none are missing. The references needed are:

Visual Basic for Applications
Microsoft Access 9.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

IMO

 

[Groundrush]

I have just checked and I don't seem to have

"Microsoft DAO 3.6 Object Library"

any ideas on how to intall that, would I have to download it from Microsoft.?

by the way, will it be ok to use a parameter qry instead of a table?


Cheers

 

[IMO]

Not sure on the parameter query, I think it would just ignore the parameters, but I may be wrong (probably am). Have you got Microsoft DAO 3.51 Object Library on your list? If it is, deselect the 'Missing' 3.6 reference and select 3.51, that should work.

IMO

 

[Groundrush]

Found It

Found It

I missed it the first time, I do have "Microsoft DAO 3.6 Object Library"

It is now selected.........


although I think the Paremeter qry I am using is to complex.

Cheers IMO