David McAfee
davidmcafee at gmail.com
Mon Mar 2 14:41:26 CST 2009
I have a command button that exports an Stored Procedure as an Excel File:
Private Sub RGAsOver90aysExp_Click()
Dim strmySql As String, strOutPutFile As String
strmySql = "EXEC stpRptRGAsStep190Days"
strOutPutFile = Me.Application.CurrentProject.Path & "\RGAsOver90Days.xls"
DoCmd.OutputTo acOutputStoredProcedure, strmySql, acFormatXLS,
strOutPutFile, True
End Sub
The export works great, but the columns aren't autosized, and has no grid.
I created this Excel macro, which works if I manually create and run
it in the Excel file:
Sub FormatSheet()
Dim a As Range
'Autofit all cells
Cells.Select
'Range("E1").Activate
Cells.EntireColumn.AutoFit
'Get to upper left corner of screen
Range("A1").Select
Set a = Selection.CurrentRegion
'Go Down then Right then Select all
Selection.End(xlDown).Select
Selection.End(xlToRight).Select
Range(Selection, Cells(1)).Select
'Add Grid
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'Deselect Cells
Range("A1").Select
Set a = Selection.CurrentRegion
End Sub
Can I create and run this macro from within my Access
RGAsOver90aysExp_Click() sub? Is there a better way to do what I am
trying to do?
Thanks, David