[RESOLVED] Writing to a new line in Excel

[callumwk]

Hey everyone, I know this has been done a million times before, but I cant't get the thing to work.. I need to write to a new line in excel every time, so I guess it needs to read the last line then add text on the new one?

 Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Dim excel_app As New Excel.Application()
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        excel_app.Visible = False

        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop + "\Repairs.xlsx")
        oSheet = oBook.worksheets(1)

        oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row
'''TESTING AREA'''
        If CheckBox1.Checked = True Then
            oSheet.Cells(2, 1) = "Road Legal"
        End If
        If CheckBox2.Checked = True Then
            oSheet.Cells(2, 1) = "Off-Road Only"
        End If

I know this wont work, I don't know what to reference when

oSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row

finds the last row

Thanks

[.paul.]

Have a look at this:

https://msdn.microsoft.com/en-us/lib...ffice.11).aspx

[callumwk]

Have a look at this:

https://msdn.microsoft.com/en-us/lib...ffice.11).aspx

That selects a defined range? Mine needs to scan all rows?

[.paul.]

Try:

Range("A" & CStr(Range("A1").End(xlDown).Row + 1)).Select

[callumwk]

Try:

Range("A" & CStr(Range("A1").End(xlDown).Row + 1)).Select

[.paul.]

It's not System.Data.Range. Try oSheet.Range.

[callumwk]

It's not System.Data.Range. Try oSheet.Range.

Thanks, silly mistake for me, but xldown isnt declared?

Guessing thats oSheet. too (Opps)

[callumwk]

It's not System.Data.Range. Try oSheet.Range.

Next Problem! haha

[.paul.]

I think it's Excel.xlDown
Not sure, i tested it in VBA, hence the need to fully declare some of the functions and constants

[callumwk]

I think it's Excel.xlDown
Not sure, i tested it in VBA, hence the need to fully declare some of the functions and constants

excel.xldown isnt a member of Microsoft.Office.Interop.Excel

[.paul.]

Excel.XLDirection.xlDown

You can google constants to find which excel class they're in...

[callumwk]

Excel.XLDirection.xlDown

You can google constants to find which excel class they're in...

Oh Yeah... Google

[callumwk]

Excel.XLDirection.xlDown

You can google constants to find which excel class they're in...

I am still getting an unhanded error...

[.paul.]

I don't see a problem, or know how to proceed with that.
The example i gave you worked in excel 2007 vba, so it is almost definitely a conversion error.
I'll test it in vb. Might be a few minutes...

[.paul.]

Imports excel = Microsoft.Office.Interop.Excel
...
...
oSheet.Range("A" & CStr(oSheet.Range("A1").End(excel.XlDirection.xlDown).Row + 1)).Select()

[callumwk]

Imports excel = Microsoft.Office.Interop.Excel
...
...
oSheet.Range("A" & CStr(oSheet.Range("A1").End(excel.XlDirection.xlDown).Row + 1)).Select()

Thanks, but still receiving the same error

[callumwk]

Thanks, but still receiving the same error

Bump

[callumwk]

Thanks, but still receiving the same error

something to do with VB not having the right permissions?

[callumwk]

Updated Code:

        'Start Excel'

        Dim oXLApp As Excel.Application
        Dim oXLBook As Excel.Workbook
        Dim oXLSheet As Excel.Worksheet
        oXLApp = New Excel.Application

        'Open Exsisting Excel File'

        oXLBook = oXLApp.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop + "\Repairs.xlsx")
        oXLSheet = oXLBook.Worksheets(1)

        'Find Last Row'

        oXLSheet.Range("A" & CStr(oXLSheet.Range("A1").End(Excel.XlDirection.xlDown).Row + 1)).Select()

        'Edit Cells'

        If CheckBox1.Checked = True Then
            oXLSheet.row = "Road Legal"
        End If
        If CheckBox2.Checked = True Then
            oXLSheet.Cells(2, 1) = "Off-Road Only"
        End If


        'End Edit Cells'

        'Start Close Excel Procedure'

        oXLBook.Close(True)
        oXLSheet = Nothing
        oXLBook = Nothing
        oXLApp = Nothing

        releaseObject(oXLApp)
        releaseObject(oXLBook)
        GC.Collect()
        Threading.Thread.Sleep(1000)

        Dim xlp() As Process = Process.GetProcessesByName("EXCEL")
        For Each Process As Process In xlp
            Process.Kill()
            If Process.GetProcessesByName("EXCEL").Count = 0 Then
                Exit For
            End If
        Next

        'Excel Closed'

        'Close Form'

        Me.Close()

    End Sub

