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

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



More information about the AccessD mailing list