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

[Darryl Collins]

The pesky answer is "It depends".

I always try to keep the Excel code in Excel, but that works for me as I will call an Excel template with all the necessary code (in Excel) from Access.  This Excel template has a single starting module (I usually call it "RunMe" or "RunFromAccess".

Then Access can do it's thing and when it is ready you can open the Excel template and run the code in Excel.

There are few advantages to doing this. Firstly you can build all of the Excel side of the code without needing to invoke Access first, this is handy for build and debugging.

It also means you can update the Excel workbook (which in my case is usually for reporting) without having to update the Access FE.

I prefer it that way, but it is horse for courses. There are advantage to building everything on the fly to.

Sample of some Access code to give you the idea.

Set fso = CreateObject("Scripting.FileSystemObject")

        'Copy the Source file into the target path and filename
        fso.copyfile strSourcePathAndName, strTargetPathAndName
    
        Set objExcelApp = CreateObject("Excel.Application")
        objExcelApp.Visible = True
        
        With objExcelApp
            .Workbooks.Open FileName:=strTargetPathAndName
            Set objExcelWB = objExcelApp.ActiveWorkbook
            
            With objExcelWB.Worksheets("Lookups")
                .Range("xlnrDB_Parent").Value = CurrentDb.Name
                .Range("xlnrDetailedReports").Value = gbDetailedReports
                .Range("xlnrSummaryReports").Value = gbSummaryReports
                .Range("xlnrBulkCosts").Value = gbBulkCostRpts
                .Range("xlnrLimitsOnly").Value = gbLimitsOnly
                .Range("xlnrExpSeq").Value = gbExpSeq
                .Range("xlnrSeqRptsOnly").Value = gbSeqOnly
                .Range("xlnrSeqRptsMiningOnly").Value = gbSeqOnlyMining
                .Range("xlnrSeqRptsDSOnly").Value = gbSeqOnlyDS
                .Range("xlnrIncludeStockpile").Value = gbIncSP
            End With
                                           
            DoEvents
            .Run "RunMe" '<----- THIS RUNS THE MS EXCEL CODE NATIVELY FROM EXCEL
            DoEvents

'	Blah blah blah....


Cheers
Darryl.

-----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