Error Handling for Creating New Sheets Based on File Names

Hi All,

Below is code to create and name worksheets according to files in a folder:

Link: https://stackoverflow.com/questions/49509661/creating-worksheets-based-on-filenames

I needed some error handling code to help with the solution, hence this post - however, I got the solution at the link below before this question could be published..

Link: https://stackoverflow.com/questions/49514258/error-handling-for-creating-new-sheets-based-on-file-names/49514537#49514537

Apologies - I will provide the code solution once this post is published and mark as solved.
Eitel DagninIT Security AdministratorAsked:
Who is Participating?
 
Roy CoxGroup Finance ManagerCommented:
Sorry, put end with in the wrong place

Option Explicit

Sub test()

    Dim strFilename As String
    Dim strPath As String

    strPath = "D:\myPath\"
    strFilename = Dir(strPath & "*.xlsx")

    Do Until strFilename = ""
        strFilename = Split(strFilename, ".")(0)

        ' check is strFilename already exists in existing sheets
        With ThisWorkbook
            If sheetExists(strFilename) = False Then    ' doesn't exist
                .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = strFilename
            Else
                ' just raise a message box
                MsgBox "Worksheet " & strFilename & " already exists.", vbInformation
            End If
        End With
        strFilename = Dir
    Loop

End Sub

Open in new window

0
 
Roy CoxGroup Finance ManagerCommented:
Not sure whether you want an answer, but here's a Function to check if a worksheet already exists. Place it in a Stabdard Module

Function WksExists(wksName As String) As Boolean
    On Error Resume Next
    WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
   On Error GoTo 0
End Function

Open in new window


Use in code like this

   If WksExists(" whatever name here") Then
MsgBox "That name has been used"
Else
''/// code to add and name new sheet
End If

Open in new window

0
 
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Roy,

Thank you for the reply. I tested your code and applied it to my work and it worked well!

Unfortunately I wasn't able to "cancel" my question before it was published and I managed to get the solution below:

Link: https://stackoverflow.com/questions/49514258/error-handling-for-creating-new-sheets-based-on-file-names/49514537#49514537

Option Explicit

Sub test()

Dim strFilename As String
Dim strPath As String
Dim WB As Workbook

Set WB = ThisWorkbook ' define which workbook you want to add the sheets
strPath = "D:\myPath\"
strFilename = Dir(strPath & "*.xlsx")

Do Until strFilename = ""
    strFilename = Split(strFilename, ".")(0)

    ' check is strFilename already exists in existing sheets

    If sheetExists(strFilename) = False Then ' doesn't exist
        WB.Sheets.Add(After:=WB.Sheets(WB.Sheets.Count)).Name = strFilename
    Else
        ' just raise a message box
        MsgBox "Worksheet " & strFilename & " already exists.", vbInformation
    End If
    strFilename = Dir
Loop

End Sub

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Roy CoxGroup Finance ManagerCommented:
Presumably your code has a Function similar to mine. I wouldn't bother declaring a variable for ThisWorkBook, but handle it with With...End With statement.
Option Explicit

Sub test()

    Dim strFilename As String
    Dim strPath As String

    strPath = "D:\myPath\"
    strFilename = Dir(strPath & "*.xlsx")

    Do Until strFilename = ""
        strFilename = Split(strFilename, ".")(0)

        ' check is strFilename already exists in existing sheets
        With ThisWorkbook
            If sheetExists(strFilename) = False Then    ' doesn't exist
                .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = strFilename
            Else
                ' just raise a message box
                MsgBox "Worksheet " & strFilename & " already exists.", vbInformation
            End If
            strFilename = Dir
        Loop
    End With
End Sub

Open in new window


I also cannot see where myPath is declared and given a value
0
 
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Roy,

I get the Error: Loop without do?
0
 
Eitel DagninIT Security AdministratorAuthor Commented:
Hi Roy,

Thank you, this works perfectly! :)
0
 
Roy CoxGroup Finance ManagerCommented:
Pleased to help.

Actually, I've just looked at the source of your code and I think my Function is neater - it doesn't use a Loop which are best avoided because Loops generally slow the code operation. The Function that you found Loops through all the sheets in the workbook until a match is found.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.