UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> The Export To Excel File Does Not Yield The Same Result As Export To .pdf File, Access 2016    
 
   
Lin100
post Dec 11 2017, 10:31 AM
Post#1



Posts: 638
Joined: 20-August 06



The export to Excel file does not yield the same result as export to .PDF file.

Report Name: Export_To_Excel_Summary
RecordSource: Export_To_Excel_Summary (this is a table)
The report has a text box named Grand_Percent_Productivity with calculated value.
ControlSource=[Grand_Total_Production_Minutes]/[Grand_Total_Minutes_Worked]

When I used the command DoCmd.OutputTo acOutputReport to a PDF file, everything is correct, but
when I used the same command to export to an Excel format, I the Excel file have 3 problems:

1) the text is very light
2) the label text is missing
3) and the calculated field is missing
Total Percent Productivity = Total Production Minutes / Total Minutes Worked (E10 = C10 / D10)

The attachment has two pictures to illustrate what I have just stated.

Question: How do I make the output to Excel format to be like the PDF format ?

/////////////////////////////////////////////////////////////////////////

Private Sub Export_To_A_PDF_File_Click()
'Export the table Export_To_Excel_Summary

Dim Sheet_Name As String
Sheet_Name = "Employee Productivity"

DoCmd.OutputTo acOutputReport, "Export_To_Excel_Summary", acFormatPDF, Me.Export_Path & Me.Export_File_Name.Caption & ".PDF"
End Sub


Private Sub Export_To_An_Excel_File_Click()
'Export the table Export_To_Excel_Summary

Dim Sheet_Name As String
Sheet_Name = "Employee Productivity"

DoCmd.OutputTo acOutputReport, "Export_To_Excel_Summary", acFormatXLS, Me.Export_Path & Me.Export_File_Name.Caption & ".XLS
End Sub



Attached File(s)
Attached File  Excel_And_PDF_Export.jpg ( 190.45K )Number of downloads: 1
Attached File  Report_Named___Export_To_Excel_Summary.jpg ( 251.2K )Number of downloads: 0
 
Go to the top of the page
 
doctor9
post Dec 11 2017, 10:39 AM
Post#2


UtterAccess Editor
Posts: 18,253
Joined: 29-March 05
From: Wisconsin


Lin100,

The Export to Excel method basically creates a comma-separated-values file, and opens that in Excel. You can have more control over the formatting by creating an Excel file, and using the Data->From Access option from the Ribbon. Once you have the raw data in your Excel file, you can format it, and save the file.

As far as the calculated value is concerned, it would be better to put the calculation in the report's recordsource as an expression in a query, and bind the textbox to the expression. The query would be something along these lines:

Select *, [Grand_Total_Production_Minutes]/[Grand_Total_Minutes_Worked] as GrandPercentProductivity FROM Export_To_Excel_Summary

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
Lin100
post Dec 11 2017, 11:09 AM
Post#3



Posts: 638
Joined: 20-August 06



Hi doctor9 and thank you for your help.

You said "As far as the calculated value is concerned, it would be better to put the calculation in the report's recordsource as an expression in a query, and bind the textbox to the expression. The query would be something along these lines:
Select *, [Grand_Total_Production_Minutes]/[Grand_Total_Minutes_Worked] as GrandPercentProductivity FROM Export_To_Excel_Summary"

The query won't work because of two reasons:
1) the table Export_To_Excel_Summary already have the field calculated field Percent_Productivity. This
value is calculated for each day for that employee name.
Employee Name Date_Of_Record Total_Production_Minutes Total_Minutes_Worked Percent_Productivity
John Doe 12/01/2017 470.6 540 87%
John Doe 12/04/2017 558.82 489 114%
John Doe 12/05/2017 514 498 103%
John Doe 12/06/2017 448 480 93%
John Doe 12/07/2017 483.08 489 99%
John Doe 12/08/2017 650.88 585 111%
John Doe 12/11/2017 78 0

2) The Grand Total is an Employee_Name grouping in the Employee_Name Footer, which is located in the report.
This grouping will occurred after all records have been listed for that employee for all of the dates.


Attached File(s)
Attached File  Export_To_Excel_Summary__Table.jpg ( 93.84K )Number of downloads: 2
 
Go to the top of the page
 
doctor9
post Dec 11 2017, 11:15 AM
Post#4


UtterAccess Editor
Posts: 18,253
Joined: 29-March 05
From: Wisconsin


Lin100,

You may need to use VBA to organize the raw data into groups in this case. Excel is able to sum a column of numbers. If formatting the Excel data to look like the PDF is really your priority, I think this may be your best bet.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th April 2018 - 07:47 PM