Export query to Excel with format

keith267

New member
Local time
Yesterday, 20:41
Joined
Nov 24, 2016
Messages
6
Hello, I've created a query (QRY_Neg-Totals) which total a number of table columns (Proposals-Table). One column is all the elements of the proposal (e.g. Labor, Materials, TotalCost, Profit, TotalPrice, etc) another column is what was negotiated (e.g. Labor, Materials, TotalCost, Profit, TotalPrice, etc). I need to export to Excel a report with column heading of "Proposed"and "Negotiated" (I have multiple additional columns needed but for simplicity only listed two) and with cell A2="Labor", A3="Materials", A4="TotalCost", B2="ProposedLabor", C2="NegotiatedLabor", etc...

How do I SELECT the query for this report and how would I assign each cell with the correct values. I've looked everywhere but have found nothing. I would have created a separate table for this but the table won't allow me to use the formulas I needed to derive my totals (multiple Sum IIf statements).
 
You don't export a report, you export a query using Transferspreadsheet command.
Make the query,with all the headings and formulas.
You shouldn't need IIF statements. You can ,but joining a lookup table will solve this need for IIFs.

Once in excel ,run a macro to format the columns.

Once you have these ,you can run the format macro in access after it exports the data.
 
Thanks, yes that's what I meant export the query so I can create an excel spreadsheet (report). I do need the IIF statements because when I query the data I need to extract only the data that meets specific criteria. But I will also need to, in other reports sum all the data. three of my query expressions below which would all be in the same row but separate columns.

OrgPropMaterial: Sum(IIf([Proposals-Table]![ServiceRequested]="Negotiation",[OriginalProposedMaterial],0))

NegMaterial: Sum(IIf([Proposals-Table]![ServiceRequested]="Negotiation",[NegotiatedMaterial],0))

DeltaOrgProp-NegotiatedODC: Sum([Proposals-Table]![DeltaOrgProp-NegotiatedODC])


The main question is how to I designate the cell each expression will populate?
 
Last edited:
The name of the query field is the header in the excel.
Change the query label if you need a different name.
 
Not sure if I'm explaining myself correctly. But it sounds as though your explaining the process to simply export the query as if I went into the database went to the External Data tab and selected Export Excel. this is not the format I want. as explained above I want to direct Excel where to put each query expression. I did find a post on another site which used the below code. I would like to do something like that but do not know how to write the SELECT statement?

Public Sub CreateInspectionReport(lngVehicleID As Long)
'Created by Helen Feddema 11-Jun-2010
'Last modified by Helen Feddema 9-Oct-2011
On Error GoTo ErrorHandler
Dim appExcel As New Excel.Application

strRecordSource = "tblVehicles"
strQuery = "qrySelectedVehicle"
strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
& "[VehicleID] = " & lngVehicleID & ";"
Debug.Print "SQL for " & strQuery & ": " & strSQL
lngCount = CreateAndTestQuery(strQuery, strSQL)
Debug.Print "No. of items found: " & lngCount
If lngCount = 0 Then
strPrompt = "No records found; canceling"
strTitle = "Canceling"
MsgBox strPrompt, vbOKOnly + vbCritical, strTitle
GoTo ErrorHandlerExit
Else
Set rst = CurrentDb.OpenRecordset(strQuery)
End If

'Create new workbook from template
strDocsPath = GetProperty("DocumentsPath", "")
strTemplatesPath = GetProperty("TemplatesPath", "")
strTemplate = strTemplatesPath & "\Northwind Inspection Report.xltm"
Debug.Print "Template: " & strTemplate
Set wkb = appExcel.Workbooks.Add(template:=strTemplate)
Set sht = wkb.Sheets(1)
appExcel.Visible = True

'Write data for selected vehicle to cells of worksheet
rst.Edit
sht.Range("A5").Value = rst![Appraiser]
sht.Range("C5").Value = rst![ClaimNumber]
sht.Range("E5").Value = Format(rst![ClaimDate], "mmm d, yyyy")
sht.Range("A7").Value = rst![Inspector]
sht.Range("B7").Value = rst![Location]
sht.Range("D7").Value = rst![YearMakeModel]
sht.Range("G7").Value = rst![AppraiserRate]
sht.Range("H7").Value = rst![InspectorRate]
sht.Range("A9").Value = Format(rst![InspectionDate], "dd-mmm-yyyy")
sht.Range("B9").Value = Format(rst![CompDate], "dd-mmm-yyyy")
sht.Range("C9").Value = rst![VIN]
sht.Range("D9").Value = rst![Mileage]
sht.Range("E9").Value = rst![Plate]
sht.Range("F9").Value = rst![State]
sht.Range("G9").Value = rst![LaborTax]
sht.Range("H9").Value = rst![PartsTax]
rst![ReportSent] = Date
rst.Update

'Protect and save filled-in workbook
sht.Protect DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
sht.EnableSelection = xlUnlockedCells
sht.Range("A13").Select

strSaveName = strDocsPath & "\Preliminary Vehicle Inspection Report for " _
& rst![YearMakeModel] & ".xlsm"
Debug.Print "Save name: " & strSaveName
wkb.SaveAs FileName:=strSaveName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled

strTitle = "Export successful"
strPrompt = strSaveName & " created"
MsgBox prompt:=strPrompt, _
Buttons:=vbInformation + vbOKOnly, _
Title:=strTitle

ErrorHandlerExit:
Set appExcel = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number _
& " in CreateInspectionReport procedure; " _
& "Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
 

Users who are viewing this thread

Back
Top Bottom