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