' *******************************************************************
' 01.Export
' *******************************************************************
Sub button_Export_Plan_And_Forecast()

Dim sFileName_CSV_File_Plan, sFileName_CSV_File_Forecast As String
Dim iRow As Integer

Dim UserName As String
Dim DateExport As String
Dim Year As String
Dim Stage As String
Dim Creator As String

Dim Num As Integer
Dim CFR As String
Dim AccPL As String
Dim Project As String
Dim Service As String
Dim Tariff As String
Dim PersonnelType As String
Dim Comment As String

Dim m_21, m_22, m_11, m_12, m11, m12, m21, m22, m31, m32, m41, m42, m51, m52, m61, m62, m71, m72, m81, m82, m91, m92, m101, m102, m111, m112, m121, m122, m131, m132, m141, m142, m151, m152 As Double

With Sheets("Plan_Forecast_Worksheet")

    If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub
    
    iRow = 8
    
    sFileName_CSV_File_Plan = "\\Server_Path\Shared_Folder\CSV_File_Plan.txt"
    ' unique file number to access the file uniquely
    sFile_CSV_File_Plan = FreeFile()
    ' to check if file name exists, if not, end the program
    If (VBA.Len(VBA.Dir(sFileName_CSV_File_Plan))) = 0 Then MsgBox "There is no file to write the plan": End
    ' Open the TextFile in Output mode in order to write in something
    Open sFileName_CSV_File_Plan For Output As #sFile_CSV_File_Plan
    
    sFileName_CSV_File_Forecast = "\\Server_Path\Shared_Folder\CSV_File_Forecast.txt"
    ' unique file number to access the file uniquely
    sFile_CSV_File_Forecast = FreeFile()
    ' to check if file name exists, if not, end the program
    If (VBA.Len(VBA.Dir(sFileName_CSV_File_Forecast))) = 0 Then MsgBox "There is no file to write the forecast": End
    ' Open the TextFile in Output mode in order to write in something
    Open sFileName_CSV_File_Forecast For Output As #sFile_CSV_File_Forecast
    
    ' Loop to read one by one every
    ' non empty row and write them
    ' in the text file
    UserName = Application.UserName
    .Cells(3, 2).Value = UserName
    
    Do
        'With ActiveWorkbook.Application.ActiveSheet

            DateExport = CStr(Now())
            Year = .Cells(1, 2).Value
            Stage = .Cells(2, 2).Value
            Creator = UserName
        
            Num = .Cells(iRow, 1).Value
            CFR = .Cells(iRow, 2).Value
            AccPL = .Cells(iRow, 3).Value
            Project = .Cells(iRow, 4).Value
            Service = .Cells(iRow, 5).Value
            Tariff = .Cells(iRow, 6).Value
            PersonnelType = .Cells(iRow, 7).Value
            Comment = .Cells(iRow, 8).Value
            
            'Forecast
            m_21 = .Cells(iRow, 9).Value: m_22 = .Cells(iRow, 10).Value
            m_11 = .Cells(iRow, 11).Value: m_12 = .Cells(iRow, 12).Value
            'Plan
            m11 = .Cells(iRow, 15).Value: m12 = .Cells(iRow, 16).Value
            m21 = .Cells(iRow, 17).Value: m22 = .Cells(iRow, 18).Value
            m31 = .Cells(iRow, 19).Value: m32 = .Cells(iRow, 20).Value
            m41 = .Cells(iRow, 21).Value: m42 = .Cells(iRow, 22).Value
            m51 = .Cells(iRow, 23).Value: m52 = .Cells(iRow, 24).Value
            m61 = .Cells(iRow, 25).Value: m62 = .Cells(iRow, 26).Value
            m71 = .Cells(iRow, 27).Value: m72 = .Cells(iRow, 28).Value
            m81 = .Cells(iRow, 29).Value: m82 = .Cells(iRow, 30).Value
            m91 = .Cells(iRow, 31).Value: m92 = .Cells(iRow, 32).Value
            m101 = .Cells(iRow, 33).Value: m102 = .Cells(iRow, 34).Value
            m111 = .Cells(iRow, 35).Value: m112 = .Cells(iRow, 36).Value
            m121 = .Cells(iRow, 37).Value: m122 = .Cells(iRow, 38).Value
            m131 = .Cells(iRow, 39).Value: m132 = .Cells(iRow, 40).Value
            m141 = .Cells(iRow, 41).Value: m142 = .Cells(iRow, 42).Value
            m151 = .Cells(iRow, 43).Value: m152 = .Cells(iRow, 44).Value
        'End With
        ' Now write these data in text file in next line
        Write #sFile_CSV_File_Plan, UserName, DateExport, Year, Stage, Creator, Num, CFR, AccPL, Project, Service, Tariff, PersonnelType, Comment, m11, m12, m21, m22, m31, m32, m41, m42, m51, m52, m61, m62, m71, m72, m81, m82, m91, m92, m101, m102, m111, m112, m121, m122, m131, m132, m141, m142, m151, m152
        Write #sFile_CSV_File_Forecast, UserName, DateExport, Year, Stage, Creator, Num, CFR, AccPL, Project, Service, Tariff, PersonnelType, Comment, m_21, m_22, m_11, m_12
        
        ' go to the next row in Excel sheet
        iRow = iRow + 1
    Loop Until IsEmpty(.Cells(iRow, 2).Value)
    
    ' Close the file once all data
    ' is written in text file
    Close #sFile_CSV_File_Plan
    Close #sFile_CSV_File_Forecast
    
    MsgBox (UserName & ", export completed at " & DateExport)

End With
End Sub


' *******************************************************************
' 02.Import Plan And Forecast to TM1
' *******************************************************************
Sub button_Import_Plan_And_Forecast()
    If MsgBox("Are you sure?", vbYesNo) = vbNo Then Exit Sub
    Call Shell("\\Server_Path\tm1runti_path$\tm1runti.exe -process Load{Fct_08.CSV_File_Plan whoami=main_planner -adminhost TM1_HOST -server TM1_MODEL -user TM1_USER_WITH_REQUIRED_RIGHTS -CAMNamespace AD -passwordfile \\Server_Path\Secure_Folder_Path$\btprk.dat -passwordkeyfile \\Server_Path\Secure_Folder_Path$\btkey.dat", vbHide)
    Call Shell("\\Server_Path\tm1runti_path$\tm1runti.exe -process Load{Fct_08.CSV_File_Forecast whoami=main_planner -adminhost TM1_HOST -server TM1_MODEL -user TM1_USER_WITH_REQUIRED_RIGHTS -CAMNamespace AD -passwordfile \\Server_Path\Secure_Folder_Path$\btprk.dat -passwordkeyfile \\Server_Path\Secure_Folder_Path$\btkey.dat", vbHide)
    'MsgBox "Data loaded"
End Sub