I know the closing of excel is unnecessary and crude but for now it doesn't matter. I get the same error every time.



The error is "An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in J&C Motors System 7.exe

Additional information: Exception from HRESULT: 0x800A03EC"

I don't like starting new threads all the time, but it seams when a thread gets beyond a day no one reply's :/ any help is greatly appreciated!!

[vbfbryce]

Where do you get the error? I can run it successfully through the following line:

oXLSheet.Range("A" & CStr(oXLSheet.Range("A1").End(Excel.XlDirection.xlDown).Row + 1)).Select()

[TnTinMN]

Try a little test:

Change:

'Find Last Row'

oXLSheet.Range("A" & CStr(oXLSheet.Range("A1").End(Excel.XlDirection.xlDown).Row + 1)).Select()

to:

'Find Last Row'

Dim maxRow As Int32  = oXLSheet.Rows.Count
Dim nextRow As Int32 = oXLSheet.Range("A1").End(Excel.XlDirection.xlDown).Row + 1
If nextRow > maxRow Then Stop

oXLSheet.Range("A" & CStr(oXLSheet.Range("A1").End(Excel.XlDirection.xlDown).Row + 1)).Select()

and let us know what happens.

[kareninstructor]

I have working examples here where you can run the code and see results. Granted there is a lot going on but the solution is in short what Paul is pointing too

Here is one sample

Option Strict On
Option Infer On

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Module Demo
    ''' <summary>
    ''' Writes one value to Column specificed, last rows +1
    ''' </summary>
    ''' <param name="FileName"></param>
    ''' <param name="SheetName"></param>
    ''' <param name="Column"></param>
    ''' <param name="Value"></param>
    ''' <remarks></remarks>
    Public Sub WriteToCell(ByVal FileName As String, ByVal SheetName As String, ByVal Column As String, ByVal Value As String)
        Dim LastRowCount As Integer = 1

        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing

        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(FileName)

        xlApp.Visible = False

        xlWorkSheets = xlWorkBook.Sheets

        For x As Integer = 1 To xlWorkSheets.Count

            xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

            If xlWorkSheet.Name = SheetName Then

                Dim xlCells As Excel.Range = xlWorkSheet.Cells()
                Dim xlTempRange1 As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)
                Dim xlTempRange2 = xlWorkSheet.Rows


                Dim xlTempRange3 = xlWorkSheet.Range(Column.ToUpper & xlTempRange2.Count)
                Dim xlTempRange4 = xlTempRange3.End(Excel.XlDirection.xlUp)

                LastRowCount = xlTempRange4.Row + 1

                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlCells)
                xlCells = Nothing

                xlCells = xlWorkSheet.Range(Column & LastRowCount)
                xlCells.Value = Value

                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlTempRange4)
                xlTempRange4 = Nothing

                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlTempRange3)
                xlTempRange3 = Nothing

                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlTempRange2)
                xlTempRange2 = Nothing

                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlTempRange1)
                xlTempRange1 = Nothing

                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlCells)
                xlCells = Nothing

            End If

        Next


        ' Common reason for failure, someone has the file open and we get it in read-only 
        ' mode
        Try
            xlWorkSheet.SaveAs(FileName)
        Catch ex As Exception
            MessageBox.Show("Failed to save data" & Environment.NewLine & ex.Message)
        End Try

        Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
        xlWorkSheet = Nothing

        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()

        ReleaseComObject(xlWorkSheets)
        ReleaseComObject(xlWorkSheet)
        ReleaseComObject(xlWorkBook)
        ReleaseComObject(xlWorkBooks)
        ReleaseComObject(xlApp)
    End Sub
    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            If obj IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            End If
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub
End Module

[callumwk]

Try a little test:

Change:

'Find Last Row'

oXLSheet.Range("A" & CStr(oXLSheet.Range("A1").End(Excel.XlDirection.xlDown).Row + 1)).Select()

to:

'Find Last Row'

