Hi all,

I am trying to execute a macro within an Excel spreadsheet from within a SW VBA macro.

The spreadsheet consists of various thread data, major pitch and minor diameter information, along with the capability to calculate 3 wire size information as well.

The macro in the spreadsheet allows the user to select the thread form they desire, the size of the thread, and if they want 3 wire size inspection information as well.

Once they select this information, it is then pasted into the active drawing within SW as a table.

This part works quite well...

The next part is where I am having a few issues.

I would like to have a custom macro button available to the operator from within SW so that the operator does not have to open Excel Spreadsheet and run the program from there.

The process I would like to have the macro follow is thus:

1. Check to make sure a drawing document is active.

2. Create a new instance of Excel

3. Open the required spreadsheet.

4. Run the "Display_Thread_Form" subroutine within the spreadsheet macros

5. Close the Spreadsheet instance, leaving any other instance of Excel intact.

6. Return to SW displaying the previously active drawing.

So far points 1,2 & 3 are working.

Point 4 is not working at all using the method shown in the code below.

Point 5 works also but if I use xlApp.quit it closes the entire Excel Application down, so any other spreadsheet that may happen to be open is also closed.

If I enable the TASKKILL process then Excel is definitely shut down!

Point 6...? I always end up with not returning to SW! How do I get back there?

The code:

Sub main()

    Dim swApp           As SldWorks.SldWorks

    Dim swModel         As SldWorks.ModelDoc2

    Dim xlApp           As Excel.Application

    Dim xlWB            As Excel.Workbook

    Dim ActiveDocName   As String

    Dim longstatus      As Long

    Dim vFileType       As Variant

    'Link to Solidworks

    Set swApp = Application.SldWorks

    Set swModel = swApp.ActiveDoc

    If swModel Is Nothing Then

        MsgBox "No document loaded" & vbCrLf & _

                "Open a Solidworks Drawing first!", vbMsgBoxSetForeground + vbSystemModal

    Else

        'check the type of Open document to make sure it is a Drawing file.

        vFileType = swModel.GetType

        If vFileType = swDocDRAWING Then

        ActiveDocName = swModel.GetTitle

        'Create a link to a NEW instance of Excel

        Set xlApp = CreateObject("Excel.application")

        'Make sure instance is visible

        xlApp.Visible = True

        'Open the target Excel Spreadsheet

        Set xlWB = xlApp.Workbooks.Open("C:\Threads\Threads.xls")

        'Run the macro using the passed in name

        xlWB.Application.Run Display_thread_form

        'Close the active workbook, do not save changes.

        xlWB.Close False

        'Close the Excel Application

        'xlApp.Quit

        'activate the drawing again.

        swApp.ActivateDoc2 ActiveDocName, False, longstatus

        'Clear memory

        Set xlApp = Nothing

        Set xlWB = Nothing

        'Kill off any Excel Process that may be still running

        'Shell "TASKKILL /F /IM Excel.exe", vbHide

    End If

End Sub

I am hoping that someone will be able to help and I thank you in advance!

Regards
Brian.