I don't know how much help this will be since this is specific to my query/report.
So I ran the DoCmd.Output command, then made a copy of the spreadsheet.
In the original spreadsheet, I recorded VBA code as I modified the worksheet like I needed it to look.
Then I used that code (what Excel calls a "Macro") and wrote the following subroutine.
I deleted the original workbook (because it had the modifications), then created a copy of the original copy, deleting the "Copy"s so it was named like the original.
Example:
original WK name = "Book1.xlsx"
Copy = "Copy of Book1.xlsx"
Delete "Book1.xlsx"
make copy - "Copy of Copy of Book1.xlsx"
rename to "Book1.xlsx"
Now I write the code to do the edits to the SS using the code in the SS as a reference.
I can single step through the subroutine to make sure the edits happen correctly and in the correct order.
If I have to make changes to the code, I delete the SS, make another copy of the copy and rename it.
Step through the code again.
Repeat until the edits are correct.
I have a "Reports" form with a list box of the reports I can print or export. I select a report and click a
"Print" button to print a hard copy or an
"Export" button to export the report query as an Excel SS, which then calls the workbook edit code.
Your code will be different from mine.... you might not need all the parameters I have.
------------
This how I call the sub:
Code:
<snip>
'output query to Excel
DoCmd.OutputTo acOutputQuery, "EQ_StatementMonthlySummary", acFormatXLSX, strSaveFileName, False
'modify the Excel workbook
Call EditVendorWkSht(strSaveFileName, "Statement Monthly Summary", Me.cboMonth & " " & Me.cboYear & " Hours")
<snip>
This is the sub that modifies the Excel workbook for the specific report.
Code:
Sub EditVendorWkSht(pWkshtPathName As String, pReportName As String, pMthYr As String)
'pWkshtPathName - the fully qualified path and file name (strSaveFileName)
'pReportName - name of the report that was output (this is for report '"Statement Monthly Summary"')
'pMthYr - a string of the month, year and text string (the month & year of the report)
Dim xlx As Object 'Excel
Dim xlw As Object 'workbook
Dim xls As Object 'worksheet
Dim sRow As Integer
Dim eRow As Integer
Dim RowDiff As Integer
Dim blnEXCEL As Boolean
blnEXCEL = False
' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
' Change True to False if you do NOT want the workbook to be visible
' when the code is running
' xlx.Visible = True
xlx.Visible = False
' the EXCEL file into which you will write the data
Set xlw = xlx.Workbooks.Open(pWkshtPathName)
' Select a worksheet
' (note that the worksheet must already be in the EXCEL file)
Set xls = xlw.Worksheets(1)
'**** Start editing the worksheet *****
'ensure correct report
If pReportName = "Statement Monthly Details" Then
'add two rows at the top
xls.Rows("1").EntireRow.Insert
xls.Rows("1").EntireRow.Insert
xls.Range("A1").Select
xls.Range("A1").FormulaR1C1 = pReportName 'report name
xls.Range("A2").Select
xls.Range("A2").FormulaR1C1 = pMthYr 'the date
With xls
.Range("F4").Select 'first row
sRow = ActiveCell.Row
ActiveWindow.FreezePanes = True
.Range("H4").Select
'find the last row and move down 1 row
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
eRow = ActiveCell.Row
RowDiff = eRow - sRow
'select columns H through P
.Range(Cells(eRow, 8), Cells(eRow, 25)).Select
'set borders
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
'remove top. left and right borders
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'add totals formulas
Selection.FormulaR1C1 = "=SUM(R[-" & RowDiff & "]C:R[-1]C)"
.Range("A2").Select
End With
End If
'**** End editing the worksheet *****
' Close the EXCEL file while saving the file, and clean up the EXCEL objects
Set xls = Nothing
xlw.Close True ' close the EXCEL file and save the new data
DoEvents
Set xlw = Nothing
Set xlx = Nothing
If blnEXCEL = True Then
xlx.Quit
End If
End Sub