Splitting out .xlsx worksheets into seperate workbooks?

See more:

SQL

PowerShell

MS-Excel

I'm new to this, and this is out of my wheelhouse. I'm trying to come up with an automated process to ultimately load an excel spreadsheet with multiple tabs into MS SQL as individual tables. We use this for data conversion purposes.

From the initial reading I've done, I was thinking I could use PowerShell to split out the spreadsheet into individual files, but I'm getting stuck. I've browsed a few other topics on this but none of the solutions are working for me, and I haven't been able to figure it out. I've tried using .xlsx and .xls files. PowerShell 2.0.

What I have tried:

I've created a folder on my C drive called load, and that is where I'm placing the files. C:\Load\Load.xls and C:\Load\Load.ps1.

I'm pasting in the script I'm using below, and I'm attempting execute this from cmd using (I've totally borrowed all this from other posts I've seen BTW)


PowerShell.exe -command "C:\Load\Load.ps1" -filepath "C:\Load\Load.xls" -output_type "csv"

I'm getting an error saying

Method Invocation failed because [System.__ComObject] doesn't contain a method named 'SaveAs'
At C:\Load\Load.ps1 :50 char:26
+ $WorkSheet.SaveAs<<<< ($ExtractedFileName + $FileFormat)
+ CategoryInfo : Invalid Operation: (SaveAs:String) [],RuntimeException
+FullyQualifiedErrorId : MethodNotFound

this is the script in the ps1 file

[CmdletBinding()]
Param ( 
    [Parameter(Mandatory=$true,Position=0)] 
    [string]$filepath,
    [Parameter(Mandatory=$true,Position=1)] 
    [ValidateSet("csv","txt","xls","html")] 
    [string]$output_type 
)


#-----------------------------------------------------------------------------#


# Figures out and returns the 'XlFileFormat Enumeration' ID for the specified format.
# http://msdn.microsoft.com/en-us/library/office/bb241279%28v=office.12%29.aspx 
# NOTE: The code being used for 'xls' is actually a 'text' type, but it seemed
# to work the best for splitting the worksheets into separate Excel files.
function GetOutputFileFormatID 
{ 
Param([string]$fomat_name) 
    $Result = 0 
    switch($fomat_name) 
    { 
        "csv" {$Result = 6} 
        "txt" {$Result = 20} 
        "xls" {$Result = 21} 
        "html" {$Result = 44} 
        default {$Result = 51} 
    } 
    
    return $Result 
}

#-----------------------------------------------------------------------------# 
$Excel = New-Object -ComObject "Excel.Application" 
$Excel.Visible = $false #Runs Excel in the background. 
$Excel.DisplayAlerts = $false #Supress alert messages. 
$Workbook = $Excel.Workbooks.open($filepath) 
#Loop through the Workbook and extract each Worksheet
#     in the specified file type. if ($Workbook.Worksheets.Count -gt 0) { 
    write-Output "Now processing: $WorkbookName" 
    
    $FileFormat = GetOutputFileFormatID($output_type) 
    #Strip off the Excel extension. 
    $WorkbookName = $filepath -replace ".xlsx", "" #Post 2007 extension
    $WorkbookName = $WorkbookName -replace ".xls", "" #Pre 2007 extension 
    $Worksheet = $Workbook.Worksheets.item(1) 
    foreach($Worksheet in $Workbook.Worksheets) { 
        $ExtractedFileName = $Worksheet.Name + "." + $output_type 
        $Worksheet.SaveAs($ExtractedFileName + $FileFormat) 
        write-Output "Created file: $ExtractedFileName" 
    } 

#Clean up & close the main Excel objects. 
$Workbook.Close() 
$Excel.Quit()

Any thoughts?