none
Check if worksheet exists if yes call a function else add worksheet then call a function RRS feed

  • Question

  • Hi,

    I'm working on a school project in vba excel. I am retrieving data from SQL but to make it look neat I'm writing a function where the code will check if the worksheet "Fetch local data" is present in the workbook if it is present then it will fetch the data directly on the sheet (Fetch local data) , else it will add a worksheet and rename it to "Fetch local data" then call the function to fetch the local data. 

    The code below works fine if the worksheet is not present. But if its presentor if I re-run the code in the same workbook, it keeps adding new worksheet and then shows the exception "that name is already taken: try differnet one").
     
    Sub FecthLocal(control As IRibbonControl)
    
     Dim worksh As Integer
        Dim worksheetexists As Boolean
        worksh = Application.ThisWorkbook.Sheets.Count
        worksheetexists = False
        For x = 1 To worksh
            If Worksheets(x).Name = "Fetch local data" Then
                worksheetexists = True
                'MsgBox ("worksheetexists")
                Call fetchdb(Worksheets(x)) ' function to fetch data from local sql table, select * from dbo.students
                Exit For
            End If
        Next x
        If worksheetexists = False Then
            'Debug.Print "transformed exists"
            Worksheets.Add after:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = "Fetch local data"
            Call fetchdb(ActiveSheet)
        End If
    End Sub


    NewBInVB



    Tuesday, January 17, 2017 10:26 AM

Answers

  • Like this (you may want to rename FecthLocal to FetchLocal):

    Sub FecthLocal(control As IRibbonControl)
        Const strName = "Fetch local data"
        Dim wsh As Worksheet
        On Error Resume Next
        Set wsh = Worksheets(strName)
        On Error GoTo 0
        If wsh Is Nothing Then
            Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            wsh.Name = strName
        End If
        Call fetchdb(wsh) ' function to fetch data from local sql table
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by NewBInCoding Tuesday, January 17, 2017 11:36 AM
    Tuesday, January 17, 2017 10:53 AM

All replies

  • Like this (you may want to rename FecthLocal to FetchLocal):

    Sub FecthLocal(control As IRibbonControl)
        Const strName = "Fetch local data"
        Dim wsh As Worksheet
        On Error Resume Next
        Set wsh = Worksheets(strName)
        On Error GoTo 0
        If wsh Is Nothing Then
            Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            wsh.Name = strName
        End If
        Call fetchdb(wsh) ' function to fetch data from local sql table
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by NewBInCoding Tuesday, January 17, 2017 11:36 AM
    Tuesday, January 17, 2017 10:53 AM
  • Thanks Hans. I have spelled it correctly and added the code. It works perfect! Thanks

    NewBInVB

    Tuesday, January 17, 2017 11:36 AM