creating an Excel spreadsheet from a report

[robojeff]

I have created a report in Access and I was wondering if instead of printing or displaying the report with ACpreview, whether this report could be output into an Excel spreadsheet instead with all of its text field's formats intact.

Any ideas if this can be done and how ?

thanks

 

[SkipVought]

hi,

Why would you want to inflict that kind of pain and suffereing on your poor, dear, defenceless, unassuming, innocent Excel users. Have you no heart? Where is your compassion?

Export to Excel, the result of your query (table) rather than the report. Exported reports in Excel are often difficult to use as Excel has been designed to function.

To paraphrase Patrick Henry,

Is life so dear, or peace so sweet, as to be purchased at the price of a report exported to Excel. Forbid it, I stand agog! I know not what course others may take, but as for me, give me a PROPER TABLE, or give me Def Leppard!

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

 

[robojeff]

I am attempting to match the format of an excel spreadsheet that is currently manually created for this use, so I am not inflicting any
new pain and suffering on anyone, as this is already in the format they want it...

I am just looking for an easier way to do this instead of having to
manually automate all of the components of the spreadsheet from within Access...

 

[malibu65k]

It's possible, I do it for one of my reports because a costumer wants it in Excel and not an Access report. They want to use the data in the spreadsheet for their own use like "Cut & Paste". I don't know why and I don't ask, It's none of my business.

Anyway...

I create a spreadsheet template. Then I run code within Access to send the data to the template then save the template as another name and close the template without saving it.

Example of the code in Access...

Private Sub ExportToExcel(pLink As Integer)
   On Error GoTo Err_Routine


   Dim appExcel As Excel.Application
   Dim wbk As Excel.Workbook
   Dim wks As Excel.Worksheet
   Dim sTemplate As String
   Dim sTempFile As String
   Dim sOutput As String
   Const cTabTwo As Byte = 1
   Const cStartRow As Byte = 6
   Const cStartColumn As Byte = 1
   sTemplate = CurrentProject.Path & "\Mag moto report- VMMT-204 Template.xls"
   sOutput = CurrentProject.Path & "\Monthly Wing Totals for VMMT-204 " & Format(Date, "ddmmmyyyy") & ".xls"
   If Dir(sOutput) <> "" Then Kill sOutput
   FileCopy sTemplate, sOutput
   
   ' Create the Excel Applicaiton, Workbook and Worksheet and Database object
   Set appExcel = Excel.Application
   Set wbk = appExcel.Workbooks.Open(sOutput)
   Set wks = appExcel.Worksheets(cTabTwo)
   
wks.Cells(5, "D") = Me.txtTotalOnHwy
wks.Cells(6, "D") = Me.txtSportBRCCount
wks.Cells(7, "D") = Me.txtSportSRCCount
wks.Cells(9, "D") = Me.txtCruiserBRCCount
wks.Cells(10, "D") = Me.txtCruiserERCCount
wks.Cells(12, "D") = Me.txtSportNeedsBRCCount + Me.txtCruiserNeedsBRCCount
wks.Cells(13, "D") = Me.txtSportNeedsSRCCount
wks.Cells(14, "D") = Me.txtCruiserNeedsERCCount
wks.Cells(16, "D") = Me.txtRiderCoach
wks.Cells(18, "D") = Me.txtATV


   wbk.Save
   wbk.Close
   
If pLink = 1 Then
OpenExcelFile (sOutput)
ElseIf pLink = 2 Then
SendSpreadSheet (sOutput)
ElseIf pLink = 3 Then
DisplayEmailToSend (sOutput)
End If

exit_Here:
   ' Cleanup all objects  (resume next on errors)
   On Error Resume Next
   Set wks = Nothing
   Set wbk = Nothing
   Set appExcel = Nothing
   Set db = Nothing
   DoCmd.Hourglass False
   Exit Sub
   
Err_Routine:
   ExportRequest = Err.Description
   Resume exit_Here

End Sub

The template was created by the customer, I just place the data in the area designated for my department. All formatting stays as is.

I hope this is what you were looking to do.