How to Get Cell Address from a Closed Workbook

pgerajr

New Member
Joined
Jan 11, 2017
Messages
5
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!
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

thinhvd

New Member
Joined
Jan 5, 2017
Messages
12
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
You'll need to open the file but it can all be done behind the scenes. Something like this...

Code:
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

New Member
Joined
Jan 11, 2017
Messages
5
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...

Code:
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764

ADVERTISEMENT

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

New Member
Joined
Jan 11, 2017
Messages
5
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

New Member
Joined
Jan 11, 2017
Messages
5
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

New Member
Joined
Jan 11, 2017
Messages
5
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!

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,324
Messages
5,600,956
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top