Check if Excel Sheet Exists

Hints, tips and tricks for newbies

Moderators: Dorian (MJT support), JRL

Post Reply
winstein
Pro Scripter
Posts: 84
Joined: Mon Nov 26, 2012 3:44 pm

Check if Excel Sheet Exists

Post by winstein » Fri Aug 18, 2017 7:47 am

I am experiencing a problem now. I followed the code from a previous thread (Verify Excel Sheet), but I could get the results to return "True" if the Excel sheet exists. Is there anything that I have missed out in the code?

(Ignore the MonthNumber function, because it will be used in another part of the code)

Code: Select all

VBSTART
    Function MonthNumber (cMonth)
        Select Case cMonth
            Case "JANUARY"
                MonthNumber = "01"
            Case "FEBRUARY"
                MonthNumber = "02"
            Case "MARCH"
                MonthNumber = "03"
            Case "APRIL"
                MonthNumber = "04"
            Case "MAY"
                MonthNumber = "05"
            Case "JUNE"
                MonthNumber = "06"
            Case "JULY"
                MonthNumber = "07"
            Case "AUGUST"
                MonthNumber = "08"
            Case "SEPTEMBER"
                MonthNumber = "09"
            Case "OCTOBER"
                MonthNumber = "10"
            Case "NOVEMBER"
                MonthNumber = "11"
            Case "DECEMBER"
                MonthNumber = "12"
            Case Else
                MonthNumber = cMonth
        End Select
    End Function

    Function SheetExists(sheetname)
      On Error Resume Next
      Set wSheet = xlBook.Sheets(sheetname)
      if wSheet Is Nothing Then
        SheetExists = False
      else
        SheetExists = True
      End if
      On Error Goto 0
    End Function
VBEND

//TEST
Let>CurrProgram=C:\Users\Norzil\Desktop\Safeguards Payroll\2015\JANUARY\MONTH END SALARY.xlsx
//TEST

XLOpen>CurrProgram,1,xlBook
VBEval>SheetExists(SEC),Result
XLQuit>xlBook
Thank you for reading.
PPQ

kdtrog
Junior Coder
Posts: 43
Joined: Wed Mar 28, 2007 9:36 am

Re: Check if Excel Sheet Exists

Post by kdtrog » Fri Aug 18, 2017 1:29 pm

As far as I know. You can only use "XLS" Files.

Klaus

zabros2020
Pro Scripter
Posts: 70
Joined: Sun May 03, 2009 11:49 pm
Location: AU

Re: Check if Excel Sheet Exists

Post by zabros2020 » Thu Aug 24, 2017 1:17 am

what version of MS are you on?

you could do this:

Code: Select all

//TEST
Let>CurrProgram=C:\Users\Norzil\Desktop\Safeguards Payroll\2015\JANUARY\MONTH END SALARY.xlsx
//TEST
XLOpen>CurrProgram,1,xlBook
XLGetSheetNames>%xlBook%,sNames
Position>SEC,%sNames%,1,exists
MessageModal>%exists%
'StringReplace>%sNames%,;,%CRLF%,sNames
MessageModal>%sNames%
XLQuit>xlBook

Loving MS's Capabilities!!!

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts