Excel to SW General Table 1

[RacingD98]

Is there a way to copy and paste an Excel spreadsheet to a Solidworks general table without having to copy and paste cell to cell? I know I can paste the spreadsheet onto a drawing but we use Smarteam and have found Smarteam and embedded spreadsheets don't play well with each other. We currently are using 2006 and will be upgrading to 2008 in the near future.

thanks in advance,
RacingD

 

[uGlay]

subscribing, because I need to do this too and I don't know how.

SW2008 Office Pro SP4.0
Intel Core 2 Duo CPU
2.2GHz, 2.00GB RAM
QuadroFX 3700
SpacePilot

 

[CorBlimeyLimey]

Does an inserted DT play nice with SmartTeam?

If so you may be able to call the data (or copy and paste) from the Excel spreadsheet into a blank (dummy) DT and then call that into the drawing. It's very similar to an OLE insertion, but may be a workaround.

 

[RacingD98]

"Does an inserted DT play nice with SmartTeam?"

I have used DT's many times, but I don't recall ever putting the DT on a drawing format. I can give it a try tomorrow at work, but my gut feeling is if it is embedded, Smarteam won't like it.

RacingD

 

[CorBlimeyLimey]

I don't believe an inserted DT is any more embedded than an Excel BOM.

 

[RacingD98]

CBL,

I performed the Design Table test this morning and had the same problems. Basically, if we want to edit an Excel table which has been inserted into a Solidworks drawing we have to go to ADD-IN and turn off SmarTeam, then everything functions as you would expect. While this is a work around, it’s not one we desire to have.

I thought about going the Enhancement Request route but by the time I go through our administrator who goes through our IT department who goes through our VAR, blah, blah, blah, it will take 6 months to get out of this office, it’s just not worth it. If anyone on this forum with fewer restrictions wants to run with this feel free to do so.

RacingD

 

[brengine]

RacingD98 , uGlay

I wrote the attached macro in SW2007 to do this. To use with SW2006, in the VBA Editor you will probably have to go up to Tools/References and uncheck the MISSING ones, and then check the corresponding SW2006 lines. I think all the calls I used are backwards compatable to SW2006.

All Excel data will be Pasted in as Values...I could not figure out how to Paste in Formulas.

From the macro:

' Preconditions: (1) Drawing must be open.
'                (2) SW Table must already exist with
'                       at least enough Rows/Columns
'                       already available to Paste in to.
'                (3) Data copied to Clipboard
'                (4) SW Table Pre-Selected

You should get an error message if any of the above pre-conditions are not met before calling the macro. So hopefully that'll be enough to get you going with this.

Hope it helps.
Ken

 

[RacingD98]

kchunk,

Thanks for the code. I will certainly give this a try when I return to work. I have a lot of vacation/holiday time built up so I won’t return to work till January 5th.

RacingD

 

[maperalia]

I have a questions regarding get notes on SW drawings from excel cell. I have recorded a macro to add a note in the SW drawings (see below).

Basically; I want to update the....Set Note = Part.InsertNote("HU987456")....
be taken from excel "Sheet Test" cell "D1". For example, if I type in the Sheet Test, cell "D1" WR65478. I want this information get in my drawing when the macro runs.

Could you please let me know if is possible to do it?
Thanks in advance.

Maperalia

'### Start Macro #####
Sub Note()

Dim swApp As Object
Dim Part As Object
Dim boolstatus As Boolean
Dim longstatus As Long, longwarnings As Long
Dim FeatureData As Object
Dim Feature As Object
Dim Component As Object
Dim Note As Object
Dim Annotation As Object
Dim TextFormat As Object

Set swApp = Application.SldWorks

Set Part = swApp.ActiveDoc

boolstatus = Part.Extension.SelectByID("Sheet1", "SHEET", 0.07389519540958, 0.1025964662951, 0, False, 0, Nothing)
Part.EditTemplate
Part.EditSketch

