[AccessD] How do I call a macro in an XLS file exported via Access?

Got it working, for the archives:


Private Sub RGAsOver90aysExp_Click()
    Dim strmySql As String, strOutPutFile As String, objXL As Object,
row As Integer
    strmySql = "EXEC stpRptRGAsStep190Days"
    strOutPutFile = Me.Application.CurrentProject.Path & "\RGAsOver90Days.xls"
    DoCmd.OutputTo acOutputStoredProcedure, strmySql, acFormatXLS,
strOutPutFile, False

    'Now, format the sheet
    Set objXL = CreateObject("Excel.Application")
    With objXL.Application
        .Visible = True
        .Workbooks.Open strOutPutFile

    objXL.Cells.EntireColumn.AutoFit

    row = objXL.CountA(objXL.Worksheets("EXEC
stpRptRGAsStep190Days").Range("A:A"))
    With objXL.Worksheets("EXEC
stpRptRGAsStep190Days").Range(.Cells(1, 1), .Cells(row, 9))
        'LineStyle = 1 = xlContinuous
        .Borders(7).LineStyle = 1 'xlEdgeLeft
        .Borders(8).LineStyle = 1 'xlEdgeTop
        .Borders(9).LineStyle = 1 'xlEdgeBottom
        .Borders(10).LineStyle = 1 'xlEdgeRight
        .Borders(11).LineStyle = 1 'xlInsideVertical
        .Borders(12).LineStyle = 1 'xlInsideHorizontal
    End With
End With
Set objXL = Nothing

End Sub



On Mon, Mar 2, 2009 at 12:41 PM, David McAfee <davidmcafee at gmail.com> wrote:
> 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
>