Dim maxRow As Int32  = oXLSheet.Rows.Count
Dim nextRow As Int32 = oXLSheet.Range("A1").End(Excel.XlDirection.xlDown).Row + 1
If nextRow > maxRow Then Stop

oXLSheet.Range("A" & CStr(oXLSheet.Range("A1").End(Excel.XlDirection.xlDown).Row + 1)).Select()

and let us know what happens.

It stopped... then got the error on continue.

[TnTinMN]

It stopped... then got the error on continue.

As I suspected it would.
That means that there is no cell filled below "A1". Now why is that the case? Either you are referencing the wrong worksheet, or your data in not what you think it is.
This code is assigning the Worksheet on blind faith that the one you want is the first Worksheet in the WorkBook.

oSheet = oBook.worksheets(1)

You could also specify the WorkSheet name instead of and index.

oSheet = oBook.worksheets("NameOfWorkSheet")

Open the Workbook in Excel and find out what the first Worksheet looks like.

[callumwk]

As I suspected it would.
That means that there is no cell filled below "A1". Now why is that the case? Either you are referencing the wrong worksheet, or your data in not what you think it is.
This code is assigning the Worksheet on blind faith that the one you want is the first Worksheet in the WorkBook.

oSheet = oBook.worksheets(1)

You could also specify the WorkSheet name instead of and index.

oSheet = oBook.worksheets("NameOfWorkSheet")

Open the Workbook in Excel and find out what the first Worksheet looks like.

Your Joking! ((( I have a line of text running in the first row, the rest are empty. Add some text in tow two, it works... I'm sorry for my stupidity.

[callumwk]

As I suspected it would.
That means that there is no cell filled below "A1". Now why is that the case? Either you are referencing the wrong worksheet, or your data in not what you think it is.
This code is assigning the Worksheet on blind faith that the one you want is the first Worksheet in the WorkBook.

oSheet = oBook.worksheets(1)

You could also specify the WorkSheet name instead of and index.

oSheet = oBook.worksheets("NameOfWorkSheet")

Open the Workbook in Excel and find out what the first Worksheet looks like.

Just quickly, instead of

 oXLSheet.row

what should it be? to write into the next clear row that the previous code worked

[TnTinMN]

I'm glad you got that resolved. Now you know that you need to do some error checking when working with user prepared data files.

Assuming you left that test code in,

oXLSheet.Cells(nextRow, 1).value = something

Also, remove the "Stop" statement and flush out some error handling to put in the if statement. Have fun.

[callumwk]

I'm glad you got that resolved. Now you know that you need to do some error checking when working with user prepared data files.

Assuming you left that test code in,

oXLSheet.Cells(nextRow, 1).value = something

Also, remove the "Stop" statement and flush out some error handling to put in the if statement. Have fun.

Yeah.. stupid me really :L

I have:

        Dim maxRow As Int32 = oXLSheet.Rows.Count
        Dim nextRow As Int32 = oXLSheet.Range("A1").End(Excel.XlDirection.xlDown).Row + 1



        oXLSheet.Range("A" & CStr(oXLSheet.Range("A1").End(Excel.XlDirection.xlDown).Row + 1)).Select()

But what do I reference in this bit of code to start editing the next empty row, then a custom coloum. Ill try and Pseudocode it.

 
  If CheckBox1.Checked = True Then
  next clear row + column 2 . text = " I know what Pseudocode means (go me) "

[TnTinMN]

Do not use the "Range.Select" command. It is not needed and just slows down your code. That is something the Excel Macro recorder inserts as it has no choice because it does not know the ultimate goal. Compute a row and column and use the Worksheet.Cells(row,column) to address the needed range. Think of the Worksheet as 2D array that you are interacting with.

When in doubt about a command, refer to the Excel Object Model Reference.

[callumwk]

The Solution

 'Start Excel'
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        Dim writeRow As Long

        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Open(My.Computer.FileSystem.SpecialDirectories.Desktop + "\Repairs.xlsx")

        oSheet = oBook.worksheets(1)
        writeRow = oSheet.cells(oSheet.rows.count, 1).end(XlDirection.xlUp).row + 1

        'Edit Cells'

        If CheckBox1.Checked = True Then
            oSheet.range("a" & writeRow).value = "Road Legal"
        End If

        If CheckBox2.Checked = True Then
            oSheet.range("a" & writeRow).value = "Off-Road Only"
        End If