ThriftyFinanceGirl said:
I'm using the simple "OUTPUTTO" command to send a query over to Excel,
however we will have many users and we would like to programmatically add the
header rows, date printed, turn on the auto filter and freeze panes, all of
which I can do within a Macro in Excel, but how do I call that macro to run
from Access and How to run it on the file that they just created?
Current Code:
DoCmd.OutputTo acOutputQuery, "MainRptWUser", acFormatXLS, , True
Here is the Macro Code in Excel:
Sub SetHeader()
'
' SetHeader Macro
' Macro recorded 4/1/2010 by Kari Jarrett
'
'
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Range("B1
1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("B1
1").Select
ActiveCell.FormulaR1C1 = "JDE Upgrade Planner - REPORTS Listing by DEPT"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Date Run:"
Range("D2").Select
ActiveCell.FormulaR1C1 = "4/1/2010"
Range("C2").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B1
1").Select
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
Range("A5").Select
ActiveWindow.FreezePanes = True
Range("A4:K4").Select
Selection.AutoFilter
Range("A5").Select
End Sub