Finding Excel Row

[DMT Dave]

Hi Guys, is there a method of finding what row number specific text is on Excel when it's open on MS One Drive ?



ie: 123 is in row 14 column B

Access to return 123 is on 14

Best Regards

[ranman256]

row# has no meaning in Access. Its relative.
if you do it in excel, do a find, then msgbox activecell.row

[DMT Dave]

Hi ranman256, thank you for your reply

The issue i am having is the file is on one drive, therefore i am trying to use

With XLWB then if i use .activeworbook.activecell for example

am i right in thinking because it's on one drive that the workbook is not active as the path is a http: web address and not a local drive ?

[Gicu]

Not really as the path should be something like C:\Users\YourUserName\SkyDrive\Documents. You can check by opening an Excel file in your OneDrive and going into code and in the debug window do debug.print Application.ActiveWorkbook.Path

Cheers,
Vlad

[DMT Dave]

Thank you Vlad, briefly how do i do this bearing in mind i am guest user ???

Much appreciated

[Gicu]

Sorry, didn't know you're guest, if you only have access via a link I don't know if you can.

[accesstos]

You do not have to deal with active objects if you want to retrieve only a value from a workbook. These belongs to the UI.
The sample below will probably give you what you want.

Public Function FindExcelRow(ByVal strWBFileSpec As String, varValue As Variant) As Long
    'A reference to the "Microsoft excel 12.0 object library" (or greater) is required
    Dim XL As Excel.Application
    Dim WB As Excel.Workbook
    
    Set XL = New Excel.Application
    XL.Visible = False
    On Error Resume Next
    Set WB = XL.Workbooks.Open(strWBFileSpec, , -1)
    If WB Is Nothing Then
        MsgBox "Workbook not found!", vbExclamation
    Else
        FindExcelRow = XL.WorksheetFunction.Match(varValue, WB.Sheets(1).UsedRange.Range("B:B"), 0)
        WB.Close False
        Set WB = Nothing
    End If
    Set XL = Nothing
End Function