<#
.Synopsis
Combines all CSV files into a folder into an XLSX file with multiple workbooks
.DESCRIPTION
This combines all CSV files into a folder into an XLSX file with multiple workbooks. It requires Excel.
Each Worksheet is named with the basename of the file, by default with AutoFit of columns 

.PARAMETER CSVFolder
Path to folder with CSV files
.PARAMETER OutputFile
Path to the XLSX file to be created
.PARAMETER DeleteOption
DeleteFolder - deletes the entire CSVFolder
DeleteCSVFiles - deletes all CSV files within CSVFolder
.PARAMETER NoClobber
Don't delete an existing OutputFile without confirmation
.PARAMETER NoAutoFit
Don't autofit
.EXAMPLE
Combine-CSVFilesToWorksheets -CSVFolder "$env:userprofile\Desktop\CSVFolder" -OutputFile "$env:userprofile\Desktop\Combined.xlsx'"
Combine all the files in the CSVfolder on your desktop to combined.xlsx on your desktop
.NOTES
Alan Kaplan
2/1/2017
#>
Function Combine-CSVFilesToWorksheets {
    [CmdletBinding(
        SupportsShouldProcess=$true,
        ConfirmImpact='Medium')
    ]
    Param
    (
        # CSVFolder is full path to folder with CSV files
        [Parameter(Mandatory=$true,Position=0)]
        [string]$CSVFolder,
        # OutputFile is full path to combined XLSX file
        [Parameter(Mandatory=$true,Position=1)]
        [ValidateScript({Test-Path $_ -IsValid})]
        [ValidateScript({$_ -match '.xlsx'})]
        $OutputFile,
        [ValidateSet("DeleteFolder", "DeleteCSVFiles")]
        $DeleteOption,
        [switch]$NoClobber,
        [switch]$NoAutoFit
    )
Begin{
    #This is the sheet to copy, 1 is first.
    $SheetNum=1
    $xlWorksheet = -4167 
    $xlOpenXMLType = 51 # SEE: http://msdn.microsoft.com/en-us/library/bb241279.aspx

    Try{
        $XL = New-Object -ComObject Excel.Application 
    }Catch{
        Write-Warning "Failed to Load Excel, skipping convert and combine"
        Break
    }
}

Process{
    if ((Test-Path $CSVFolder) -eq $false){
        Write-Warning "$CSVFolder not found!"
        Break 
    }

    $CSVs = GCI $CSVFolder | Where {$_.Name -like "*.csv"}
    
    if ($CSVs.count -eq 0) {
        Write-Warning "No CSV files in $CSVFolder!"
        Break 
    }

    if ($PSBoundParameters['deleteoption']){
    $DeleteChoice = $PSBoundParameters.item('DeleteOption')   }

    if ($PSBoundParameters['NoClobber']){$bDontOverWrite=$true}ELSE{$bDontOverWrite = $false}

    if ($PSBoundParameters.ContainsKey('NoAutoFit')){ $bAutoFit=  $False}ELSE{$bAutofit = $True}
    
    if ($bDontOverWrite -eq $false){
        if (test-path $OutputFile) {Remove-Item $OutputFile}
    }    

    $wbDst = $XL.Workbooks.Add($xlWorksheet)
    foreach($CSV in ($CSVs).FullName){
            Write-Verbose "Adding $csv to $OutputFile"
            $wbSrc = $XL.Workbooks.Open($CSV)   
            $wsSrc = $wbSrc.Worksheets.item($SheetNum)
            if ($bAutoFit){$wsSrc.UsedRange.Columns.Autofit() | Out-Null}
            $wsSrc.Copy($wbDst.Worksheets.item($wbDst.Worksheets.Count))
            $wbSrc.Close($False)
    }

    #Delete the extra workbook
    $wbDst.Worksheets.item($wbDst.Worksheets.Count).Delete()
    $XL.ActiveWorkBook.SaveAs($OutputFile,$xlOpenXMLType)
}
End{
    switch ($DeleteChoice)
    {
        'DeleteFolder' {
           Remove-item $CSVFolder -Recurse 
        }

        'DeleteCSVFiles' {
            Get-ChildItem $CSVFolder -Include *.csv -Recurse | Remove-Item 
        }
        Default {}
    }

    if ($XL){
        $XL.Quit()
        while( [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)){}
        Write-Verbose "Done.  Logfile is $OutputFile"
    }
}
}