Hello
I'm trying to generate an Excel ss using vb.net and add/name sheets. It works but not quite
[code]
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim intCount As Short ' from 0 to 255 - should be sufficient. Integer would add unnecessary "space"
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
' add new worksheets & name worksheets ----------------------------
xlApp.Range("A50:I50").EntireColumn.AutoFit()
With xlWorkBook
.Sheets("Sheet1").Select()
intCount = 1
'use FOR Instead of While
'Insert sheet(s) 1 - 7 ( Takoro #1 - Takoro #7 )
'For intCount = 1 To 7
'better to work with exact objects, rather than index values, in order to identify a sheet. Especially here:
Dim ws As Excel.Worksheet
For intCount = 1 To 7
ws = .Worksheets.Add(After:=.Worksheets(intCount)) 'INSERT AFTER LAST WORKSHEET
'Use counter variable properly to rename all sheets accordingly
ws.Name = "Taroko # " & intCount.ToString()
ws = Nothing
Next
.Worksheets.Add(Before:=.Worksheets(1)) 'Add config sheet last. BEFORE Takoro #1
.Sheets(1).Name = "config" 'Config is now the first sheet
xlApp.Visible = True
End With
xlWorkSheet.Columns("A:J").ColumnWidth = 28
' BIOS Lab header --------------------------
With xlWorkSheet
.Cells(1, 1) = "Location"
.Cells(1, 2) = "BIOS Lab"
.Cells(1, 3) = "BIOS Lab"
.Cells(1, 4) = "BIOS Lab"
.Cells(1, 5) = "BIOS Lab"
.Cells(1, 6) = "BIOS Lab"
.Cells(1, 7) = "BIOS Lab"
.Cells(1, 8) = "BIOS Lab"
.Cells(1, 9) = "BIOS Lab"
' System name header -----------------------
.Cells(2, 1) = "System name:"
.Cells(2, 2) = "Taroko #1"
.Cells(2, 3) = "Taroko #2"
.Cells(2, 4) = "Taroko #3"
.Cells(2, 5) = "Taroko #4"
.Cells(2, 6) = "Taroko #5"
.Cells(2, 7) = "Taroko #6"
.Cells(2, 8) = "Taroko #7"
.Cells(2, 9) = "Taroko #8"
.Cells(3, 1) = "Mac address:"
.Cells(4, 1) = "Processor:"
.Cells(4, 1).Font.Bold = True
' gray rows ----------------------------------------------------------------
.Cells.Range("A4:I4").Interior.ColorIndex = 15 ' processor
.Cells.Range("A10:I10").Interior.ColorIndex = 15 ' memory
.Cells.Range("A30:I30").Interior.ColorIndex = 15 ' hdd
.Cells.Range("A34:I34").Interior.ColorIndex = 15 ' optical
.Cells.Range("A38:I38").Interior.ColorIndex = 15 ' slots
.Cells.Range("A45:I45").Interior.ColorIndex = 15 ' other info
' ---------------------------------------------------------------------------
.Cells(5, 1) = "CPU0"
.Cells(6, 1) = "Stepping"
.Cells(7, 1) = "Watt"
.Cells(8, 1) = "Other(Non-XE,XE)"
.Cells(9, 1) = "Intel Q string"
.Cells.Height.Equals(40)
.Cells(10, 1) = "Memory Total: Details hidden below"
.Cells(10, 1).Font.Bold = True
.Cells(11, 1) = "DIMM 1 - Brand (load 1)"
.Cells(12, 1) = "DIMM 1 - Size"
.Cells(13, 1) = "DIMM 1 - String 1"
.Cells(14, 1) = "DIMM 1 - String 2"
.Cells(16, 1) = "DIMM 2 - Brand (load 2)"
.Cells(17, 1) = "DIMM 2 - Size"
.Cells(18, 1) = "DIMM 2 - String 1"
.Cells(19, 1) = "DIMM 2 - String 2"
.Cells(21, 1) = "DIMM 3 - Brand (load 1)"
.Cells(22, 1) = "DIMM 3 - Size"
.Cells(23, 1) = "DIMM 3 - String 1"
.Cells(24, 1) = "DIMM 3 - String 2"
.Cells(26, 1) = "DIMM 4 - Brand (load 2)"
.Cells(27, 1) = "DIMM 4 - Size"
.Cells(28, 1) = "DIMM 4 - String 1"
.Cells(29, 1) = "DIMM 4 - String 2"
.Cells(30, 1) = "HDD bay"
.Cells(30, 1).Font.Bold = True
.Cells(31, 1) = "Top"
.Cells(32, 1) = "Middle"
.Cells(33, 1) = "Bottom"
.Cells(34, 1) = "Optical bay"
.Cells(34, 1).Font.Bold = True
.Cells(35, 1) = "Top"
.Cells(36, 1) = "Middle"
.Cells(37, 1) = "Bottom"
.Cells(38, 1) = "Slots I/O"
.Cells(38, 1).Font.Bold = True
.Cells(39, 1) = "PCIe x1 Slot 1"
.Cells(40, 1) = "PCIe x16 Slot 2"
.Cells(41, 1) = "PCIe x4 (1)Slot 3"
.Cells(42, 1) = "PCIe x16 (4) Slot 4"
.Cells(43, 1) = "PCI Slot 5"
.Cells(44, 1) = "PCI Slot 6"
.Cells(45, 1) = "Other Info"
.Cells(45, 1).Font.Bold = True
.Cells(46, 1) = "Motherboard Rev"
.Cells(47, 1) = "Power Supply Rev"
.Cells(48, 1) = "Rework date code"
.Cells(49, 1) = "Integ. Video/Audio"
End With
xlWorkSheet.SaveAs("C:\Taroko_config.xlsx")
'xlWorkBook.Close()
'xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
End Sub
[/code]
It works but it adds 3 extra sheets two on the far right and one in the second spot. Also the data that is supposed to be on the config sheet in on the one to the right of it.
thanks for any help.
Loading
Steve BrockPosted Sep 3, 2010, 12:07 PM
I believe I'm a little past this point. Thanks for trying
Steve
ShankeyPosted Sep 3, 2010, 9:31 AM
You can add new sheet using following code but dont forget to mark my answer as accepted if it helped you :):
dim xlsht as excel.worksheet
xlsht = xlbook.worksheets.add
'Now rename the worksheet to identify it.
xlsht.name= "NewSheet"
'To access the sheet within the code, you use its object name - xlsht.
'After you write into and save this excel worksheet and workbook, you can access it from 'another program by using:
xlsht = ctype(xlbook.worksheets("NewSheet"), Excel.worksheet)