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 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101
| Private Sub UserForm_Initialize()
Set myolapp = CreateObject("Outlook.Application")
Set myinspector = myolapp.ActiveInspector
With myinspector.CurrentItem
If .CompanyName = "" Then ref = .LastName & " " & .FirstName Else ref = .CompanyName
End With
'-------------------------------Création listview-----------------------------------------
With Existant.ListView1
.View = lvwReport
.FullRowSelect = True
End With
With Existant.ListView1
With .ColumnHeaders
.Clear
.Add , , "Lots", 100
End With
End With
Dim x As Integer
With Existant.ListView1
.ListItems.Clear
.GridLines = True
End With
Existant.ListView1.ListItems.Add , , "Lot 1"
Existant.ListView1.ListItems.Add , , "lot 2"
'-------------------------------Ouverture fichier excel-----------------------------------------
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
Set appExcel = CreateObject("Excel.Application")
appExcel.Visible = True
'Ouverture d'un fichier Excel
Dim Presence As Boolean
Presence = False
For Each w In appExcel.Workbooks
If w.Name = "Ratio 2011.xls" Then Presence = True
Next w
If Presence = True Then
Else
Set wbExcel = appExcel.Workbooks.Open("C:\Documents\Ratio 2011.xls")
End If
'wsExcel correspond à la première feuille du fichier
Set wsExcel = wbExcel.Worksheets(1)
'-------------------------------Alimentation de la listview suivant fichier excel-----------------------------------------
Dim v As Integer
v = "0"
For x = 4 To appExcel.Workbooks("Ratio 2011.xls").Sheets("Ratio").Range("c65536").End(xlUp).Row
If appExcel.Workbooks("Ratio 2011.xls").Sheets("Ratio").Cells(x, 3).Value = ref Then
v = v + 1
With Existant.ListView1
With .ColumnHeaders
.Add , , "Type", 100
Existant.ListView1.ListItems(1).ListSubItems.Add , , "Devis n°" & v
Existant.ListView1.ListItems(2).ListSubItems.Add , , "Type :" & appExcel.Workbooks("Ratio 2011.xls").Sheets("Ratio").Cells(x, 4)
.Add , , "Prix", 50
Existant.ListView1.ListItems(1).ListSubItems.Add , , "Année : " & appExcel.Workbooks("Ratio 2011.xls").Sheets("Ratio").Cells(x, 2)
Existant.ListView1.ListItems(2).ListSubItems.Add , , "Mois : " & appExcel.Workbooks("Ratio 2011.xls").Sheets("Ratio").Cells(x, 1)
.Add , , "Ratio", 50
Existant.ListView1.ListItems(1).ListSubItems.Add , , ""
Existant.ListView1.ListItems(2).ListSubItems.Add , , ""
End With
End With
End If
Next x
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'------------------Fermeture excel---------------------------------
appExcel.Application.DisplayAlerts = False
appExcel.ActiveWorkbook.Saved = True
appExcel.ActiveWorkbook.Close
appExcel.Quit
appExcel.Visible = True
appExcel.Application.DisplayAlerts = True
'------------------Transfert vers note du contact---------------------------------
Set myolapp = CreateObject("Outlook.Application")
Set myinspector = myolapp.ActiveInspector
With myinspector.CurrentItem
.Body = Existant.note
End With
End Sub |
Partager