[AccessD] More Access to Excel Help Needed

I recently worked on an Access to Excel procedure, and there are three
things I can see that might help.  

First, you will want to know if your user currently has Excel already open:

    '-- Is Excel already running?
    If IsAppRunning("Excel") = True Then
        Set objExcel = GetObject(, "Excel.Application")
        blnXLRunning = True
    Else
        Set objExcel = CreateObject("Excel.Application")
        blnXLRunning = False
    End If

I used late binding because I don't know which version of Excel a user might
have.  Also, the procedure IsAppRunning is from Leban's web site.

Next, you need to specifically save & close the workbook and instance of
Excel if your code opened a new one.

    '-- Save & Quit
    objWorkbook.Save
    objWorkbook.Close
    If blnXLRunning = False Then
        objExcel.Quit
    End If

    Set objWorkbook = Nothing
    Set objExcel = Nothing

Then, open the excel spreadsheet using ShellExecute:

    ShellExecute 0, "Open", stgFullPath, vbNullString, CurDir$,
vbMaximizedFocus

I am NOT experienced with running Excel in Access, but perhaps this will
help.

Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of ewaldt at gdls.com
Sent: Tuesday, September 18, 2007 7:03 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] More Access to Excel Help Needed

I'm thrilled at how well a database I'm working is doing (thanks in part 
to people here) except for one area: Excel doesn't want to go leave when 
it's told to. My database opens Excel, creates a workbook, exports data 
from several runs of a query (each to its own worksheet,), copies a code 
module from Access to Excel (it's actually Excel code just being stored in 
Access for this purpose), and then tells Excel to run that code. 

One of two things happens:
        1. It runs perfectly, but Excel does not completely shut down 
(i.e., it still shows in the Task Manager) when closed.
        2. It does NOT run completely, but gives error #462: "The remote 
server machine does not exist or is unavailable." In this case, the Excel 
workbook                has been created, the data have been exported, but 
it's unable to transfer the code module.

Now, I've also noticed that my Personal Macro Workbook is not present in 
either case. I don't know if this is normal in this situation or not; just 
thought I'd mention it in case it offers a hint to one of you 
knowledgeable individuals.

In case #2, BTW, it dies at the "Set NewModule" statement in CopyModule.

Any help with these would be greatly appreciated as always.


Thomas F. Ewald
Stryker Mass Properties
General Dynamics Land Systems


'----Code Follows----

Public Sub CreateExcelWB()
    Dim varChoice As Variant
    Dim intToggle As Integer
    Dim rst As ADODB.Recordset
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim intCount As Integer
    intToggle = 0

    On Error GoTo HandleErr
 
    Set xlApp = New Excel.Application       'Launch Excel
    Set xlBook = xlApp.Workbooks.Add        'Create workbook
 
    Set rst = New ADODB.Recordset
    For Each varChoice In 
Forms![frmexportcriteria].[lboVariants].ItemsSelected
        Forms![frmexportcriteria]![txtVariant] = _
            Forms![frmexportcriteria].[lboVariants].ItemData(varChoice)
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "qryGen"
        DoCmd.SetWarnings True
        xlBook.Sheets.Add
        Set xlSheet = xlBook.ActiveSheet
        xlSheet.Name = _
            "Data_" & 
Forms![frmexportcriteria]![lboVariants].ItemData(varChoice)
        rst.Open _
            Source:="tbl02", _
            ActiveConnection:=CurrentProject.Connection
 
    With xlSheet
        For intCount = 1 To 3
            With .Cells(1, intCount)
                .Value = rst.Fields(intCount - 1).Name
                .Font.Bold = True
            End With
        Next intCount
        .Range("A2").CopyFromRecordset rst
    End With
        rst.Close
    Next
 
    CopyModule (xlBook.Name)
 
    xlBook.Application.Run "RunImpacts"
 
    xlApp.Visible = True
ExitHere:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set xlSheet = Nothing
 
    Set xlApp = Nothing
    Set xlBook = Nothing
    Exit Sub

HandleErr:
    MsgBox ERR & ": " & ERR.Description, , "Error"
    xlApp.Visible = True
    Resume ExitHere
End Sub

'---------------------------------------------------
Sub CopyModule(strXL As String)
'Copy CR_Impacts module to Excel workbook

    Dim CodeLines As String
    Dim ModuleTocopy As vbcomponent, NewModule As vbcomponent
 
    Set ModuleTocopy = _
        Application.VBE.ActiveVBProject.VBComponents("CR_Impacts")
    Set NewModule = _
        Workbooks(strXL).VBProject.VBComponents.Add(vbext_ct_StdModule)
    CodeLines = ModuleTocopy.CodeModule.Lines _
            (1, ModuleTocopy.CodeModule.CountOfLines)
 
    NewModule.CodeModule.AddFromString CodeLines
 
    NewModule.Name = ModuleTocopy.Name

End Sub



This is an e-mail from General Dynamics Land Systems. It is for the intended
recipient only and may contain confidential and privileged information.  No
one else may read, print, store, copy, forward or act in reliance on it or
its attachments.  If you are not the intended recipient, please return this
message to the sender and delete the message and any attachments from your
computer. Your cooperation is appreciated.

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