Option Explicit
Dim fily, FName, FileName As String
Dim Lastrow As Long
Dim arNames(), GNames(), Names, anNames, namefile As Variant
Dim f As Integer
Dim k As Integer
Dim mes1, mes2, anno1, anno2 As Integer
Dim strFolderpath As String
Dim userFolder As String
Dim myCount, t As Integer

Sub TakeData()
'05/01/18 change name of created file with more info
Dim namefile, conexion As String
Dim j, f As Integer
userFolder = "CO2"
strFolderpath = "C:\tests\" & userFolder & "\"
Workbooks.Add
    ActiveWindow.DisplayGridlines = False
ActiveWorkbook.SaveAs FileName:=strFolderpath & "resultado\" & "CO2Summary", FileFormat:= _
    xlOpenXMLWorkbook, CreateBackup:=False
Call GuardaFicheros(strFolderpath)

For f = LBound(arNames) To UBound(arNames)
    t = t + 1
    Call MakePivs(t, (arNames(f)))
    For j = 1 To 4
    Call CopyPiv("PiTab" & t, (j), "PiTab" & (t + 1))
    t = t + 1
    Next j
'Prepare tables
Call PrepPivs(f)
Call HacerGraphs
namefile = Replace(Replace(arNames(f), ".txt", ""), " ", "")
Call GraphConfo((namefile)) ' confort graph
'makes graphs
Next f

End Sub
Sub GuardaFicheros(strFol As String)
'05/01/18 modified to take only txt
Dim pos As Integer
myCount = 0
FName = Dir(strFol)
pos = InStr(FName, ".txt")
Do Until FName = "" And pos <> 0
    If pos <> 0 Then
        myCount = myCount + 1
        ReDim Preserve arNames(1 To myCount)
        arNames(myCount) = FName
        FName = Dir
    Else
    End If
Loop
End Sub
Sub CopyPiv(s1 As String, j As Integer, s3 As String)
ActiveSheet.PivotTables(s1).PivotSelect "", xlDataAndLabel, True
Selection.Copy
Range("a3").Offset(0, j * 5).PasteSpecial
Range("a3").Offset(0, j * 5).Activate
ActiveCell.PivotTable.Name = s3
End Sub

