Hi @brianmD486F. There are likely a lot of possible code paths to make something like that happen, but here is something I customized to your needs. I put most of the complicated Excel related stuff down into a separate custom Function for you, to make the main routine shorter and less complicated looking. You will need to edit few things within the Sub Main area of the code though, like the sExcelFile variable's value (the full path, name, & extension of the Excel file), and optionally the sheet name, and the two index numbers. I put comments after the two index numbers, to clarify what they represent, but they may not need to be changed. Those are needed because sometimes folks have column headers and sometimes they do not, so this allows us to ignore the first row if it is just column headers. The same for rows. Sometimes you may have row labels in the first column, and sometimes you may not, and this column index allows us to ignore that row if set right. The code basically opens the Excel file. Then gets the bounds of the data present on the sheet, starting with those specified indices for where the data starts, and inspecting how many rows & columns are being used. Then it copies all that data into a 2 dimensional Array of Object (because the data may be String, Double, or other data types), then closes the Excel file, because it is no longer needed. Then it loops through that array of data by each row, gets the two pieces of data for the row, then uses them to form a sheet name. Then first tries to find an existing sheet by that name first, so it will not try to duplicate one, then if that fails, it creates a new sheet, then renames it to the name it formed.
Give this a try, and let me know how it works for you.
AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Sub Main
If ThisDoc.Document.DocumentType <> DocumentTypeEnum.kDrawingDocumentObject Then
MsgBox("A Drawing document must be active for this code to work. Exiting.", vbCritical, "iLogic")
Exit Sub
End If
Dim oDDoc As DrawingDocument = ThisDoc.Document
Dim oSheets As Inventor.Sheets = oDDoc.Sheets
Dim sExcelFile As String = "C:\Temp\Testing123.xlsx"
Dim sExcelSheetName As String = "Sheet1"
Dim iFirstRowOfData As Integer = 2 'second row is first row of data (not column headers)
Dim iFirstColumnOfData As Integer = 1 'first column contains data (not row labels)
Dim oData(,) As Object = Get2DArrayOfDataFromExcel(sExcelFile, iFirstRowOfData, iFirstColumnOfData, sExcelSheetName)
If oData Is Nothing OrElse oData.Length = 0 Then
Logger.Info("No data returned from the Function.")
Exit Sub
End If
Logger.Info("You retrieved " & oData.Rank & " columns and " & oData.GetLength(0) & " rows of data.")
For iRow As Integer = LBound(oData,1) To UBound(oData, 1)
Dim sPageNo As String = oData(iRow, 1).ToString
Dim sRoomTag As String = oData(iRow, 2).ToString
Dim sSheetName As String = "Sheet " & sPageNo & " - " & sRoomTag
Dim oNewSheet As Inventor.Sheet = Nothing
Try
oNewSheet = oSheets.Item(sSheetName)
Catch
oNewSheet = oSheets.Add()
oNewSheet.Name = sSheetName
End Try
Next 'iRow
End Sub
Function Get2DArrayOfDataFromExcel(sFullFileName As String, iStartRow As Integer, _
iStartColumn As Integer, Optional sSheetName As String = vbNullString) As Object(, )
If sFullFileName = "" Then Exit Function
If System.IO.File.Exists(sFullFileName) = False Then Exit Function
Dim oExcel As Excel.Application = Nothing
Dim bAppStarted As Boolean = False
Try
oExcel = GetObject("", "Excel.Application")
Catch
oExcel = CreateObject("Excel.Application")
bAppStarted = True
End Try
If oExcel Is Nothing Then Exit Function
oExcel.DisplayAlerts = False
oExcel.Visible = False
Dim oWB As Excel.Workbook = Nothing
Try : oWB = oExcel.Workbooks.Open(sFullFileName) : Catch : End Try
If oWB Is Nothing Then Exit Function
Dim oWS As Excel.Worksheet = Nothing
If String.IsNullOrEmpty(sSheetName) Then
Try : oWS = oWB.Sheets.Item(1) : Catch : End Try
Else
Try : oWS = oWB.Sheets.Item(sSheetName) : Catch : End Try
End If
If oWS Is Nothing Then Exit Function
Dim iLastColumnUsed As Integer = oWS.UsedRange.Columns.Count
Dim iLastRowUsed As Integer = oWS.UsedRange.Rows.Count
Dim oFirstCell As Excel.Range = oWS.Cells.Item(iStartRow, iStartColumn)
Dim oLastCell As Excel.Range = oWS.Cells.Item(iLastRowUsed, iLastColumnUsed)
Dim oRange As Excel.Range = oWS.Range(oFirstCell.Address, oLastCell)
Dim oData(, ) As Object = oRange.Value
oWB.Close
If bAppStarted = True Then oExcel.Quit
Return oData
End Function
...and if needed, the main routine can be greatly improved upon with things like the ability to browse for the Excel file, use an InputBox type prompt for you to enter any needed data, ask preliminary question(s) before proceeding, and other such functionality later on.
Wesley Crihfield
(Not an Autodesk Employee)
