locked
Tricky cell referencing for Excel RRS feed

  • Question

  • Hi there,

    I'm an extremely green VB.net programmer - I've been at it for about twelve hours total now.  I've been meaning to learn to program for awhile now, and a project I'm working on has presented the opportunity.  Basically, I need to look at a Wikiscanner (http://wikiscanner.virgil.gr/) edit, assign it a code, and then move to the next edit.

    I already have an Excel workbook with all the edits I need, including their respective URLs (in column D).  I've also put together enough code to call up the workbook, select a sheet, and edit the appropriate cells.  Basically, this is what I'd like VB to do next:

    1.  Scan down column G until it finds  a null cell (say, G50).
    2.  Offset three cells to the left, to the column with the URLs in it (D50).
    3.  Load the URL into my form's browser.
    4.  Tell a neighboring sub that G50 is where the next code goes.

    I've got everything else in place, but these cell references are driving me crazy.  I'm at the end of my rope.  Please help!

    Here's my code, so you can see how it all will fit together.  Sorry for the length - the troublesome bits have comments:

    Public Class Form1

        Public Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        End Sub

        Public oApp As Microsoft.Office.Interop.Excel.Application
        Public oBook As Microsoft.Office.Interop.Excel.Workbook
        Public oSheet As Microsoft.Office.Interop.Excel.Worksheet
        Public oRngCode, oRngURL As Microsoft.Office.Interop.Excel.Range

        Public Sub BookButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BookButton.Click
            Dim oPath = FileLocation.Text
            Try
                oApp = New Microsoft.Office.Interop.Excel.Application
                oBook = oApp.Workbooks.Open(oPath)
                oApp.Visible = True

            Catch ex As Runtime.InteropServices.COMException
                MessageBox.Show("Error accessing Excel: " + ex.ToString())

            Catch ex As Exception
                MessageBox.Show("Error: " + ex.ToString())

            End Try
        End Sub

        Public Sub CompanyBox_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles CompanyBox.TextChanged
            oSheet = oBook.Worksheets(CompanyBox.Text)
            'Somewhere in here I tell VB to scan Column G for empty cells, then
            'offset to the URL, then plug the value into the browser
            WebBrowser1.Navigate(oRngURL.Value)
            'This is a label
            CurrentURL.Text = oRngURL.Value
        End Sub

        Public Sub CodeButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CodeButton.Click
            Try
                Dim Code As String
                Code = CodeBox.Text
                oSheet = oBook.Worksheets(CompanyBox.Text)
                'oRngCode = the appropriate cell from the sub above
                oRngCode.Value = Code

            Catch ex As Runtime.InteropServices.COMException
                MessageBox.Show("Error accessing Excel: " + ex.ToString())

            Catch ex As Exception
                MessageBox.Show("Error: " + ex.ToString())

            End Try
        End Sub
    End Class


    Thanks for any advice you can give me.


    Monday, December 31, 2007 10:20 AM