Sub MakePivs(k As Integer, anNames As Variant)
Dim namefile As Variant
Dim conexion As String
userFolder = "CO2"
strFolderpath = "C:\tests\" & userFolder & "\"
namefile = Replace(Replace(anNames, ".txt", ""), " ", "")
conexion = "TEXT;" & strFolderpath & anNames
Sheets.Add
    Application.CutCopyMode = False

    With ActiveSheet.QueryTables.Add(Connection:=conexion, Destination:=Range("$A$1"))
        .Name = namefile
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 3, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
' replace . with , to adapt to different country data settings
    Columns("E:G").Select
    Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    'Convert temperature and humidity data to numbers
    Columns("E:E").Select
    Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("G:G").Select
    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    
    ActiveSheet.Name = namefile
    Range("C6").Select
    ActiveCell.FormulaR1C1 = "Day"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "Temperature"
    Range("F6").Select
    ActiveCell.FormulaR1C1 = "CO2"
    Range("G6").Select
    ActiveCell.FormulaR1C1 = "Humidity"
    Range("H6").Select
    ActiveCell.FormulaR1C1 = "weekday"
    Range("H7").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=WEEKDAY(RC[-5])"
    Range("I6").Select
    ActiveCell.FormulaR1C1 = "hour"
    Range("I7").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=HOUR(RC[-5])"
    Range("H7:I7").Select
    Lastrow = Range("b" & Rows.Count).End(xlUp).Row
    Selection.AutoFill Destination:=Range("H7:I" & Lastrow)
    Range("H7:I" & Lastrow).Select
    Rows("1:5").Select
    Range("A5").Activate
    Selection.Delete Shift:=xlUp
    Columns("A:B").Select
    Range("B1").Activate
    Selection.Delete Shift:=xlToLeft
    
        Columns("E:E").Select
    Selection.TextToColumns Destination:=Range("E1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), DecimalSeparator:=",", ThousandsSeparator:=".", _
        TrailingMinusNumbers:=True
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), DecimalSeparator:=",", ThousandsSeparator:=".", _
        TrailingMinusNumbers:=True
    'humidity and temperature graphs
    
    Cells.Select
    ActiveSheet.Range("$A$1:$I$" & Lastrow).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
        7, 8, 9), Header:=xlNo
    Range("C6").Select
    ActiveSheet.QueryTables(namefile).Delete
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$G$" & Lastrow), , xlYes).Name = _
        "TableT" & k
    Range("TableT" & k & "[#All]").Select
    Sheets.Add
    ActiveSheet.Name = "tab" & namefile
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "TableT" & k, Version:=6).CreatePivotTable TableDestination:="tab" & namefile & "!R3C1", _
        TableName:="PiTab" & k, DefaultVersion:=6
    Sheets("tab" & namefile).Select
    ActiveSheet.PivotTables("PiTab" & k).AddDataField ActiveSheet.PivotTables( _
        "PiTab" & k).PivotFields("Temperature"), "Sum of Temperature", xlSum
    ActiveSheet.PivotTables("PiTab" & k).AddDataField ActiveSheet.PivotTables( _
        "PiTab" & k).PivotFields("CO2"), "Sum of CO2", xlSum
    ActiveSheet.PivotTables("PiTab" & k).AddDataField ActiveSheet.PivotTables( _
        "PiTab" & k).PivotFields("Humidity"), "Sum of Humidity", xlSum
    With ActiveSheet.PivotTables("PiTab" & k).PivotFields("Sum of Humidity")
        .Caption = "Max of Humidity"
        .Function = xlMax
    End With
    With ActiveSheet.PivotTables("PiTab" & k).PivotFields("Sum of CO2")
        .Caption = "Max of CO2"
        .Function = xlMax
    End With
    With ActiveSheet.PivotTables("PiTab" & k).PivotFields("Sum of Temperature")
        .Caption = "Max of Temperature"
        .Function = xlMax
    End With
    With ActiveSheet.PivotTables("PiTab" & k).PivotFields("Day")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PiTab" & k).PivotFields("Day").AutoGroup
    With ActiveSheet.PivotTables("PiTab" & k).PivotFields("hour")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PiTab" & k)
        .ColumnGrand = False
        .RowGrand = False
    End With

    ActiveSheet.PivotTables("PiTab" & k).PivotFields("Day").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
    ActiveWindow.SmallScroll Down:=-87
    ActiveSheet.PivotTables("PiTab" & k).PivotFields("hour").Orientation = xlHidden
 
    ActiveWindow.DisplayGridlines = False
End Sub

Sub PrepPivs(m As Integer)

    Range("a3").Offset(0, 0).Select
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1).PivotFields("Max of CO2").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1).PivotFields("Max of Humidity").Orientation = _
        xlHidden
    With ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1).PivotFields("Day")
        .PivotItems("(blank)").Visible = False
    End With
    Range("a3").Offset(0, 5).Select
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 1).PivotFields("Max of Temperature"). _
        Orientation = xlHidden
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 1).PivotFields("Max of CO2").Orientation = _
        xlHidden
    Range("a3").Offset(0, 10).Select
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 2).PivotFields("Max of Humidity").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 2).PivotFields("Max of Temperature"). _
        Orientation = xlHidden
    With ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 1).PivotFields("Day")
        .PivotItems("(blank)").Visible = False
    End With
    ActiveWindow.LargeScroll ToRight:=1
    Range("a3").Offset(0, 15).Select
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 3).PivotFields("Max of CO2").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 3).PivotFields("Max of Humidity").Orientation = _
        xlHidden
    With ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 3).PivotFields("weekday")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 3).PivotFields("weekday").ClearAllFilters
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 3).PivotFields("weekday").CurrentPage = "(All)"
   
  'make weekends behaviour
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 3).PivotFields("weekday").CurrentPage = "(All)"
    With ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 3).PivotFields("weekday")
        .PivotItems("2").Visible = False
        .PivotItems("3").Visible = False
        .PivotItems("4").Visible = False
        .PivotItems("5").Visible = False
