Control NX attributes write to different worksheet in an existing excelfile

Submitted by garysgcn on Mon, 10/25/2021 - 21:13
Forums: 
Journaling / NXOpen API

I have an existing VB that enable extract data from NX attributes to a new excelfile which is working perfectly. Now, I have a new requirement to extract data to different worksheet said "Worksheet-A" and "Worksheet-B" in the same existing excel. Anyone can help on the VB programs that can write data to different worksheet determine by the number of rows from NX attributes to different worksheet? Example like Attribute data 1 to 30 -write to worksheet-A and 31 to 60 write to worksheet-B? Thanks in advanced.

NXJournaling - Tue, 10/26/2021 - 08:47

The code snippet below creates a new worksheet in an existing Excel workbook. It illustrates how to create a new sheet, which should help with the first part of your question. For the 2nd part, I'd suggest saving your information in some sort of data structure (list of string, perhaps); this way you can easily divide up the entries and write them to the appropriate sheet.

This snippet is part of a larger journal that records point information to Excel. Each time the journal is run, it creates a new sheet based on the current time and writes the point information to the new sheet. I can post the entire journal if you are interested.

If objWorkbook Is Nothing Then
theUISession.NXMessageBox.Show("Error", NXMessageBox.DialogType.Error, "Could not open Excel file: " & excelFileName & ControlChars.NewLine & "journal exiting.")
theSession.UndoToMark(markId1, "journal")
Exit Sub
End If

objWorksheet = objWorkbook.Sheets.Add

Dim timestamp As String
timestamp = Year(Today).ToString & Month(Today).ToString & Microsoft.VisualBasic.Day(Today).ToString & "."
timestamp = timestamp & Hour(Now).ToString & Minute(Now).ToString & Second(Now).ToString

objWorksheet.Name = timestamp
objWorksheet.move(, objWorkbook.Worksheets(objWorkbook.worksheets.Count))
objWorkbook.Save()

garysgcn - Tue, 10/26/2021 - 23:20

Thanks for your help.

I guess my issue is how to switch from open one existing excel worksheet (worksheet-A) write data in row1 to row30, and switch to another worksheet (worksheet-B) in the sane existing excel workbook to write data in row31 to row60

objBook = objExcel.Workbooks.Open(PathExcelFile)
index is the number of attributes

If index <= 30 Then
objBook.sheet("worksheet-A).cells(index, 1).value = PlotDataArray(0, index)
objBook.sheet("worksheet-A").cells(index, 2).value = PlotDataArray(1,index)
objBook.sheet("worksheet-A").cells(index, 3).value = PlotDataArray(2,index)
objBook.sheet("worksheet-A").cells(index, 4).value = PlotDataArray(3,index)
..
.. until row30

End If

objBook = objExcel.Workbooks.Open(PathExcelFile)
If index > 30 Then
objBook.sheet("worksheet-B).cells(index, 1).value = PlotDataArray(0, index)
objBook.sheet("worksheet-B").cells(index, 2).value = PlotDataArray(1,index)
objBook.sheet("worksheet-B").cells(index, 3).value = PlotDataArray(2,index)
objBook.sheet("worksheet-B").cells(index, 4).value = PlotDataArray(3,index)
...
...
until row30

NXJournaling - Wed, 10/27/2021 - 08:03

http://nxjournaling.com/comment/5990#comment-5990

The code in the link above will, among other things, list all the sheets found in an Excel workbook. Get a reference to the desired sheet and use it when writing the data.

Edit: corrected link