[AccessD] Is it better to call Excel Macro from access, or manipulate from Access?

[David McAfee]

I have an Excel file that I copy contents from five different queries into
five different worksheets in the same Excel file/workbook.

The column headers for the worksheets have certain formatting and word
wrapping, so I figured it was easier to take an existing workbook, rename
it as a template then paste (through access VBA) the query contents into
each worksheet.

One worksheet, a summary sheet, is always 30 rows and 10 columns of data.
Row 1 is the fancy, word wrapped and formatted row.
Row 30 is the sum of data (rows 2-29 for columns B-J).

It is easier to insert a blank row in row 2, paste my (28 rows of data and
one row of header) data in cell A2, format the data (grid, number format)
then delete row 2, leaving the formatted header row.

I ran the Excel Macro recorder to see how to do this in Excel,
but I'd like to do it all from within Access.

Any thoughts which is easier to do?
Should I save the first and second sections as separate macros
and run the macros from Access?

Or do I manipulate the excel file with (early, or) late binding through VBA?

The reason I am trying to do this all from within Access is that I am
trying to automate
a bunch of manual processes to hand them off to someone else to run.



Here are my macro steps::

'Insert Blank Row, to deal with header row
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").Select

  '***************Access Section *********
   'Run Query in MS Access
    'Select All data using Ctrl+A
    'Copy Query Data to clipboard
  '**************************************

    'Paste into cell A2:
    ActiveSheet.Paste

    'Format Active Cells with grid:
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

    'Add comma to number format:
    Selection.Style = "Comma"

    'Get rid of cents:
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(*
""-""??_);_(@_)"
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"

    'Delete the 2nd Header row:
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp