How to check for different worksheet names in excel and add new in case it doesn't exist

Question

I'm exporting my data from MS Project to MS Excel (single pre-defined file with a given name all the time, for e.g. XYZ.xlsx) and want to have different worksheet in the excel for every workstream in the project. And number of workstreams can increase in future, thus I've to keep it dynamic.

As of now my code does the export, but I also want it to check if the workstream already exists, - if yes, delete all the data in that worksheet and paste the new data in XYZ file. - if no, create a new worksheet in the XYZ file and paste the data into it.

Can anyone please help as I'm on a deadline to finish it.

Code that I'm using it,

Set tsks = ThisProject.Tasks
For Each t In tsks
    If Not t Is Nothing Then
        If t.OutlineLevel > 1 Then
            If t.OutlineLevel = 2 Then
                If ExcelRowCounter > 2 Then
                    'Finish formatting the sheet we just finished
                    For i = 1 To 7
                        xlSheet.Columns(i).AutoFit
                    Next i
                End If
                'Add Excel sheet, name it and define column headers
                AppActivate ExcelAppTitle
                Set xlSheet = xlBook.Worksheets.Add
                ExcelSheetName = Left(Replace(t.Name, "&", "and"), 30)
                xlSheet.Name = ExcelSheetName
                xlSheet.Cells(1, 1).Value = "Task Name"
                xlSheet.Cells(1, 2).Value = "Duration (days)"
                xlSheet.Cells(1, 3).Value = "Start Date"
                xlSheet.Cells(1, 4).Value = "Finish Date"
                xlSheet.Cells(1, 5).Value = "Workstream Group"
                xlSheet.Cells(1, 6).Value = "% Complete"
                xlSheet.Cells(1, 7).Value = "Status"
                xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, 7)).Font.Bold = True
                ExcelRowCounter = 2
            End If

                xlSheet.Cells(ExcelRowCounter, 1).Value = t.Name
                xlSheet.Cells(ExcelRowCounter, 2).Value = t.Duration / (8 * 60)
                xlSheet.Cells(ExcelRowCounter, 3).Value = Format(t.Start, "mm/dd/yyyy")
                xlSheet.Cells(ExcelRowCounter, 4).Value = Format(t.Finish, "mm/dd/yyyy")
                xlSheet.Cells(ExcelRowCounter, 5).Value = t.Text1
                xlSheet.Cells(ExcelRowCounter, 6).Value = t.PercentComplete
                xlSheet.Cells(ExcelRowCounter, 7).Value = t.Number1
                xlSheet.Cells(ExcelRowCounter, 1).IndentLevel = 2 * (t.OutlineLevel - 2)
                If t.Summary = "True" Then
                    xlSheet.Range(xlSheet.Cells(ExcelRowCounter, 1), xlSheet.Cells(ExcelRowCounter, 6)).Font.Bold = True
                End If

                ExcelRowCounter = ExcelRowCounter + 1


        End If

    End If
Next t


For i = 1 To 7
    xlSheet.Columns(i).AutoFit
Next i

Actually I'm a newbie in VBA and wanted to ask how should I add the condition of checking if a worksheet already exists with the name of a workstream, and if not add a new worksheet with that name. — crazyfrog, Jan 23 '15 at 13:00
Okay, can you reduce your question down to that? The code you've provided seems to be largely (if not entirely) irrelevant. — rory.ap, Jan 23 '15 at 13:01

rory.ap · Answer 1 · 2015-01-23T13:10:29.473

0

Here's as simple method:

Function AddOrGetWorksheet(withName As String) As Worksheet
    Dim found As Boolean
    found = False
    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Sheets
        If (LCase(ws.Name) = LCase(withName)) Then
            found = True
            Set AddOrGetWorksheet = ws
            Exit For
        End If
    Next

    If (Not found) Then
        Set AddOrGetWorksheet = ActiveWorkbook.Sheets.Add()
        AddOrGetWorksheet.Name = withName
    End If
End Function

edited Jan 23 '15 at 13:10

answered Jan 23 '15 at 13:05

rory.ap

34,009
10
83
174

Thanks for the suggestion roryap. I know I'm quite late to reply, but I've changed my approach and not following this approach. – crazyfrog Feb 04 '15 at 14:36

How to check for different worksheet names in excel and add new in case it doesn't exist

1 Answers1