boolstatus = Part.Extension.SelectByID("Revision Table Anchor", "BOMTEMP", 0, 0, 0, False, 0, Nothing)

Set Note = Part.InsertNote("HU987456")
If Not Note Is Nothing Then
Note.Angle = 0
boolstatus = Note.SetBalloon(0, 0)
Set Annotation = Note.GetAnnotation()
If Not Annotation Is Nothing Then
longstatus = Annotation.SetLeader2(False, 0, True, True, False, False)
boolstatus = Annotation.SetPosition(0.1583153968128, 0.09275491145937, 0)
boolstatus = Annotation.SetTextFormat(10, True, TextFormat)
End If
End If

Part.ClearSelection2 True
Part.WindowRedraw

boolstatus = Part.Extension.SelectByID("Sheet Format1", "SHEET", 0.1020137934272, 0.1451902159624, 0, False, 0, Nothing)
Part.EditSheet
Part.EditSketch

End Sub

'### End Macro #####

 

[handleman]

Yes, it is possible. You should be able to figure it out by inspecting Ken's code. Please start a new thread for new topics in the future.

-handleman, CSWP (The new, easy test)

 

[brengine]

maperalia,

...Enjoy,
Ken

Option Explicit

Sub main()
    MsgBox GetExcelApp("c:\temp\Book1.xls", "Sheet1", "D1")
End Sub

Private Function GetExcelApp(sFilePathAndName As String, sSheet As String, sCell As String) As String
    Dim xlApp As Excel.Application
    Dim xlWkbk As Excel.Workbook
    Dim bXlWasNotRunning As Boolean
    
    On Error Resume Next
        Set xlApp = GetObject(, "Excel.Application")
    
        If (xlApp Is Nothing) Or (Err.Number <> 0) Then
            Set xlApp = CreateObject("Excel.Application")
            Err.Clear
            bXlWasNotRunning = True
        Else
            bXlWasNotRunning = False
        End If
    On Error GoTo 0
    
    If (xlApp Is Nothing) Then
        MsgBox "Excel NOT found!", vbOKOnly + vbExclamation
        Exit Function
    End If

    'If Excel was not previously open, it will not be visible to the User
    '-->Only make Excel visible if you *WANT* to see it. If you are
    '   automatically retrieving data, then there's probably no need.
    'xlApp.Visible = True
    
    Set xlWkbk = xlApp.Workbooks.Open(sFilePathAndName) 'From VBA
    'Set xlWkbk = GetObject(sFilePathAndName) 'From VB or other
    If (xlWkbk Is Nothing) Then
        MsgBox "File NOT found!" & Chr(13) & Chr(13) & sFilePathAndName, vbOKOnly + vbExclamation
        Exit Function
    End If

    'Uncomment for Debugging if needed
    'Debug.Print xlWkbk.Worksheets(sSheet).Cells(1, 1).Value 'Cell value using Row and Column
    'Debug.Print xlWkbk.Worksheets(sSheet).Range("A1").Value 'Cell value using Range
    'MsgBox xlWkbk.Worksheets(sSheet).Cells(1, 1).Value

    GetExcelApp = xlWkbk.Worksheets(sSheet).Range(sCell).Value

    If (bXlWasNotRunning) Then
        xlApp.Quit
    End If

    Set xlWkbk = Nothing
    Set xlApp = Nothing

End Function

 

[RacingD98]

brengine,

I know its been a while since you posted your code for this but I just wanted to let you know I tried it and it works great. A BIG THANKS goes out to you.

thanks again,
RacingD

 

[fcsuper]

There's also a macro made by malpass that will create the table called ImportBOM:

http://forum.angelsix.com/viewtopic.php?f=26&t=25&start=0

It's fairly powerful.

Matt Lorono
CAD Engineer/ECN Analyst
Silicon Valley, CA
Lorono's SolidWorks Resources
Co-moderator of Solidworks Yahoo! Group
and Mechnical.Engineering Yahoo! Group