[Macro Excel] Libération d'espace mémoire ?!

Dim serverConnection As Boolean
Dim server As String
Dim user As String
Dim pwd As String
Dim homeScript As String
Dim perlHome As String

'==========================================
'==========================================

'This function create a text file containing the list of
'of sequence which have to be execute and excute them
Function getSeqList()

 'Sequence List
 Dim seqName, fileName As String
 Dim firstSeqId As Long
 Dim treatmentDate As String
 Dim prmUserValue As String
 Dim dateSplit() As String
 Dim getKshFile As String
 Dim euTest As Boolean
 Dim naTest As Boolean
 
 '1 for NA test
 '3 for EU Test
 Dim typeTest As Byte
 typeTest = 3
 
 
 
 
 Worksheets("Jobs_or_Sequences_Name").Activate
 idEmptyLastLine = Application.CountA(Columns("A:A"))
 
 Set fs = CreateObject("Scripting.FileSystemObject")
 Set seqList = fs.CreateTextFile(ThisWorkbook.Path & "\sequenceList.txt", True)
  
  
   For firstSeqId = 2 To idEmptyLastLine
     seqName = Worksheets("Jobs_or_Sequences_Name").Cells(firstSeqId, 1).Value
     fileName = Worksheets("Jobs_or_Sequences_Name").Cells(firstSeqId, 2).Value
     param2 = Worksheets("Jobs_or_Sequences_Name").Cells(firstSeqId, 3).Value
     
     seqList.WriteLine (seqName & ";" & fileName & ";" & param2 & ";")
      
 Next
 seqList.Close
 
 'Connection info
 server = Worksheets("Connection_info").servername.Value
 user = Worksheets("Connection_info").username.Value
 pwd = Worksheets("Connection_info").password.Value
 homeScript = Worksheets("Connection_info").scriptHome.Value
 perlHome = Worksheets("Connection_info").perlHomeDir.Value
 
 'Environment
 envSelected = Worksheets("Jobs_or_Sequences_Name").listRelease.Value

  
 treatmentDate = Worksheets("Jobs_or_Sequences_Name").trtDate.Value
 prmUserValue = Worksheets("Jobs_or_Sequences_Name").prmUser.Value
 
 
 getKshFile = Worksheets("Jobs_or_Sequences_Name").onlyKsh.Value
  
 If getKshFile Then
        getKshFile = "1"
  Else
        getKshFile = "0"
  End If
  
 If treatmentDate Like "##/##/####" Then
 
         dateSplit = Split(treatmentDate, "/")
         treatmentDate = dateSplit(2) & "-" & dateSplit(1) & "-" & dateSplit(0)
       
          
        'Get the selected environnement
        Set appExcel = CreateObject("Excel.Application")
        Set wbExcel = appExcel.Workbooks.Open(ThisWorkbook.Path & "\listEnvironnement.csv")
        Set wEnvlistEnvExcel = wbExcel.Worksheets(1)
        islastLine = False
        getEnv = False
        idEmptyLastLine = 2
        Do While (islastLine = False) And (getEnv = False)
           envInfo = wEnvlistEnvExcel.Cells(idEmptyLastLine, 1).Value
          
           envInfoTab = Split(envInfo, ";")
           
           If Not (envInfo Like "") Then
                currEnv = envInfoTab(0)
                envFile = envInfoTab(1)
           End If
           
           If currEnv Like envSelected Then
              getEnv = True
              
           End If
           If envInfo Like "" Then
               islastLine = True
           End If
           idEmptyLastLine = idEmptyLastLine + 1
           
         Loop
        
        
        euTest = Worksheets("Jobs_or_Sequences_Name").EU_Test.Value
        naTest = Worksheets("Jobs_or_Sequences_Name").NA_Test.Value
        
        If euTest Then
             typeTest = 3
        Else
              typeTest = 1
        End If
        
        
        
      
        
        'Run the jobs on the selected environnement
        If getEnv Then
             Shell (perlHome & "\bin\perl " & ThisWorkbook.Path & "\execAllSeqs.pl " & envFile & " " & server & " " & user & " " & pwd & " " & ThisWorkbook.Path & "\" & " " & treatmentDate & " " & prmUserValue & " " & homeScript & " " & getKshFile & " " & typeTest)
        Else
              MsgBox "The release " & envSelected & " has not been defined, please retry"
        End If
        
    
   Else
        MsgBox "Incorrect date format, please retry with a date format like dd/mm/aaaa"
  End If
  
    
    
 End Function

'==========================================
'==========================================

'This function verifies connections informations and set
'values of servername, username, password, script home directory
Function testConnection(servername, username, password, testAutoDir)
    
    Dim islastLine As Boolean
   islastLine = False
   Dim idEmptyLastLine As Integer
  
    perlHome = Worksheets("Connection_info").perlHomeDir.Value
    
    Shell (perlHome & "\bin\perl " & ThisWorkbook.Path & "\ping.pl " & servername & " " & username & " " & password & " " & testAutoDir & " " & ThisWorkbook.Path & "\")
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 10
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    
    res = readFileContent(ThisWorkbook.Path & "\ping.csv", servername)
    
       
         
    'Initialization of the environment list
    Set appExcel = CreateObject("Excel.Application")
    Set wbExcel = appExcel.Workbooks.Open(ThisWorkbook.Path & "\listEnvironnement.csv")
    Set wlistEnvExcel = wbExcel.Worksheets(1)
    islastLine = False
    idEmptyLastLine = 2
    Worksheets("Jobs_or_Sequences_Name").listRelease.Clear
    Do While islastLine = False
       envInfo = wlistEnvExcel.Cells(idEmptyLastLine, 1).Value
       envInfoTab = Split(envInfo, ";")
              
       If Not (envInfo Like "") Then
           currEnv = envInfoTab(0)
           Worksheets("Jobs_or_Sequences_Name").listRelease.AddItem currEnv
       
       Else
          islastLine = True
       End If
       idEmptyLastLine = idEmptyLastLine + 1
     Loop
    
     
    If res Then
        MsgBox "Successfully !"
        serverConnection = True
        server = servername
        user = username
        pwd = password
    End If
    
    
    
    'Delete temporary file
    Kill (ThisWorkbook.Path & "\ping.csv")
    
End Function

'==========================================
'==========================================

'Read the the result off the script ping.pl
Function readFileContent(fileName, servername) As Boolean
 
    Dim appExcel As Excel.Application 'Application Excel
    Dim wbExcel As Excel.Workbook 'Classeur Excel
    Dim wsExcel As Excel.Worksheet 'Feuille Excel
    Dim res As Boolean
    res = False
    
        
    'Ouverture de l'application
    Set appExcel = CreateObject("Excel.Application")
    'Ouverture d'un fichier Excel
    Set wbExcel = appExcel.Workbooks.Open(fileName)
    'wsExcel correspond à la première feuille du fichier
    Set wsExcel = wbExcel.Worksheets(1)
    
    pingResult = wsExcel.Cells(1, 1).Value
    
    'Servername invalid
    If pingResult Like "2" Then
          MsgBox "Cannot connect to the server " & servername
    End If
    
    'Username or password invalid
    If pingResult Like "4" Then
         MsgBox "Invalid username or password"
    End If
    
    'Servername, username and password valid
    If pingResult Like "3" Then
          res = True
    End If
     
    wbExcel.Close
    
    readFileContent = res
    
End Function

'==========================================
'==========================================
 
Function generateAllMOTL()

 Dim cubeListFileName As String
 Dim islastLine As Boolean
 islastLine = False
 
  

'List of cube that we have to generate
    Worksheets("Cube_generation").Activate
    idEmptyLastLine = Application.CountA(Columns("A:A"))
 
    Set fs1 = CreateObject("Scripting.FileSystemObject")
    Set cubeList = fs1.CreateTextFile(ThisWorkbook.Path & "\cubeList.txt", True)
    cubeListFileName = ThisWorkbook.Path & "\cubeList.txt"
  
    For firstSeqId = 2 To idEmptyLastLine
      cubeName = Worksheets("Cube_generation").Cells(firstSeqId, 1).Value
      
      cubeList.WriteLine (cubeName & ";")
    
   Next
   
   cubeList.Close
   
   'Read the file cubeList.txt
   Set appExcelCube = CreateObject("Excel.Application")
   cubeListFileName = ThisWorkbook.Path & "\cubeList.txt"
   Set wbExcelCube = appExcelCube.Workbooks.Open(cubeListFileName)
   'wsExcel correspond à la première feuille du fichier
    Set wsbExcelCube = wbExcelCube.Worksheets(1)

    idEmptyLastLine = 1
    Do While (islastLine = False)
    
      cubeInfo = wsbExcelCube.Cells(idEmptyLastLine, 1).Value
      If Not (cubeInfo Like "") Then
       
             cubeInfoTab = Split(cubeInfo, ";")
             'MsgBox "Cube = " & cubeInfoTab(0)
             generateMOTL (cubeInfoTab(0))
            
       Else
             islastLine = True
       End If
             
       idEmptyLastLine = idEmptyLastLine + 1
       
    Loop

     wbExcelCube.Close

End Function

'==========================================
'==========================================

'Generate a cube related to a specfied motl
Function generateMOTL(motlNameToFind)

    Dim islastLine, findOTL As Boolean
    islastLine = False
    findOTL = False
    Dim msgConf As Byte
    msgConf = 0
    
        
     Set appExcel = CreateObject("Excel.Application")
     
    'Search all the information related to 'motlName'
    fileName = ThisWorkbook.Path & "\listMOTL.csv"
    Set wbExcel = appExcel.Workbooks.Open(fileName)
    'wsExcel correspond à la première feuille du fichier
    Set wsbExcel = wbExcel.Worksheets(1)
       
    idEmptyLastLine = 2
    Do While ((islastLine = False) And (findOTL = False))
    
       appInfo = wsbExcel.Cells(idEmptyLastLine, 1).Value
       appInfoTab = Split(appInfo, ";")
              
       If Not (appInfo Like "") Then
           motlCurrent = appInfoTab(0)
      
           If motlNameToFind = motlCurrent Then
              findOTL = True
              
              cbsName = ThisWorkbook.Path & "\cbs\" & appInfoTab(1)
              
              
               'msgConf = MsgBox("Do you really want to generate the motl " & motlNameToFind & " ? ", vbOKCancel)
               'MsgBox "cmd.exe /K " & " " & ThisWorkbook.Path & "\loadEssAPP.bat " & " " & motlNameToFind & " " & cbsName
              
                Shell ("cmd.exe /K " & " " & ThisWorkbook.Path & "\loadEssAPP.bat " & " " & motlNameToFind & " " & cbsName)
              
              
           End If
           
       Else
          islastLine = True
       End If
       idEmptyLastLine = idEmptyLastLine + 1
    
    Loop
    
   If Not findOTL Then
      MsgBox "The following motl " & motlNameToFind & " does not exist"
   End If

End Function