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 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
| Private Sub OuvrirFichier()
'Déclaration des variables
Dim appExcel As Excel.Application 'Application Excel
Dim wbExcel As Excel.Workbook 'Classeur Excel
Dim wsExcel As Excel.Worksheet 'Feuille Excel
'Ouverture de l'application
appExcel = CreateObject("Excel.Application")
'Ouverture d'un fichier Excel
wbExcel = appExcel.Workbooks.Open("C:\TEMP\toto.xls")
'wsExcel correspond à la première feuille du fichier
Dim c, ligne, i, s, r As Integer
Dim strSheetName As String
Dim blnFound As Boolean
appExcel.Visible = True
Dim oRng As Excel.Range
Dim firstCell As Excel.Range
Try
For ligne = 0 To DataGridView1.RowCount - 1
For c = 0 To DataGridView1.ColumnCount - 1
Dim monmois = Mid(DataGridView1(0, ligne).Value, 4, 2)
Dim monann = Mid(DataGridView1(0, ligne).Value, 7, 5)
Dim madate = monmois & monann
strSheetName = madate
i = wbExcel.Sheets.Count
For j = 1 To i
If wbExcel.Worksheets(j).Name = strSheetName Then
wbExcel.Sheets(strSheetName).select()
blnFound = True
Exit For
Else
blnFound = False
End If
Next
If blnFound = False Then
wsExcel = wbExcel.Worksheets.Add()
wsExcel.Name = strSheetName
oRng = wsExcel.Range("A1", "C1")
oRng(ligne + 1, c + 1) = DataGridView1.Rows(ligne).Cells(c).Value
Else
wsExcel = wbExcel.ActiveSheet
oRng = wsExcel.Range("A1", "C1")
oRng(ligne + 1, c + 1) = DataGridView1.Rows(ligne).Cells(c).Value
End If
Next c
Next ligne
For s = 0 To wbExcel.Sheets.Count - 1
wsExcel = wbExcel.ActiveSheet
For r = 0 To wsExcel.Rows.Count - 1
Dim maval As String
'firstCell = wsExcel.Range("A1", "C1")
maval = wsExcel.Cells(r, 0).value
If maval = "" Then
wsExcel.Cells(r, 0).Delete()
End If
'Dim lastCell As Excel.Range
'lastCell = firstCell.End(Excel.XlDirection.xlDown)
'Dim entireColumn As Excel.Range
'entireColumn = wsExcel.Range(firstCell, lastCell)
'If firstCell.Value Is Nothing Then
'entireColumn.EntireRow.Delete()
'End If
' For r = 0 To wsExcel.Rows.Count - 1
'If oRng.Value Is Nothing Then
'oRng.Delete()
'End If
Next r
Next s
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub |
Partager