1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
<#
.SYNOPSIS
    This PowerShell script converts an Excel file to CSV format 
    and saves the CSV files in a specified directory.
.DESCRIPTION
    The script takes input parameters for the file path and name, 
    checks for the ImportExcel module, installs it if needed,
    opens the Excel workbook, creates a folder for CSV files, 
    iterates through each worksheet, and saves them as CSV files.
.PARAMETER filePath
    Specifies the path where the Excel file is located.
.PARAMETER fileName
    Specifies the name of the Excel file.
#>

param (
    [string]$filePath = "C:\Users\ted\Downloads\", 
    [string]$fileName = "WPP2022_Locations_notes.xls" 
)

# Get all files in the specified directory
$fileList = Get-ChildItem -Path $filePath -File

# Output the list of files
foreach ($file in $fileList) {
    Write-Host $($file.FullName -replace '^.*\\')
}
Write-Host "**************"

# Check if ImportExcel module is installed, if not, install it
if (-not (Get-Module -ListAvailable -Name ImportExcel)) {
    Import-Module ImportExcel
    Write-Host "The ImportExcel module has been installed."
}

# Build the full paths for Excel file and CSV output directory
$excelFilePath = Join-Path $filePath $fileName
$csvOutputDirectory = Join-Path $filePath ($fileName -replace '\..*$', '')

# Create Excel application object
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false

# Open the Excel workbook
$workbook = $excel.Workbooks.Open($excelFilePath)

# Create a folder for CSV files if it doesn't exist
if (-not (Test-Path -Path $csvOutputDirectory -PathType Container)) {
    New-Item -Path $csvOutputDirectory -ItemType Directory
}

# Iterate through each worksheet and save it as a CSV file
foreach ($worksheet in $workbook.Sheets) {
    $csvFileName = Join-Path $csvOutputDirectory "$($fileName -replace '\..*$', '')_$($worksheet.Name).csv"
    $worksheet.SaveAs($csvFileName, 6)  # 6 represents CSV format
}

# Close Excel application
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

Write-Host "CSV files saved to $csvOutputDirectory"