This is what I came up with after days of trial and error. Created/recorded the code in Excel to see what formatting looked like in Excel, then adapted the Excel code to Access. Ken Snell's site was a BIG help.
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm
Saves the data: (EQ_ = Export query)
Code:
strSaveFileName = sPath & "\Statement Monthly Summary " & Me.cboMonth & " " & Me.cboYear & " Hours.xlsx"
DoCmd.OutputTo acOutputQuery, "EQ_StatementMonthlySummary", acFormatXLSX, strSaveFileName, False
Then calls a sub to do the editing/formatting:
Code:
Call EditVendorWkSht(strSaveFileName, "Statement Monthly Summary", Me.cboMonth & " " & Me.cboYear & " Hours")
The code to edit/format the worksheet:
Code:
Sub EditVendorWkSht(pWkshtPathName As String, pVendor As String, pMthYr As String)
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim sRow As Integer, eRow As Integer, 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
' Replace C:\Filename.xls with the actual path and filename
' of the EXCEL file into which you will write the data
Set xlw = xlx.Workbooks.Open(pWkshtPathName)
'get worksheet name
' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
' (note that the worksheet must already be in the EXCEL file)
Set xls = xlw.Worksheets(1)
' Replace A1 with the cell reference into which the first data value
' is to be written
Set xlc = xls.Range("A1") ' this is the first cell into which data go
'add two rows at the top
xls.Rows("1").EntireRow.Insert
xls.Rows("1").EntireRow.Insert
xls.Range("A1").Select
xls.Range("A1").FormulaR1C1 = pVendor
xls.Range("A2").Select
xls.Range("A2").FormulaR1C1 = pMthYr
If pVendor = "Statement Monthly Details" Then
With xls
.Range("F4").Select
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
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'add formulas
Selection.FormulaR1C1 = "=SUM(R[-" & RowDiff & "]C:R[-1]C)"
.Range("A4").Select
.Range("A2").Select
End With
End If
' Close the EXCEL file while saving the file, and clean up the EXCEL objects
Set xlc = Nothing
Set xls = Nothing
xlw.Close True ' close the EXCEL file and save the new data
DoEvents
Set xlw = Nothing
If blnEXCEL = True Then
xlx.Quit
End If
Set xlx = Nothing
End Sub