Outputting Report to Excel and keeping the formatting

[Triacona]

Please help...

I have reports I need to output to excel for analysis, and addition of other data.
The way the user outputs the report to excel is a button (Output Report to Excel)
Which has the code:

Private Sub DcOutPutQueryToExcel_Click()
On Error GoTo Err_DcOutPutQueryToExcel_Click

Dim stDocName As String
    stDocName = ListDc.Column(2)
          
    If Not QueryExists(stDocName) Then
        MsgBox stDocName & " query doesn't exist, Output the REPORT!", vbExclamation, "Output The REPORT!!!"
    ElseIf Not Me.txtEndDate.Enabled = True Then
        DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, stDocName & ".xls", True
    ElseIf IsNull(Me.txtStartDate And Me.txtEndDate) Then
        DoCmd.RunMacro "MsgBoxNoDate"
    Else: DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, stDocName & ".xls", True
    End If
        
Exit_DcOutPutQueryToExcel_Click:
    Exit Sub

Err_DcOutPutQueryToExcel_Click:
    MsgBox Err.Description
    Resume Exit_DcOutPutQueryToExcel_Click
    
End Sub

The query has the same information, but not formatted correctly, the report has Sorting and grouping headers, which group the types of Modules and Document types respectively.
So if the user outputs the query they still have to sit for hours grouping it into a manageable format, with groups.

My thought was to make colums in the report with tags or some way of defining it as a column excel would recognise and format as such.
Any help would be greatly appreciated.

Thank you,

Kind regards

Triacona

 

[dhookom]

If you want your Excel formatted much like the report, you could try the not-so-impressive "Export To Excel" from the report or use automation. There are also nice solutions for pulling data from Access while in Excel.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

...and to piggy-back on Duane's last response and endorse his suppestion to pull data in Excel, I would advise against exporting a report to Excel, as there are often VISUAL REPORT GROUPING data techniques that makes post processing in Excel VERY DIFFICULT AND FRUSTRATING!!!

Rather query the data directly in Excel and format the result in Excel. You can get help with MS Query in forum68.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

Skip,
I thought (hoped) you might jump in on this thread.

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Gents,

Thanks very much for all the help

The problem is that the report pulls data from several queries and it would take hours to format in excel...

What is Excel Automation?
I have a button that Outputs the report to Excel and I have also tried Office Links, Analyse with Excel. Tools->Office Links->Analyse it with Microsoft Excel.

I have tried other methods...I.e. exporting to word and the coping that and pasting to Excel.
This works but is slightly long winded, I have multiple headings, rather than having a heading for a group of 3 that go under it.
i.e.
Industry Industry Industry
Agric WareD IndTot
1 1 2
The headings have to be short or word puts them on 2 lines...
This way it copies the best into Excel.
So the user will only have to move and merge a few headings rather that hours formatting an Excel spread sheet with the data pulled off.

Is there no way to tell Excel what the headings are?

Thanks for all your help!

Thank you,

Kind regards

Triacona

 

[SkipVought]

and it would take hours to format in excel...

If that is the case, it is a ONE TIME TASK!

The headings have to be short or word puts them on 2 lines...

What is the 2 lines thing? That does not work in a TABLE, does it?

You want to get this data into Excel to use like a table, don't you?

Because if you ONLY want to slap a FORMATTED REPORT into Excel, you will get MIINIMAL FACILITY from Excel for doing any sort of post processing, like filtering, sorting, analysis, etc!!!

Most REPORTS that get put into Excel are USELESS. They might as well be on paper or in a .pdf


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

I agree with Skip. However you asked about Excel Automation. Check Microsoft Access Sample Code. I typically use the macro recorder in Excel to write my first draft of code and then copy/paste it into Access where I can tweak it. You could also just write all your code in Excel if desired.

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Hi Skip,
The table does work, as it is exported to Word first and then Copy and pasted into Excel.

This is how it appears in the Word output for the report:
Industry Industry Industry
Agric WareD IndTot
1 1 2

I then copy that and paste it into Excel.

And yes the table is usable and formulas and lists can be applied.
What I am looking for is to skip that part and format the report directly into Excel...maybe using tags for column headers, or defining the column headers or creating an excel template that imports the data???

Thanks for your help.

Thank you,

Kind regards

Triacona

 

[SkipVought]

creating an excel template that imports the data

THAT is EXACTLY what was meant.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

If the user supplies UNIQUE DATA that does not persist, and therfore, cannot be reproduced at another time in Excel, you have atleast TWO choices.

The first has been posited by Duane and his Sample Code link.

The second would be to add a process in Access to SAVE these parameters with a timestamp in a table. Having this additional data might make it possible to PULL the required data while in Excel.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]