Trying to create loop that creates new excel sheet and copies data to sheet

Question

I have a loop that populates my excel workbook, current worksheet with data that is calculated throughout the time my program is ran. My loop is supposed to check for if the current year tab exists, if it does it's supposed to write to that sheet. If not, it's supposed to create another sheet with the current year as the name of the sheet and copy over header titles that are in A1 - A65 of the worksheet right before the newly created worksheet. It does the job going to the current year sheet (if it already exists), but will not create a new sheet with teh current year and copy the cells over if the current year sheet does not exist. I've included the loop and a few lines after to see if anyone can help me spot or correct the error

   Dim excel_app As Excel.Application
    Dim workbook As Excel.Workbook
    Dim sheet_name As String
    Dim sheet As Excel.Worksheet

    Dim exeDir As New IO.FileInfo(Reflection.Assembly.GetExecutingAssembly.FullName)
    Dim xlPath = IO.Path.Combine(exeDir.DirectoryName, "Records.xlsx")

    ' Get the Excel application object.
    excel_app = New Excel.Application

    ' Make Excel visible (optional).
    excel_app.Visible = False

    ' Open the workbook.
    workbook = excel_app.Workbooks.Open(xlPath)


    'Under Construction, trying to check if current year tab exists, if not creating it
    Dim i As Integer
    Dim blnsheet As Boolean = False
    Dim yearstamp As String = _
    DateTime.Now.ToString("yyyy")

    Dim datestamp As String = _
            DateTime.Now.ToString("MMMM yyyy")

    With workbook
        For i = 1 To .Sheets.Count
            If .Sheets(i).name = yearstamp Then
                blnsheet = True
                Exit For
            End If
        Next i

        If blnsheet = False Then
            .Sheets.Add()
            With .ActiveSheet
                .name = yearstamp
                .Range("A1:A65") = .Sheets(i - 1).Range("A1:A65")
            End With
        End If
    End With

    'End of Sheet loop

    sheet_name = yearstamp
    sheet = excel_app.Worksheets(sheet_name)

    Dim ColumnCount As Long
    ColumnCount = sheet.Range("A1").CurrentRegion.Columns.Count

    For m As Integer = 0 To ColumnCount
        If sheet.Range("A1").Offset(0, m).Value = datestamp Then
            ColumnCount = m
            Exit For
        End If
    Next m

    'End Construction


    With sheet.Range("A1")
        .Offset(0, ColumnCount).Value = datestamp
        .Offset(1, ColumnCount).Value = "$" & FormatNumber(GlobalVariables.totalincome, 2)
        .Offset(2, ColumnCount).Value = "$" & FormatNumber(totalexpenses, 2)
        .Offset(3, ColumnCount).Value = "$" & FormatNumber(GlobalVariables.cellphone, 2)
        .Offset(4, ColumnCount).Value = "$" & FormatNumber(GlobalVariables.carinsurance, 2)
        .Offset(5, ColumnCount).Value = "$" & FormatNumber(GlobalVariables.healthinsurance, 2)
        .Offset(6, ColumnCount).Value = "$" & FormatNumber(GlobalVariables.therapysessions, 2)
        .Offset(7, ColumnCount).Value = "$" & FormatNumber(GlobalVariables.drappointments, 2)
        .Offset(8, ColumnCount).Value = "$" & FormatNumber(GlobalVariables.medications, 2)
        .Offset(9, ColumnCount).Value = GlobalVariables.med1name & " $" & FormatNumber(GlobalVariables.med1, 2)
        .Offset(10, ColumnCount).Value = GlobalVariables.med2name & " $" & FormatNumber(GlobalVariables.med2, 2)
        .Offset(11, ColumnCount).Value = GlobalVariables.med3name & " $" & FormatNumber(GlobalVariables.med3, 2)
        .Offset(12, ColumnCount).Value = GlobalVariables.med4name & " $" & FormatNumber(GlobalVariables.med4, 2)
        .Offset(13, ColumnCount).Value = GlobalVariables.med5name & " $" & FormatNumber(GlobalVariables.med5, 2)
        .Offset(14, ColumnCount).Value = GlobalVariables.med6name & " $" & FormatNumber(GlobalVariables.med6, 2)
        .Offset(15, ColumnCount).Value = GlobalVariables.med7name & " $" & FormatNumber(GlobalVariables.med7, 2)
        .Offset(16, ColumnCount).Value = GlobalVariables.med8name & " $" & FormatNumber(GlobalVariables.med8, 2)

