How to Get Cell Address from a Closed Workbook

[pgerajr]

Hello everyone,

I'm in need of getting a cell address information from a closed workbook. There is a macro that is already working and it does the job, but I have to OPEN the workbook, use the FIND function "c = ActiveSheet.Cells.Find(CurrentForm)" then "Range(c.Address).Select" and "FormRow = ActiveCell.Row" in order to get the ROW.

Does any one has a hint on how to get the row number without having to open the file?

Thank you!

 

[thinhvd]

If you close source workbook, you can't use function link to source workbook with normal excel function.
If you know ADO in VBA or Add-in A-Tools, your issue will be ok

 

[njimack]

You'll need to open the file but it can all be done behind the scenes. Something like this...

Sub x()
Dim wb As Workbook
Dim FormRow As Long
Application.ScreenUpdating = False
Set wb = Workbooks.Open("Path and filename go here")
With wb
    FormRow = Sheets("Sheet name goes here").Find(CurrentForm).Row
    .Close
End With
Application.ScreenUpdating = False
       
End Sub

 

[pgerajr]

Thank you for your quick reply njimack.

I have a question... If I do that for 500 values that I need to find and get the row, it will open the workbook 500 times. This would spend more time and processor, wouldn't it?

I already use something similar from what you kindly posted here.

Thank you!

You'll need to open the file but it can all be done behind the scenes. Something like this...

Sub x()
Dim wb As Workbook
Dim FormRow As Long
Application.ScreenUpdating = False
Set wb = Workbooks.Open("Path and filename go here")
With wb
    FormRow = Sheets("Sheet name goes here").Find(CurrentForm).Row
    .Close
End With
Application.ScreenUpdating = False
       
End Sub

 

[njimack]

Thank you for your quick reply njimack.

I have a question... If I do that for 500 values that I need to find and get the row, it will open the workbook 500 times. This would spend more time and processor, wouldn't it?

I already use something similar from what you kindly posted here.

Thank you!

If you're doing this for 500 values then I would go with the ADO approach suggested previously. This will import a range from a closed file without opening it - you would import this into a hidden sheet in your file, and then use your Find function on this hidden sheet.

 

[pgerajr]

I see.. I will try that then. Thank you!

If you're doing this for 500 values then I would go with the ADO approach suggested previously. This will import a range from a closed file without opening it - you would import this into a hidden sheet in your file, and then use your Find function on this hidden sheet.

 

[pgerajr]

Hello thinhvd, thank you for your reply. I'm new at excel vba but today we have everything on the internet. lol I'll search information about ADO. Thank you

If you close source workbook, you can't use function link to source workbook with normal excel function.
If you know ADO in VBA or Add-in A-Tools, your issue will be ok

 

[pgerajr]

Hello everyone,
this is what I did and it worked just fine. At first, I was having a hard time to get the data from the original XLSM file, but then I found the correct connection code and it did the work.

Thank you!

Public Sub GetData()    Dim oConn  As Object
    Dim oRS As Object
    Dim sFilename As String
     
    sFilename = "C:\Test.xlsm"
    sSQL = "SELECT * FROM [Sheet1$]"
     
    Set oConn = CreateObject("ADODB.Connection")
    
    With oConn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & sFilename & ";" & "Extended Properties=Excel 12.0"
        .Open
    End With
    
    Set oRS = CreateObject("ADODB.Recordset")
    oRS.Open oConn, adOpenForwardOnly, adLockReadOnly, adCmdText
     
    If Not oRS.EOF Then
        ActiveWorkbook.Worksheets.Add
        ActiveSheet.Name = "DB"
        ActiveSheet.Range("A1").CopyFromRecordset oRS
    Else
    End If


    oRS.Close
    Set oRS = Nothing

    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Application.DisplayAlerts = True

    
End Sub