title: Insufficient memory to continue the execution of the program. Creating Xlxs from CSV
body: <p>I have a script that cycles through a folder and condenses multiple CSVs to one xlsx file with the names of the CSV as worksheets. However, when the script runs as part of a larger script it failes when it refreshes the query.</p>
  
  <pre><code>$Query.Refresh()
  </code></pre>
  
  <p>On its own the script runs fine, but when added to the larger one it fails. Can anyone advise why this is the case?</p>
  
  <p>Below is the error I get:</p>
  
  <pre>
  Insufficient memory to continue the execution of the program.
  At C:\Temp\Scripts\Shares_Complete.psm1:254 char:13
  +             $Query.Refresh()
  +             ~~~~~~~~~~~~~~~~
      + CategoryInfo          : OperationStopped: (:) [], OutOfMemoryException
      + FullyQualifiedErrorId : System.OutOfMemoryException
  </pre>
  
  <p>I have tried single csv with the same code and still the same result.</p>
  
  <pre><code>$script:SP = "C:\Temp\Servers\"
  
  $script:TP = "C:\Temp\Servers\Pc.txt"
  $script:FSCSV = "C:\Temp\Server_Shares\Server Lists\"
  $script:Message1 = "Unknown Hosts"
  $script:Message2 = "Unable to connect"
  $script:Message3 = "Unknown Errors Occurred"
  $script:Txt = ".txt"
  $script:OT = ".csv"
  $script:FSERROR1 = $FSCSV+$Message1+$OT
  $script:FSERROR2 = $FSCSV+$Message2+$OT
  $script:FSERROR3 = $FSCSV+$Message2+$OT
  
  $script:ERL3 = $E4 + "Shares_Errors_$Date.txt"
  $script:ECL1 = $E4 + "Shares_Exceptions1_$Date.txt"
  $script:ERL1 = $E4 + "Shares_Errors1_$Date.txt"
  $script:ECL3 = $E4 + "Shares_Exceptions_$Date.txt"
  
  function Excel-Write {
      if ($V -eq "1") {
          return
      }
  
      [System.GC]::Collect()
      $RD = $FSCSV + "*.csv" 
      $CsvDir = $RD 
      $Ma4 = $FSCSV + "All Server Shares for Domain $CH4"
      $csvs = dir -path $CsvDir # Collects all the .csv's from the driectory 
      $FSh = $csvs | Select-Object -First 1
      $FSh = ($FSh -Split "\\")[4]
      $FSh = $FSh -replace ".{5}$"
      $FSh
      $outputxls = "$Ma4.xlsx"
      $script:Excel = New-Object -ComObject Excel.Application
      $Excel.DisplayAlerts = $false
      $workbook = $excel.Workbooks.Add()
      # Loops through each CVS, pulling all the data from each one
      foreach ($iCsv in $csvs) {
          $script:iCsv
          $WN = ($iCsv -Split "\\")[-1]
          $WN = $WN -replace ".{4}$"
          if ($WN.Length -gt 30) {
              $WN = $WN.Substring(0, [Math]::Min($WN.Length, 20))
          }
          $Excel = New-Object -ComObject Excel.Application
          $Excel.DisplayAlerts = $false
          $Worksheet = $workbook.Worksheets.Add()
          $Worksheet.Name = $WN
  
          $TxtConnector = ("TEXT;" + $iCsv)
          $Connector = $worksheet.Querytables.Add($txtconnector,$worksheet.Range("A1"))
          $query = $Worksheet.QueryTables.Item($Connector.Name)
  
          $query.TextfileOtherDelimiter = $Excel.Application.International(5)
  
          $Query.TextfileParseType = 1
          $Query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Column.Count
          $query.AdjustColumnWidth = 1
  
          $Query.Refresh()
          $Query.Delete()
          $Worksheet.Cells.EntireColumn.AutoFit()
          $Worksheet.Rows.Item(1).Font.Bold = $true
          $Worksheet.Rows.Item(1).HorizontalAlignment = -4108
          $Worksheet.Rows.Item(1).Font.Underline = $true
          $Workbook.Save()
      }
      $Empty = $workbook.Worksheets.Item("Sheet1")
      $Empty.Delete()
      $Workbook.SaveAs($outputxls,51)
      $Workbook.Close()
      $Excel.Quit()
      $ObjForm.Close()
      Delete
  }
  </code></pre>
  
  <p>Should continue script and create the xlsx.</p>