Excel to SW General Table
Excel to SW General Table
(OP)
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
thanks in advance,
RacingD






RE: Excel to SW General Table
SW2008 Office Pro SP4.0
Intel Core 2 Duo CPU
2.2GHz, 2.00GB RAM
QuadroFX 3700
SpacePilot
RE: Excel to SW General Table
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.
RE: Excel to SW General Table
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
RE: Excel to SW General Table
RE: Excel to SW General Table
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
RE: Excel to SW General Table
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:
CODE
' (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
RE: Excel to SW General Table
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
RE: Excel to SW General Table
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 #####
RE: Excel to SW General Table
-handleman, CSWP (The new, easy test)
RE: Excel to SW General Table
...Enjoy,
Ken
CODE
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
RE: Excel to SW General Table
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
RE: Excel to SW General Table
Matt Lorono
CAD Engineer/ECN Analyst
Silicon Valley, CA
Lorono's SolidWorks Resources
Co-moderator of Solidworks Yahoo! Group
and Mechnical.Engineering Yahoo! Group