[AccessD] More Access to Excel Help Needed

Are you closing all the Excel objects you're setting and then setting them =
Nothing?

Rocky
 




 	
	

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

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.487 / Virus Database: 269.13.22/1013 - Release Date: 9/17/2007
1:29 PM