Excel connection

[cll]

Hi,
Having written vba codes for AutoCad 2005 and Inventor 9 that connects to Excel and put some title block entries into same workbook, some strange problems occur.

Although running fine separately in each application, I cannot run in the other. That is: If I open AutoCad and Inventor, run the AutoCad code (works fine), then a following run of the Inventor code fails - and vise versa (I have tried every combination of open/run). It is as the first takes over Excel and won't let the other in.
I use the ExcelLink method of connecting and closing Excel.10 for every run.

Help is needed...

Best regards
Claus

[Ed Jobe]

Can you show the code you are using to connect/disconnect?

[mtuersley]

Can you show the code you are using to connect/disconnect?

Thats the problem If he is using the Autodesk ExcelLink as an example, it doesn't clear out any of the objects it substantiates.

Set all your Excel objects to Nothing beginning with the most insignificant and work your way back out through the worksheet object then finally the app object. Failing to clear sub objects of the app, will not free up the app.

Read my article on data access [www.cadalyst.com] as you are better off using an ODBC connection to the XLS file than opening the entire Excel application.

[cll]

Thanks both,

The connecting code is the following:

-------------------------------------------------------------------------

Public Sub ConnectExcel(X As Variant)
    ' X is only used to suppress this routine from the Run Macro Dialog

    ' Determine the versions Excel that we have available on this machine,
    ' and set the public variables accordingly
    
    On Error Resume Next
       
    ExcelVer = 0
    
    ' Try to find a version of Excel 10
       
    ' Attempt to activate an existing instance
    Err.Clear
    Set ExcelServer = GetObject(, "Excel.Application.10")
    If Err.Number = 0 Then
        ExcelVer = 10
        GoTo FINISH
    End If
    
    ' Create a new instance
    Err.Clear
    Set ExcelServer = CreateObject("Excel.Application.10")
    If Err.Number = 0 Then
        ExcelVer = 10
        GoTo FINISH
    End If
    
FINISH:
    ' If we don't have Excel installed, refuse to run
    If (ExcelVer = 0) Then
        MsgBox "ExcelXP skal være installeret for at bruge denne metode", vbCritical, conDemoName
        ExelLink.Hide
    End If

    ExcelServer.WindowState = -4140 ' xlMinimized
    ExcelServer.Visible = True  ' Make it visible
    
End Sub

-----------------------------------------------------------------------------
In between objWorksheet and objWorkbook is set:

Set objWorkBook = ExcelServer.Workbooks.Open("Path")
Set objWorksheet = objWorkBook.Sheets(1),

where Path is the path to my Excel workbook

The disconnecting code:

------------------------------------------------------------------------------
.......

Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set ExcelServer = Nothing

' close Excel
    Dim excelApp As Excel.Application
    On Error Resume Next

Err.Clear
    Set excelApp = GetObject(, "Excel.Application.10")
    If Err <> 0 Then
        Err.Clear
        MsgBox "Excel er ikke aktiv.", vbExclamation
    Else
        excelApp.DisplayAlerts = False
        excelApp.Quit
    End If
  
Exit Sub

_______________________________________________


To mtuersley:

Excellink says about the code:

' Some of this code is not the way we would ordinarily
' write it within Excel. However, we need to work around
' a pair of Excel anomalies. If we refer to the Range
' object, or use the With/End With construction, it turns
' out that Excel does not remove itself from RAM when closed.

I will study your article

Thanks again
Claus

[ Moderator Action = ON ] What are [ CODE ] tags... [ Moderator Action = OFF ]

[mtuersley]

The disconnecting code:
------------------------------------------------------------------------------
Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set ExcelServer = Nothing

' close Excel
Dim excelApp As Excel.Application
On Error Resume Next

Err.Clear
Set excelApp = GetObject(, "Excel.Application.10")
If Err <> 0 Then
Err.Clear
MsgBox "Excel er ikke aktiv.", vbExclamation
Else
excelApp.DisplayAlerts = False
excelApp.Quit
End If

Exit Sub

Is it a typo that you have the connection code within the disconnect code? The disconnect should just be:

If Not objWorksheet Is Nothing Then Set objWorksheet = Nothing
If Not objWorkBook Is Nothing Then Set objWorkBook = Nothing
If Not ExcelServer Is Nothing Then Set ExcelServer = Nothing
If Not excelApp Is Nothing Then Set excelApp = Nothing

Also why are you using two Excel App objects?

To mtuersley:

Excellink says about the code:

' Some of this code is not the way we would ordinarily
' write it within Excel. However, we need to work around
' a pair of Excel anomalies. If we refer to the Range
' object, or use the With/End With construction, it turns
' out that Excel does not remove itself from RAM when closed.

Especially if they never close out their references to Excel objects. I've used both and there is no problem with Excel closing [maybe back in pre2000]. But I also rarely use Excel itself since I opt for the ODBC connection.

Good Luck,
Mike

[cll]

Ok - I can't give a good answer to your questings (but must be due to my insufficiency to vba).
I've tried your suggestions without much result, but found out that a little End before leaving the code solved the problem ??

Thanks again!
Claus

[burling]

....Read my article on data access [www.cadalyst.com] as you are better off using an ODBC connection to the XLS file than opening the entire Excel application.

Any ideas on where i could find this article? I tired searchin Cadalyst with no luck

thanks

Brett

[Ed Jobe]

I couldn't find it either. Just ask Mike. Click on his name in this thread and send him an email.