[RESOLVED] How to find (only) the row address of an Excel Cell??

[Abhilash007]

Hey guys
I am working on a project using visual basic 2005 which involves finding some data and adding it onto an existing excel file. the data in question is a set of numbers.

Actually, i have to read a bitmap (.bmp) image dimensions in pixels and display them through excel. I am new to VB and dont know how to do this. Please advise.


cheers
Abhilash

[cyberpd]

you can try the following:

Dim oExcel As New Object
        Dim oBook As Object
        Dim oSheet As Object
    
 
        'oExcel = New excel.application
        'Start a new workbook in Excel.
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.add ' The Error Occurs here
        'Create an array with 3 columns and 100 rows.
        Dim DataArray(99, 2) As Object
        Dim r As Integer
        For r = 0 To 99
            DataArray(r, 0) = "ORD" & Format(r + 1, "0000")
            DataArray(r, 1) = Rnd() * 1000
            DataArray(r, 2) = DataArray(r, 1) * 0.07
        Next
        'Add headers to the worksheet on row 1.
        oSheet = oBook.ActiveSheet
        oSheet.Range("A1").Value = "Order ID"
        oSheet.Range("B1").Value = "Amount"
        oSheet.Range("C1").Value = "Tax"
        'Transfer the array to the worksheet starting at cell A2.
        oSheet.Range("A2").Resize(100, 3).Value = DataArray
        'Save the Workbook and quit Excel.
        oBook.SaveAs("Book2.xls")
        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()

[Abhilash007]

Hey cyberpd
thx a lot


cheers
abhilash

[RobDog888]

For more info you could check the Office Development FAQ (link in my signature).

This FAQ item is of relevance to your question.
http://vbforums.com/showthread.php?t=406637

[cyberpd]

You are most Welcome.
Always visit FAQ and old RESOLVED threads for more help...

[Abhilash007]

Hey!
I am actually required to add data into an 'existing' excel file (unitemp.xls).. i think the code you gave me is one to create a new excel file and then add data onto that.. can you please gimme the code in case i need to add data to an existing file instead of creating a new file..

thx
Abhilash

[RobDog888]

Just switch this line ...
oBook = oExcel.Workbooks.add

To this line ...
oBook = oExcel.Workbooks.Open("C:\Whatever.xls")

[Abhilash007]

thx rob!! the code works just fine...


I am looking for a code in VB 2005 to search for a string in a particular column in an existing excel file. the string to be searched will be the filename of a image (ex: 00C5) w/o the extension. The code should look for the given string in the first column (col. A). the code should return the row location of the searched string.

please advise

cheers
Abhilash

[cyberpd]

abhilash..
i have already posted a code that prints a order number
one of the order number is ORD0085
i am going to find the same in the worksheet and if i find it out i print "HI"

Dim oExcel As New Object
        Dim oBook As Object
        Dim oSheet As Object
        Dim c
 
        Try
            'Start a new workbook in Excel.
            oExcel = CreateObject("Excel.Application")
            oBook = oExcel.Workbooks.open("C:\Book2.xls")
 
            With oBook
                oSheet = .Worksheets("Sheet1")
            End With
            oSheet = oBook.ActiveSheet
 
            For Each c In oSheet.Range("A2:A101")
                If c.Value = "ORD0085" Then
                    MsgBox("HI")
                End If
            Next c
 
        Catch ex As Exception
            MsgBox(ex.Message)
            oSheet = Nothing
            oBook = Nothing
            oExcel.visible = True
            oExcel = Nothing
            GC.Collect()
        End Try

[RobDog888]

Just noticed, you shouldnt be making an explicit call to the Garbage Collector.

[cyberpd]

You are right RobDog888

[Abhilash007]

Hey cyberpod...

thx for your code.. but i actually just need to find the location of the searched string (ex. A3 or A51 etc).. will it be in osheet.Range or something.. please advise


cheers
Abhilash

[RobDog888]

.Find should work for you.

[cyberpd]

the following code is an example given in Microsoft Excel Visual Basic Reference This example finds all cells in the range A1:A500 on worksheet one that contain the value 2 and changes it to 5.

With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

a little bit of modification will meet your requirement. the cell position is given by firstaddress..

[RobDog888]

Yes, as I suggested but that is the VBA code and will need re-writting in .NET code.

[Abhilash007]

hey.. thx for the code...

can anyone help me translate the above VBA code into .net code.. i rilli need it... thx


cheers
Abhilash

[RobDog888]

Integrate it with post #9 code as it contains the same converted code sections.

[Abhilash007]

Hey Rob!
I did wad u said but the address i get is a little distorted
(eg. $C$17) i used c.address.Tostring as shown below..

is it possible to remove the $ signs and just get the location.

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim oExcel As New Object
        Dim oBook As Object
        Dim oSheet As Object
        Dim c


        Try
            'Start a new workbook in Excel.
            oExcel = CreateObject("Excel.Application")
            oBook = oExcel.Workbooks.open("C:\Book2.xls")

            With (oBook)
                oSheet = .Worksheets("Sheet1")
            End With
            oSheet = oBook.ActiveSheet

            For Each c In oSheet.Range("C3:C101")
                If c.Value = "ORD0015" Then
                    MessageBox.Show("Location: " & c.address.ToString)
                End If
            Next (c)

        Catch ex As Exception
            MsgBox(ex.Message)
            oSheet = Nothing
            oBook = Nothing
            oExcel.visible = True
            oExcel = Nothing
            GC.Collect()
        End Try

    End Sub
End Class

cheers
Abhilash

[RobDog888]

Here is the help topic for Address:
http://office.microsoft.com/client/h...AiH-0&respos=3

You can use
.Address(RowAbsolute:=False, ColumnAbsolute:=False).ToString and that should give you what you want.

[Abhilash007]

Hey Rob!
That works perfect.. thx

cheers
Abhilash

[RobDog888]

No prob, glad to help. I like to try to "teach a hungry man how to fish rather then just give him a fish"

Ps, dont forget to Resolve your thread now that its solved

[Abhilash007]

Hey Guys!!

I have encountered another problem while dealing with Excel. I have a code which searches for a string in an Excel file and gives the address of the cell where the string is found. (Please refer to post #18 for the code)


I require only the row number of the cell as i need to use it later in the code.. Is there a way to get only the row number or maybe save it to a variable...


Please advise



cheers
Abhilash

[Siddharth Rout]

I require only the row number of the cell as i need to use it later in the code.. Is there a way to get only the row number or maybe save it to a variable...

Does this help? A very basic example...

Private Sub CommandButton1_Click()
 
Dim rowno As String
 
'Say the cell is A55 for which you want the Row number
aaa = Range("A55").Address
 
'Reverse Looping and Checking for "$"
For i = Len(Trim(aaa)) To 1 Step -1
 
    If Mid(aaa, i, 1) <> "$" Then
 
        rowno = rowno & Mid(aaa, i, 1)
 
    Else
        'The first "$" found from Right
        'Exit Loop
        Exit For
    End If
 
Next i
 
MsgBox rowno

End Sub

[Abhilash007]

Hey!
Your code does separate the row number but it inverts the number when storing it to the variable... for example Row number for "A65" will come out to be "56".. I am trying to find out to solve this but havent made progress yet

Please advise..

cheers
Abhilash

[Siddharth Rout]

oops...

change this

rowno = rowno & Mid(aaa, i, 1)

to

rowno = Mid(aaa, i, 1) & rowno

in the above code...