Find Sheet name if Not exist stop and email issue

Question

Find Sheet name if Not exist stop and email issue

SSCrazy

Points: 2058

November 6, 2020 at 1:39 pm

I have a process that uses an xcel file and looks for the default Sheet1. There are times when someone sends a file and has the default with a pre-defined name. This cause the following process to error, so what I would like to do is in the process below throw an error back when it can't find "Sheet1", and stop the process, maybe email user of why it failed.

Thanks.

Function ExportWSToCSV ($excelFileName, $csvLoc)
{
    $excelFile = "F:\ftpfiles\" + $excelFileName + ".xls"
    $E = New-Object -ComObject Excel.Application
    $E.Visible = $false
    $E.DisplayAlerts = $false
    $wb = $E.Workbooks.Open($excelFile)
    foreach ($ws in $wb.Worksheets)
    {
        $n = $excelFileName + "_" + $ws.Name
        $ws.SaveAs($csvLoc + $n + ".csv", 6)
    }
    $E.Quit()
}
ExportWSToCSV -excelFileName "Frt-Load" -csvLoc "F:\ftpfiles\"

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

Looking at that chunk of powershell, I don't see where it is looking for the name "Sheet1". BUT if you did want to check for that, I would possibly have 2 foreach loops (I am not that familiar with Excel in Powershell) and a variable called something like "$Sheet1Found". In the first foreach loop, you check if $ws.Name = "Sheet1". if true, $Sheet1Found=1, otherwise continue and keep looking through the excel file. Then before your next foreach, you check if $sheet1Found is 1. if it is, then continue, otherwise send out an email and exit the function.

Something to note though - I would require the full file name in the "excelFileName" variable including the xls. The reason for this is that there are multiple types of Excel file extensions. xls, xlsm, xlsx, xlsb, etc. I would do a little bit of data validation (such as does the excel file extension start with "x", and check if $E is null in the event the excel file is corrupt before starting to do things on $E).

Just my 2 cents!

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Spiff SSCertifiable Points: 7826 More actions · Answer 3

The function you've provided doesn't do what you've described tbh. If you want to do as described I'd suggest using the ImportExcel module as it has all this functionality built in.

You can specify a worksheet name and it will throw an error if it doesn't exist and then you can wrap that in a try / catch block if you want to add handling to alert users etc.

Something like the below should do what you want - though agree with Brian that you should provide the full name of the excel file.

Function ExportWSToCSV {
Param
    (
    $excelFileName
    , $csvLoc
    , $wsName = "Sheet1"
    )

    #Requires -Modules ImportExcel
    $outPutFileName = [IO.Path]::ChangeExtension($excelFileName,".csv")

    Try {
        Import-Excel -Path $excelFileName -WorksheetName $wsName | 
            Export-Csv -Path (Join-Path -Path $csvLoc -ChildPath $outPutFileName) -NoTypeInformation -Delimiter ","
    }
    Catch {
       #Handle business
       Write-Error $_ -ErrorAction Stop
    }
}

Bruin SSCrazy Points: 2058 More actions · Answer 4

Bruin

SSCrazy

Points: 2058

December 4, 2020 at 4:13 pm

#3816991

Thanks for suggestion.