BrisChri Since 1956 |
ça fonctionne bien, voici la version définitive
Code :
- Option Explicit
- Dim wb As Workbook
- Dim ws As Worksheet
- Dim nLigne As Long
- Dim nMaxLigne As Long
- Dim nCol As Long
- Dim nMaxCol As Long
- Dim vRes As Variant
- Dim nColID As Long
- Dim nColTitle As Long
- Dim nColEntity As Long
- Dim nColdOpen As Long
- Dim nColDesc As Long
- Dim nColStatus As Long
- Dim nColTech As Long
- Dim nColLink As Long
- Dim nColReq As Long
- Dim nColPriority As Long
- Dim nColGroup As Long
- Dim nColdUpd As Long
- ____________________________________________________________________________
- Sub maj_glpi()
- Set wb = Workbooks.Open("\\GLPI\glpi.xlsx" )
- Set ws = wb.Worksheets(1)
-
- nMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column
- nMaxLigne = Range("A" & Rows.Count).End(xlUp).Row
-
- For nCol = 1 To nMaxCol
- Cells(1, nCol).Value = LTrim(RTrim(Cells(1, nCol).Value))
- Next nCol
- With ws.Cells(1).CurrentRegion
- vRes = Application.Match("ID", .Rows(1), 0)
- If IsNumeric(vRes) Then nColID = vRes
- vRes = Application.Match("Title", .Rows(1), 0)
- If IsNumeric(vRes) Then nColTitle = vRes
- vRes = Application.Match("Entity", .Rows(1), 0)
- If IsNumeric(vRes) Then nColEntity = vRes
- vRes = Application.Match("Opening date", .Rows(1), 0)
- If IsNumeric(vRes) Then nColdOpen = vRes
- vRes = Application.Match("Description", .Rows(1), 0)
- If IsNumeric(vRes) Then nColDesc = vRes
- vRes = Application.Match("Status", .Rows(1), 0)
- If IsNumeric(vRes) Then nColStatus = vRes
- vRes = Application.Match("Assigned to - Technician", .Rows(1), 0)
- If IsNumeric(vRes) Then nColTech = vRes
- vRes = Application.Match("Linked tickets - All", .Rows(1), 0)
- If IsNumeric(vRes) Then nColLink = vRes
- vRes = Application.Match("Requester", .Rows(1), 0)
- If IsNumeric(vRes) Then nColReq = vRes
- vRes = Application.Match("Priority", .Rows(1), 0)
- If IsNumeric(vRes) Then nColPriority = vRes
- vRes = Application.Match("Assigned to - Group", .Rows(1), 0)
- If IsNumeric(vRes) Then nColGroup = vRes
- vRes = Application.Match("Last update", .Rows(1), 0)
- If IsNumeric(vRes) Then nColdUpd = vRes
- End With
- If nColID * nColTitle * nColEntity * nColdOpen * nColDesc * nColStatus * nColTech * nColLink * nColReq * nColPriority * nColGroup * nColdUpd > 0 Then
- For nLigne = 2 To nMaxLigne
- Cells(nLigne, nColID).Value = Val(Cells(nLigne, nColID))
- Cells(nLigne, nColTech).Value = RTrim(LTrim(Cells(nLigne, nColTech)))
- Cells(nLigne, nColLink).Value = Val(Cells(nLigne, nColLink))
- Next nLigne
- Else
- MsgBox "Certaines colonnes sont manquantes" & Chr(10) & " " & Chr(10) & "Il faut modifier la vue dans GLPI et" & Chr(10) & "refaire l'extraction", vbCritical
- End If
- wb.Close savechanges:=True
- End Sub
|
---------------
Si une frite n'a pas de mayo, alors elle ne pourra pas aller à la piscine
|