[AccessD] Excel Macro Automation from within Access

Wed Jun 1 11:43:00 CDT 2005

Can't you just design a query that uses the column headings you want to
display?

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Boyd
Sent: Wednesday, June 01, 2005 12:24 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Excel Macro Automation from within Access

Thanks Lambert.  I can't use DoCmd.TransferSpreadsheet b/c I need to send an
Access report to Excel, not a table or select query.  I would use a query,
but I need to keep the report headings intact.  The macro that runs
afterward formats the headings and columns appropriately.

Mark Boyd
I/S Supervisor
McBee Associates, Inc.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Wednesday, June 01, 2005 12:09 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Excel Macro Automation from within Access

You cannot set the excel type with OutputTo, but with
DoCmd.TransferSpreadsheet you can.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Boyd
Sent: Wednesday, June 01, 2005 11:53 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Excel Macro Automation from within Access

Thanks to everyone who helped with this issue.  I greatly appreciate it. The
only issue that I'm now having is the excel file seems to be created with
Excel 5.0/95.  I'm using Office 2003, and the excel file is created with
("DoCmd.OutputTo acOutputReport, strXLName, acFormatXLS, "c:\" & strXLName &
".xls", False").  Why does Access create the file using a lower version of
Office than what is running on the client?  Is there a way to specify the
Office version when exporting the file?

FYI - The macro automation function is below:

Function ExcelMacro(ByVal strFileName As String) On Error GoTo
Err_ExcelMacro
    Dim xlApp         As Excel.Application
    Dim xlWkb         As Excel.Workbook

    If fIsAppRunning("Excel") Then
        Set xlApp = GetObject(, "Excel.Application")
    Else
        Set xlApp = CreateObject("Excel.Application")
    End If

    xlApp.Application.Visible = True
    Set xlWkb = xlApp.Workbooks.Open(strFileName, 0)

    'run Excel macro code here
    '...
    '...
    'end macro code

Exit_ExcelMacro:
On Error Resume Next

    Set xlWkb = Nothing
    Set xlApp = Nothing
    Exit Function

Err_ExcelMacro:
    Select Case Err
        Case 0      'insert Errors you wish to ignore here
            Resume Next
        Case Else   'All other errors will trap
            MsgBox Err.Number & " - " & Err.Description, vbCritical, "Error"
            Resume Exit_ExcelMacro
    End Select
End Function

Thanks again,
Mark Boyd
I/S Supervisor
McBee Associates, Inc.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Thursday, May 26, 2005 5:22 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Excel Macro Automation from within Access

Hi Mark

Here is how (as I learned from Mr. Colby). 
It works very reliably:

Function RunExcelMacros( _
  ByVal strFileName As String, _
  ParamArray avarMacros()) As Boolean

Debug.Print "xl ini", Time

  On Error GoTo Err_RunExcelMacros

  Static xlApp      As Excel.Application
  Dim xlWkb         As Excel.Workbook

  Dim varMacro      As Variant
  Dim booSuccess    As Boolean
  Dim booTerminate  As Boolean

  If Len(strFileName) = 0 Then
    ' Excel shall be closed.
    booTerminate = True
  End If

  If xlApp Is Nothing Then
    If booTerminate = False Then
      Set xlApp = New Excel.Application
    End If
  ElseIf booTerminate = True Then
    xlApp.Quit
    Set xlApp = Nothing
  End If

  If booTerminate = False Then
    Set xlWkb = xlApp.Workbooks.Open(FileName:=strFileName,
UpdateLinks:=0, ReadOnly:=True)

    ' Make Excel visible (for troubleshooting only) or not.
    xlApp.Visible = False 'True

    For Each varMacro In avarMacros()
      If Not Len(varMacro) = 0 Then
  Debug.Print "xl run", Time, varMacro
        booSuccess = xlApp.Run(varMacro)
      End If
    Next varMacro
  Else
    booSuccess = True
  End If

  RunExcelMacros = booSuccess

Exit_RunExcelMacros:

  On Error Resume Next

  If booTerminate = False Then
    xlWkb.Close SaveChanges:=False
    Set xlWkb = Nothing
  End If

Debug.Print "xl end", Time
  Exit Function

Err_RunExcelMacros:
  Select Case Err
    Case 0      'insert Errors you wish to ignore here
      Resume Next
    Case Else   'All other errors will trap
      Beep
      MsgBox "Error: " & Err & ". " & Err.Description, vbCritical +
vbOKOnly, "Error, macro " & varMacro
      Resume Exit_RunExcelMacros
  End Select

End Function

/gustav

>>> MarkBoyd at McBeeAssociates.com 05/25 11:28 pm >>>
I have a need to run Excel macro code from within Access VBA. Basically, the
code logic flows as follows: 1. Export Access report as a new Excel file
(DoCmd.OutputTo acOutputReport, strXLName, acFormatXLS, , True) 2. Run Excel
macro (from within Access VBA) to format the Excel file accordingly.

Does anyone have any sample code and/or resources related to this logic? Any
help is greatly appreciated.

Thanks,
Mark Boyd
I/S Supervisor
McBee Associates, Inc.

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-----------------------------------------
This message and any attachments are intended only for the use of the
individual or entity to which it is addressed and may contain information
that is privileged, confidential, and exempt from disclosure under
applicable law. If the reader of this message is not the intended recipient,
or the employee or agent responsible for delivering the message to the
intended recipient, you are hereby notified that any dissemination,
distribution or copying of this communication is strictly prohibited. If you
have received this communication in error, please notify the sender by
replying to this message, and then delete it from your system.
-------------------------------------------
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-----------------------------------------
This message and any attachments are intended only for the use of the
individual or entity to which it is addressed and may contain information
that is privileged, confidential, and exempt from disclosure under
applicable law. If the reader of this message is not the intended recipient,
or the employee or agent responsible for delivering the message to the
intended recipient, you are hereby notified that any dissemination,
distribution or copying of this communication is strictly prohibited. If you
have received this communication in error, please notify the sender by
replying to this message, and then delete it from your system.
-------------------------------------------
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com