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 102 103 104 105 106 107
| Sub testCalendrier()
Dim xlApp As Excel.Application
Dim xlBook, xlsheet As Excel.Workbooks
Dim xlrange As Excel.Range
If xlApp Is Nothing Then
'Start new instance
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't Find Excel, please try again.", vbCritical
End 'Stop, can't proceed without Excel
End If
Else
Set xlrange = Nothing
Set xlApp = Nothing
Set xlBook = Nothing
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't Find Excel, please try again.", vbCritical
End 'Stop, can't proceed without Excel
End If
End If
xlApp.Visible = True
Dim strReturn As String
strReturn = String(255, 0)
GetPrivateProfileString "parametres", "templateFolder", "c:\dev\msproject\template", strReturn, Len(strReturn), ActiveProject.Path & "/config.ini"
LitDansFichierIni = Left(strReturn, InStr(strReturn, Chr(0)) - 1)
TemplateFolder = LitDansFichierIni
GetPrivateProfileString "parametres", "baseFolder", "C:\dev\msproject", strReturn, Len(strReturn), ActiveProject.Path & "/config.ini"
LitDansFichierIni = Left(strReturn, InStr(strReturn, Chr(0)) - 1)
baseFolder = LitDansFichierIni
repertoire = baseFolder
On Error Resume Next
repertoire_existe = GetAttr(repertoire) And vbDirectory
If repertoire_existe = "" Then
baserep_extst = GetAttr(baseFolder) And vbDirectory
If baserep_extst = "" Then
MsgBox " le repertoire '" & baseFolder & "' n'existe pas, il faut le créer"
End If
End If
' MsgBox "Le fichier congés sera créé dans le répertoire : " & baseFolder
Set xlBook = xlApp.Workbooks.Open(TemplateFolder & "\template_planning.xlsx")
Set xlsheet = xlBook.ActiveSheet
Set xlrange = ActiveSheet.Range("A3")
For Each myResource In ActiveProject.Resources
If (Not myResource Is Nothing) Then
If (myResource.Group = "UMANIS") Then
getcal myResource:=myResource, xlrange:=xlrange
getcongé myResource:=myResource '(traitement des exceptions)
End If
End If
Next myResource
xlApp.ActiveWorkbook.SaveAs FileName:=baseFolder & "\congés.xlsx" ', CreateBackup:=True, ConflictResolution:=xlLocalSessionChanges
xlApp.ActiveWorkbook.Close
Set xlrange = Nothing
Set xlsheet = Nothing
Set xlBook = Nothing
xlApp.Application.Quit
Set xlApp = Nothing
End Sub
'---------------------------------------------------------------------------------------
' Procedure : feuilleDeTempsPourRessource
' Auteur : Jean-Yves DUMAS
' Date : 28/06/2012
' Objet : cette procedure copie les données de la ressource MSproject sélectionnée dans
' un classeur Excel. La structure de la feuille d'origine est le fichier template.xlsx
' Paramètres - Entrée :
' - sortie :
'---------------------------------------------------------------------------------------
Private Sub getcal(ByVal myResource As Resource, ByVal xlrange As Excel.Range)
Dim MyMOnth As Month
Dim MyDay As Day
Dim NbCalendar As Integer
Dim sortie As String
On Error GoTo gest_err
Debug.Print "-------------------------------------------------------------"
<affectation à la première cellule de myResource.Name>
Set xlrange = ActiveSheet.Range("A3")
xlrange.EntireRow.Insert
For Each MyMOnth In myResource.Calendar.Years("2012").Months
sortie = "ressource : " & myResource.Name & " - mois de " & MyMOnth.Name
For Each MyDay In MyMOnth.Days
<traitement de la cellule de la journée (travaillé ou chomé)>
Next MyDay
<traitement sur changement de mois>
Next MyMOnth
Exit Sub
gest_err:
Debug.Print " erreur : "; Err.Number; " - "; Err.Description; " resource : "; resourceTemp.Name
msgbix " erreur : " & Err.Number & " - " & Err.Description & " resource : "& resourceTemp.Name |
Partager