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

[Darryl Collins]

David,

Whilst on this, I strongly recommend you get rid of all the "Activesheet" and "Select" from you code.  Be explicit, otherwise you will come undone, and badly too, at some point.

I have clean up the code below (quickly and it is a bit 'aircode' but you get the idea.

Dim xlshtTarget as Excel.Worksheet

Application.screenupdating = false

Set xlshtTarget = Sheet1  '(using sheet1 as the sheet CODE name, not the sheet name, although you can use either)
xlshtTarget. Rows("2:2").Insert Shift:=xlDown

' HERE YOU WANT TO USE 'CopyFromRecordset' TO AUTOMATE THIS.
  '***************Access Section *********
   'Run Query in MS Access
    'Select All data using Ctrl+A
    'Copy Query Data to clipboard
  '**************************************

    'Paste into cell A2:
   xlshtTarget.Range("A2").Paste
 application.cutcopymode = false 'empties the clipboard


    '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);_(* ""-""??_);_(@_)"

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






-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Friday, 15 May 2015 8:39 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Is it better to call Excel Macro from access, or manipulate from Access?

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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com