1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
|
Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
Form2.xlWorkSheet = CType(Form2.xlWorkBook.Sheets(Form2.ComboBox1.Text), Excel.Worksheet)
Form2.xlWorkSheet.Activate()
Form2.xlApp.Visible = True
Dim key As String = CStr(DirectCast(ComboBox2.SelectedItem, KeyValuePair(Of Integer, String)).Key)
Dim value As String = DirectCast(ComboBox2.SelectedItem, KeyValuePair(Of Integer, String)).Value
Dim DoesSheetExists As Boolean = False
For Each xs In Form2.xlApp.Sheets
If xs.Name = value Then
DoesSheetExists = True
End If
Next
If DoesSheetExists = True Then
MsgBox("Sheet already exists", CType(MessageBoxIcon.Error, MsgBoxStyle))
Else
With Form2.xlWorkSheet
Dim lastrow As Integer = Form2.xlWorkSheet.Cells.Rows.End(XlDirection.xlDown).Row
Dim colletter As String = ColumnIndexToColumnLetter(CInt(key))
exWS2 = DirectCast(Form2.xlWorkBook.Sheets.Add, Microsoft.Office.Interop.Excel.Worksheet)
exWS2.Name = value
Form2.xlWorkSheet.Range("A1:A" & lastrow.ToString).Copy(exWS2.Range("A1"))
Form2.xlWorkSheet.Range(colletter & "1:" & colletter & lastrow.ToString).Copy(exWS2.Range("B1"))
exWS2.Range("A1").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue)
exWS2.Range("B1").Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue)
exWS2.Range("A1").Interior.ColorIndex = 8
exWS2.Range("B1").Interior.ColorIndex = 8
exWS2.Range("A2:A" & lastrow.ToString).Interior.ColorIndex = 20
exWS2.Range("A1:A" & lastrow.ToString).HorizontalAlignment = -4108
exWS2.Range("B1:B" & lastrow.ToString).HorizontalAlignment = -4108
exWS2.Range("A1").Font.Name = "Times New Roman"
exWS2.Range("B1").Font.Name = "Times New Roman"
exWS2.Range("B1").Font.FontStyle = "Gras"
exWS2.Range("A1").Font.FontStyle = "Gras"
End With
End If
End Sub
Public Function ColumnIndexToColumnLetter(ByVal colIndex As Integer) As String
Dim div As Integer = colIndex
Dim colLetter As String = String.Empty
Dim modnum As Integer = 0
While div > 0
modnum = (div - 1) Mod 26
colLetter = Chr(65 + modnum) & colLetter
div = CInt((div - modnum) \ 26)
End While
Return colLetter
End Function |
Partager