[AccessD] More Access to Excel Help Needed

Hi Thomas

First, Sheet is not the same as WorkSheet. Use WorkSheet.
Then, as you have the application object, this:

    xlBook.Application.Run "RunImpacts"

could be written as:

    xlApp.Run "RunImpacts"

Also, you must close all open worksheets before you terminate these.
Finally, close the app before terminating it:

    xlApp.Quit
    Set xlApp = Nothing

/gustav


>>> ewaldt at gdls.com 18-09-2007 14:02 >>>
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