'        .PivotItems("6").Visible = False
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 3).PivotFields("weekday"). _
        EnableMultiplePageItems = True
    With ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 3).PivotFields("hour")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 2).PivotFields("Day").Orientation = xlHidden
       With ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 2).PivotFields("Day")
        .Orientation = xlRowField
        .Position = 1
        .PivotItems("(blank)").Visible = False
    End With
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 3).PivotFields("Day").AutoGroup
    With ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 3).PivotFields("hour")
        .Orientation = xlRowField
        .Position = 2
    End With
    Range("P4").Select
    Range("a3").Offset(0, 20).Select
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 4).PivotFields("Max of CO2").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 4).PivotFields("Max of Humidity").Orientation = _
        xlHidden
 'non working hours graph
    With ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 4).PivotFields("hour")
        .Orientation = xlRowField
        .Position = 2
        .PivotItems("(blank)").Visible = False
        'remove working hours
        .PivotItems("8").Visible = False
        .PivotItems("9").Visible = False
        .PivotItems("10").Visible = False
        .PivotItems("11").Visible = False
        .PivotItems("12").Visible = False
        .PivotItems("13").Visible = False
        .PivotItems("14").Visible = False
        .PivotItems("15").Visible = False
        .PivotItems("16").Visible = False
        .PivotItems("17").Visible = False
        .PivotItems("18").Visible = False
        .Orientation = xlRowField
        
    End With
    With ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 4).PivotFields("Day")
        .Orientation = xlRowField
        .Position = 1
    End With

    ActiveSheet.PivotTables("PiTab" & ((m - 1) * 5) + 1 + 4).PivotFields("Day").ShowDetail = False



End Sub
Sub HacerGraphs()
Dim titGraph() As Variant
Dim n As Integer
Dim Lastrow As Long
Dim ro As Range
titGraph = Array("Temperature", "Humidity", "CO2", "Weekends", "Non Working hours")
For n = 0 To 4
    Range("C3").Offset(0, (n * 5) + n).Select
'    Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C3").Offset(0, ((n * 5) + n) - 2).Select
    'Lastrow modified
    Lastrow = Range("c" & Rows.Count).Offset(0, ((n * 5) + n) - 2).End(xlUp).Row
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
    ActiveChart.ChartArea.Select
    'Graph title
    ActiveSheet.ChartObjects("Chart " & (n + 1)).Activate
    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Text = titGraph(n)
        With ActiveSheet.Shapes("Chart " & (n + 1))
            .Left = Range("C" & Lastrow + 4).Offset(0, ((n * 5) + n) - 2).Left
            .Top = Range("C" & Lastrow + 4).Offset(0, ((n * 5) + n) - 2).Top
        End With
    
Next n
End Sub

Sub GraphConfo(resy As Variant)
'Graph to compare confort conditions
Dim many As String
many = resy & "!$E:$E," & resy & "!$C:$C"
Worksheets(resy).Activate
    ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
    ActiveChart.SetSourceData Source:=Range(many)
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlValue).Select
    ActiveChart.ChartArea.Select
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 12
    ActiveChart.Axes(xlCategory).MaximumScale = 30
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MaximumScale = 100
    ActiveChart.ChartTitle.Select
    Selection.Caption = "Confort Conditions"
    With ActiveChart
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Temperature"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Humidity"
End With
    ActiveChart.Parent.Cut
    Worksheets("tab" & resy).Activate
    Range("a28").Select
    ActiveSheet.Paste
End Sub