Attribute VB_Name = "toto"
Public CodeErreur As String
Const InputFile As String = "toto.csv"
Public Const OutputFile As String = "toto"
Sub Launch_toto()
Call AddReference
Call Generate_toto_Report
End Sub
Sub DisplayIt(Optional sDate As String = Empty)
Progressindicator.Show
End Sub
Sub Generate_toto_Report(Optional sDate As String = Empty, Optional CSVFilePath As Variant = Empty)
On Error GoTo errorHandler
Application.DisplayAlerts = False
progress 5
Application.ScreenUpdating = False
Call AddReference
Dim sDashedDate As String
If sDate <> "" Then
progress 10
sDashedDate = Format(sDate, "yyyy-mm-dd")
If OutputExists(sDashedDate) Then GoTo customExit
Set oFSO = New Scripting.FileSystemObject
progress 15
CSVFilePath = "\\server\XXX\YYYY\" & sDashedDate & "\" & InputFile
If Not oFSO.FileExists(CSVFilePath) Then CSVFilePath = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , , , False)
End If
' Import CSV
progress 20
If CSVFilePath = "" Then CSVFilePath = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , , , False)
If CSVFilePath = False Then
GoTo customExit
Else
Call ImportCSV(CSVFilePath)
End If
progress 25
' Récupération date des logs
If sDate = "" Then
Do
sDate = InputBox("Please enter logs date (JJ/MM/AAAA)", , Format(Now - 1, "dd/mm/yyyy"))
Loop Until Mid(sDate, 3, 1) = "/" And Mid(sDate, 6, 1) = "/"
End If
progress 30
sDashedDate = Format(sDate, "yyyy-mm-dd")
If OutputExists(sDashedDate) Then GoTo customExit
If Cells(1, 1) = "" And Cells(1, 2) = "" And Cells(2, 1) = "" And Cells(2, 2) = "" Then GoTo noDataExit
progress 35
Columns("D😀").Select
Selection.Replace What:=".", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
ActiveSheet.ListObjects.Add(xlSrcRange, , , xlYes).Name = "RawData"
progress 40
Range("RawData[#All]").Select
ActiveSheet.ListObjects("RawData").TableStyle = "TableStyleMedium9"
Cells.Select
Cells.EntireColumn.AutoFit
Call DeleteUselessSheets
progress 45
Range("RawData[[#Headers],[event_time]]").Select
iCol = 1
While Cells(1, iCol) <> ""
If Columns(iCol).ColumnWidth > 80 Then Columns(iCol).ColumnWidth = 80
iCol = iCol + 1
progress 50
Wend
ActiveSheet.Name = "Raw Data"
' Création TCD
Sheets.Add Before:=Worksheets(1)
progress 55
Sheets(1).Name = "AccessPorn_" & sDashedDate
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"RawData", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="'XX_" & sDashedDate & "'!R3C1", TableName:="TCD", _
DefaultVersion:=xlPivotTableVersion14
Sheets(1).Select
Cells(3, 1).Select
progress 60
With ActiveSheet.PivotTables("TCD").PivotFields("user_dst")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("TCD").PivotFields("event_time")
.Orientation = xlRowField
.Position = 2
progress 65
End With
With ActiveSheet.PivotTables("TCD").PivotFields("referer")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("TCD").PivotFields("url")
.Orientation = xlRowField
.Position = 4
End With
progress 70
ActiveSheet.PivotTables("TCD").AddDataField ActiveSheet. _
PivotTables("TCD").PivotFields("disposition"), _
"Nombre de url", xlCount
ActiveSheet.PivotTables("TCD").PivotFields("user_dst").ShowDetail = False
Range("B:B").Select
ActiveSheet.PivotTables("TCD").PivotFields("user_dst").AutoSort _
xlDescending, "Nombre de url", ActiveSheet.PivotTables("TCD"). _
PivotColumnAxis.PivotLines(1), 1
progress 90
Range("A1").Select
If Columns("A:A").ColumnWidth > 80 Then
Columns("A:A").ColumnWidth = 80
End If
Range("A3").Select
Set oFSO = New Scripting.FileSystemObject
If Not oFSO.FolderExists(sResultsPath & sDashedDate) Then oFSO.CreateFolder (sResultsPath & sDashedDate)
ActiveWorkbook.SaveAs Filename:= _
sResultsPath & sDashedDate & "\" & GetOutputFile(sDashedDate), FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
GoTo customExit
errorHandler:
CodeErreur = OutputFile & " - " & Err.Number & " - " & Err.Description
Debug.Print CodeErreur
Application.DisplayAlerts = False
ThisWorkbook.Close
GoTo customExit
noDataExit:
Call NoData(sDashedDate, OutputFile)
GoTo customExit
customExit:
progress 100
Progressindicator.Hide
End Sub