Question add rows to excel

[brianmac59]

I am using VBnet(VS2022), i have a program that add lines to a csv file. Everything works fine. I now need to modify it to an excel file. I have that working. My problem is, the second time the program is run it writes the new lines in the right row, but deleted the previous lines and blanks them out. Here is the code, I know I have a ton of extra lines, thats just because I have been trying everything to make it work...

        Dim path As String = "d:\PickPackAudit.xlsx"
        ' Build Excel for testing ---------------------------------------------------------------------------------- START
        Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        xlApp.DisplayAlerts = False
        Dim xlWorkBook As Excel.Workbook = xlApp.Workbooks.Add()
        Dim worksheets As Excel.Sheets = xlWorkBook.Worksheets
        Dim xlNewSheet = DirectCast(worksheets.Add(worksheets(1), Type.Missing, Type.Missing, Type.Missing), Excel.Worksheet)



        Dim Vardate As Date
        Dim Vartime As Date
        Dim VRtot As Decimal
        Dim x As Integer = 0
        Dim u As Integer = 0
        Dim z As Integer = 0    'row of excel to write to
        Dim A As Integer = 0
        Dim VRcheck As String

        Dim nextrow As Integer

        x = DataGridView1.RowCount
        Vartime = System.DateTime.Now
        xlNewSheet.Name = "Pick Audit"




        Do Until u = x
            If DataGridView1(6, u).Value > "" Then
                VRtot = DataGridView1(9, u).Value * DataGridView1(7, u).Value
                If File.Exists(path) Then

                    If A = 0 Then
                        z = xlNewSheet.UsedRange.Rows.Count + 2
                        A = 1
                    End If

                    'Write Detail
                    xlNewSheet.Cells(z, 1) = Vartime
                        xlNewSheet.Cells(z, 2) = (DataGridView1(0, u).Value)
                        xlNewSheet.Cells(z, 3) = (DataGridView1(1, u).Value)
                        xlNewSheet.Cells(z, 4) = (DataGridView1(2, u).Value)
                        xlNewSheet.Cells(z, 5) = (DataGridView1(3, u).Value)
                        xlNewSheet.Cells(z, 6) = (DataGridView1(4, u).Value)
                        xlNewSheet.Cells(z, 7) = (DataGridView1(5, u).Value)
                        xlNewSheet.Cells(z, 8) = (DataGridView1(6, u).Value)
                        xlNewSheet.Cells(z, 9) = (DataGridView1(7, u).Value)
                        xlNewSheet.Cells(z, 10) = VRtot
                        xlNewSheet.Cells(z, 11) = (DataGridView1(9, u).Value)
                    xlWorkBook.SaveAs(path)
                    z = z + 1

                    Else
                        xlNewSheet.Name = "Pick Audit"

                            'Write Heading
                            z = 1
                            xlNewSheet.Cells(z, 1) = "Date"
                            xlNewSheet.Cells(z, 2) = "SO#"
                            xlNewSheet.Cells(z, 3) = "SO Line#"
                            xlNewSheet.Cells(z, 4) = "Part"
                            xlNewSheet.Cells(z, 5) = "Desc."
                            xlNewSheet.Cells(z, 6) = "Location"
                            xlNewSheet.Cells(z, 7) = "Bin"
                            xlNewSheet.Cells(z, 8) = "Remaining"
                            xlNewSheet.Cells(z, 9) = "Price"
                            xlNewSheet.Cells(z, 10) = "Amt"
                            xlNewSheet.Cells(z, 11) = "Phy Picked"
                            z = z + 1
                            'Write Detail
                            xlNewSheet.Cells(z, 1) = Vartime
                            xlNewSheet.Cells(z, 2) = (DataGridView1(0, u).Value)
                            xlNewSheet.Cells(z, 3) = (DataGridView1(1, u).Value)
                            xlNewSheet.Cells(z, 4) = (DataGridView1(2, u).Value)
                            xlNewSheet.Cells(z, 5) = (DataGridView1(3, u).Value)
                            xlNewSheet.Cells(z, 6) = (DataGridView1(4, u).Value)
                            xlNewSheet.Cells(z, 7) = (DataGridView1(5, u).Value)
                            xlNewSheet.Cells(z, 8) = (DataGridView1(6, u).Value)
                            xlNewSheet.Cells(z, 9) = (DataGridView1(7, u).Value)
                            xlNewSheet.Cells(z, 10) = VRtot
                            xlNewSheet.Cells(z, 11) = (DataGridView1(9, u).Value)
                        'xlWorkBook.SaveAs(path)

                    End Using
                End If
            End If
            u = u + 1
        Loop
                End If
            End If
            u = u + 1
        Loop

 

