[AccessD] Excel Macro Automation from within Access

Heenan, Lambert Lambert.Heenan at AIG.com
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



More information about the AccessD mailing list