Using OUTPUT TO in VBA, then how to modify the XLS from Access?

  • Thread starter Thread starter ThriftyFinanceGirl
  • Start date Start date
T

ThriftyFinanceGirl

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
 
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
Click to expand...
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:D1").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:D1").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:D1").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
 
Hi,

you can work with excel from other office applications using code similar to:

Dim appExcel As Excel.Application
Dim bksBooks As Excel.Workbooks
Dim wkbBook As Excel.Workbook
Dim wksSheet As Excel.Worksheet



'Initiate all objects and variables
Set appExcel = GetObject(, "Excel.Application")
Set bksBooks = appExcel.Workbooks
Set wkbBook = bksBooks.Open("give here the path + file name from the
excel")
Set wksSheet = wkbBook.Sheets(1)
wksSheet.Activate
 
Back
Top