[jmcilhinney]

I know I have a ton of extra lines, thats just because I have been trying everything to make it work.

Please post only the relevant code. We shouldn't have to spend our time working out what code is relevant to the issue before we can even start to work on the issue. Anything you post that isn't relevant to the problem makes it harder for us to help and thus makes it less likely you'll get the help you want.

Also, I would suggest that you look into using source control. Every developer should, no matter how new. That way, you can try some things and then simply discard your changes if something doesn't work. That way, you don't end up with lots of different layers of useless code that makes it hard for you and others to see what's actually relevant.

 

[brianmac59]

Ok, thanks for responding, I will take your advice....

So the issue is, first time through, it creates the spreadsheet, puts in header names and data. Next time the program runs, the data is put into the spreadhseet(file exists so no headers), but it deletes the previous data... So the issue is i'm no adding data to excel spreadsheet, simply writing over the top of it... I need to add data to the file.

Any help would be greatly appreciated...
Thanks,
brian

 

[brianmac59]

anyone?

 

[brianmac59]

Bueller, Bueller, Bueller....

 

[jdelano]

You can do something like

        ' open the previous Excel File
        Dim xlApp As New Excel.Application
        Dim xlWB As Excel.Workbook
        Dim xlWS As Excel.Worksheet
        Dim xlLastRow As Long
        Dim csvFile As StreamReader
        Dim csvLine() As String

        dlgGetCSV.ShowDialog()

        ' get the csv file
        If dlgGetCSV.FileName.Length = 0 Then
            Exit Sub
        End If

        csvFile = New StreamReader(dlgGetCSV.FileName)

        ' open excel file
        If File.Exists("F:\Helping people online\AppendToMe.xlsx") Then
            ' the excel file exists
            xlWB = xlApp.Workbooks.Open("F:\Helping people online\AppendToMe.xlsx")
        Else
            ' need a new file
            xlWB.SaveAs($"F:\Helping people online\Audit_{Format(Now, "MM-dd-yy")}.xlsx")
        End If

        xlWS = xlWB.Sheets("Sheet1")
        xlLastRow = xlWS.UsedRange.Rows.Count

        If xlLastRow = 0 Then
            ' this is a new file, write the header row
            xlLastRow = 1
            xlWS.Cells(1, 1) = "Title1"
            xlWS.Cells(1, 2) = "Title2"
        End If

        csvFile.ReadLine() ' skip the header row in the file

        ' loop through the csv file write the data to the worksheet
        Do While Not csvFile.EndOfStream
            csvLine = Split(csvFile.ReadLine, ",")
            xlLastRow += 1
            xlWS.Cells(xlLastRow, 1) = csvLine(0)
            xlWS.Cells(xlLastRow, 2) = csvLine(1)

        Loop

        ' close all the things
        csvFile.Close()
        xlWB.Save()
        xlWB.Close()

        xlApp.Quit()

 

[Rythorian]

Note: call subkey in load >> test()

Imports Microsoft.Office.Interop

Sub test()
    Dim ContractForm As Excel.Worksheet
    Dim InsertRow As Int32

    ContractForm = Application.ActiveWorkbook.Worksheets("Lab Contracts")
    InsertRow = 5
    ContractForm.Rows(InsertRow).Resize(8).insert() '(Excel.XlInsertShiftDirection.xlShiftDown)
End Sub