Edit to add most recent progress/failed attempt

  'Under Construction, trying to check if current year tab exists, if not creating it
    Dim blnsheet As Boolean = False

    With workbook
        If .Item(yearstamp) = True Then
            blnsheet = True
        Else : blnsheet = False
        End If

        If blnsheet = False Then
            .Sheets.Add()
            .ActiveSheet.Name = yearstamp
            .ActiveSheet.Range("A1:A65") = .Sheets(yearstamp - 1).Range("A1:A65")
        End If
    End With

I'm sorry. It's just I've tried many ways to get this loop to accomplish what I've set out to do and so far nothing's worked in terms of my loop. Maybe I should rephrase, "Does anyone have any advice on how to have a loop that checks for a certain sheet, and if it doesn't exist then creating it and copying over cells from the previous sheet?" Is that a more appropriate question? — CaffeinatedMike, Jan 02 '14 at 16:56

score 1 · Answer 1 · edited May 23 '17 at 12:03

1

You can get rid of the loop entirely, I think, by finding the sheet by name. The Item property will accept a string representing the name of the sheet.

See Sheets.Item property:

Index
Type: System.Object
Required Object. The name or index number of the object.

This will return a Worksheet object. You can test to see if the return value is Nothing to determine if it exists:

Dim l_worksheets = workbook.Worksheets
Dim l_worksheet = l_worksheets("2013")
Dim l_worksheetExists = l_worksheet Is Not Nothing

A problem you might not have noticed yet (maybe you have and you are concerned with more pressing issues) is that Excel is not quitting properly when your code has finished executing, even though you are calling Quit. If you haven't noticed that yet, you will. This is because you are creating references to objects but not cleaning them up when you are done. I see several examples in your code, but to point one out specifically:

        For i = 1 To .Sheets.Count

You never clean up your Sheets COM object, so it hangs around in memory. (Because it is a COM object, it cannot be garbage collected.)

See Never use 2 dots with com objects

edited May 23 '17 at 12:03

Community

1
1

answered Jan 02 '14 at 17:04

JDB

25,172
5
72
123

`"Excel is not quitting properly"` Yes, and I believe if you check "task manager" there will be a bunch of excel processes running:p - Been there, done that! – Bjørn-Roger Kringsjå Jan 02 '14 at 17:10
If I were to use that method how could I create the new sheet and copy over the required cells if the sheet doesn't exist? As for the quit issue I have noticed it, but have not been able to figure out as to why so thank you for bringing that to my attention! Is there a way I could close them all at once or what way would you suggest I do it? – CaffeinatedMike Jan 02 '14 at 17:12
@CaffeineCoder - read the article I linked to and the articles the linked article link to. It's more than I can explain in comments. – JDB Jan 02 '14 at 17:13
@JDB - I've attempted a different approach using your suggest of the Item feature, but it still does not get the job done. I've appended the newest try above, so you can see if you see anything wrong with it or can suggest a way to make it work – CaffeinatedMike Jan 09 '14 at 22:00
I'm getting an error on the following line of the code you provided `l_worksheet Is Not Nothing`. The error states "'Is' operator does not accept operands of type 'Integer'. Operands must be reference or nullable types" – CaffeinatedMike Jan 10 '14 at 16:05
I fixed that error by replacing the line with `Dim l_worksheetExists = Not (l_worksheet Is Nothing)`, but still getting the error and not having the sheet created. I'm getting the errors in the debugger as follows when I step through that loop `Step into: Stepping over non-user code 'System.Windows.Forms.Message.Create' Step into: Stepping over non-user code 'System.Windows.Forms.NativeWindow.DebuggableCallback' A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in iBudgetBuddy.exe` – CaffeinatedMike Jan 10 '14 at 16:17

Trying to create loop that creates new excel sheet and copies data to sheet

1 Answers1