Hide Table of Contents

Get Excel Design Table Worksheet Example (VBA)

This example shows how to get the Excel worksheet of a design table.

 

'-------------------------------------

'

' Preconditions: Drawing document is open that

'                contains an Excel-based design

'                table.

'

' Postconditions: None

'

'-------------------------------------

Option Explicit

 

Sub main()

 

    Dim swApp As SldWorks.SldWorks

    Dim myModelDoc As SldWorks.ModelDoc2

    Dim myDrawingDoc As SldWorks.DrawingDoc

    Dim myView As SldWorks.View

    Dim myDesignTable As SldWorks.DesignTable

    Dim designTableDoc As SldWorks.ModelDoc2

    Dim hasDT As Boolean

    Dim viewModelName As String

    Dim longstatus As Long

    Dim myWorksheet As Excel.Worksheet

    Dim tableRowCount As Long, tableColCount As Long

    Dim tableFirstRow As Long, tableFirstCol As Long

 

    Set swApp = Application.SldWorks

    Set myModelDoc = swApp.ActiveDoc

    Set myDrawingDoc = myModelDoc

    Set myView = myDrawingDoc.GetFirstView

    While Not myView Is Nothing

        hasDT = myView.HasDesignTable()

        If Not (hasDT = 0) Then

            viewModelName = myView.GetReferencedModelName()

            Set designTableDoc = swApp.ActivateDoc2(viewModelName, True, longstatus)

            Set myDesignTable = designTableDoc.GetDesignTable()

            If Not myDesignTable Is Nothing Then

                myDesignTable.Attach

            ' These APIs deal with the actual design table data, not necessarily all of the information

            ' that is in the Excel worksheet.

                Debug.Print "Total Row Count = " & myDesignTable.GetTotalRowCount

                Debug.Print "      Col Count = " & myDesignTable.GetTotalColumnCount

                Debug.Print "Start Row = " & myDesignTable.GetStartRowNumber

                Debug.Print "      Col = " & myDesignTable.GetStartColumnNumber

            ' These APIs deal with the information in the Excel worksheet.

                tableRowCount = myDesignTable.GetVisibleRowCount

                Debug.Print "Table row count = " & tableRowCount

                tableColCount = myDesignTable.GetVisibleColumnCount

                Debug.Print "Table column count = " & tableColCount

                tableFirstRow = myDesignTable.GetVisibleTopRowNumber

                tableFirstCol = myDesignTable.GetVisibleLeftColumnNumber

                Debug.Print "Visible Row Count = " & tableRowCount

                Debug.Print "        Col Count = " & tableColCount

                Debug.Print "Visible Top Row  = " & tableFirstRow

                Debug.Print "        Left Col = " & tableFirstCol

            ' Exposing the Excel interface provide you with more information

            ' than currently available from SolidWorks APIs. For example,

            ' DXF/DWG translation can now reproduce a table that looks like the design table.

            ' Obviously, the translator will have to the work in C++.

                Set myWorksheet = myDesignTable.Worksheet

                If Not myWorksheet Is Nothing Then

                    Debug.Print ""

                    Debug.Print "The name of the worksheet is " & myWorksheet.Name

                    Dim i As Integer, rowIndex As Integer

                    Dim j As Integer, colIndex As Integer

                    Dim cellRange As Excel.Range

                    Dim rowRange As Excel.Range

                    Dim colRange As Excel.Range

                    Dim rowHeight As Double, colWidth As Double

                    Dim rowHidden As Boolean, colHidden As Boolean

                    Dim cellValue As String, cellText As String

                    Dim horzalign As Integer, vertalign As Integer, ismerged As Boolean

                    Dim halignStr As String, valignStr As String

                    For i = 1 To tableRowCount

                        rowIndex = tableFirstRow + i - 1

                        Set rowRange = myWorksheet.Rows(rowIndex)

                        rowHeight = rowRange.rowHeight

                        rowHidden = rowRange.Hidden

                        Debug.Print "Row[" & rowIndex & "] Height = " & rowHeight & ", Hidden = " & rowHidden

                    Next i

Stop

                    For j = 1 To tableColCount

                        colIndex = tableFirstCol + j - 1

                        Set colRange = myWorksheet.Columns(colIndex)

                        colWidth = colRange.ColumnWidth

                        colHidden = colRange.Hidden

                        Debug.Print "Col[" & colIndex & "] Width = " & colWidth & ", Hidden = " & colHidden

                    Next j

Stop

                    For i = 1 To tableRowCount

                        rowIndex = tableFirstRow + i - 1

                        For j = 1 To tableColCount

                            colIndex = tableFirstCol + j - 1

                            Set cellRange = myWorksheet.Cells(rowIndex, colIndex)

                            cellValue = cellRange.Value2

                            cellText = cellRange.Text

                            horzalign = cellRange.HorizontalAlignment

                            Select Case horzalign

                            Case XlHAlign.xlHAlignLeft

                                halignStr = "Left"

                            Case XlHAlign.xlHAlignCenter

                                halignStr = "Center"

                            Case XlHAlign.xlHAlignRight

                                halignStr = "Right"

                            Case XlHAlign.xlHAlignCenterAcrossSelection

                                halignStr = "Center Across"

                            Case XlHAlign.xlHAlignDistributed

                                halignStr = "Distributed"

                            Case Else

                                halignStr = "unknown"

                            End Select

                            vertalign = cellRange.VerticalAlignment

                            Select Case vertalign

                            Case XlVAlign.xlVAlignBottom

                                valignStr = "Bottom"

                            Case XlVAlign.xlVAlignCenter

                                valignStr = "Middle"

                            Case XlVAlign.xlVAlignTop

                                valignStr = "Top"

                            Case XlVAlign.xlVAlignDistributed

                                valignStr = "Distributed"

                            Case Else

                                valignStr = "unknown"

                            End Select

                            ismerged = cellRange.MergeCells

                            Debug.Print "Cell[" & rowIndex & "," & colIndex & "] = " & cellText & " " & cellValue & " " & halignStr & " " & valignStr & " " & ismerged

                            

                        Next j

                    Next i

                End If

                Set myWorksheet = Nothing

                myDesignTable.Detach

            End If

            swApp.CloseDoc viewModelName

        End If

        Set myView = myView.GetNextView()

    Wend

End Sub



Provide feedback on this topic

SOLIDWORKS welcomes your feedback concerning the presentation, accuracy, and thoroughness of the documentation. Use the form below to send your comments and suggestions about this topic directly to our documentation team. The documentation team cannot answer technical support questions. Click here for information about technical support.

* Required

 
*Email:  
Subject:   Feedback on Help Topics
Page:   Get Excel Design Table Worksheet Example (VBA)
*Comment:  
*   I acknowledge I have read and I hereby accept the privacy policy under which my Personal Data will be used by Dassault Systèmes

Print Topic

Select the scope of content to print:




x

We have detected you are using a browser version older than Internet Explorer 7. For optimized display, we suggest upgrading your browser to Internet Explorer 7 or newer.

 Never show this message again
x

Web Help Content Version: API Help (English only) 2012 SP05

To disable Web help from within SOLIDWORKS and use local help instead, click Help > Use SOLIDWORKS Web Help.

To report problems encountered with the Web help interface and search, contact your local support representative. To provide feedback on individual help topics, use the “Feedback on this topic” link on the individual topic page.