[AccessD] More Access to Excel Help Needed

Two thoughts:
 If you are using ActiveCell, ActiveWorkbook, etc in your code and
another workbook is open and being used while your code is running the
code may grab that as the currently active workbook. This obviously will
confuse the code completely since the workbook it has grabbed won't be
what it expected. Second, I have found that ALL object references (not
just those using excel objects) have to be properly set to nothing or
Excel will sometimes hang around. Don't rely on Access to do proper
garbage collection. 

Jim Hale

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

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.