Index Indirect Match Function on Closed Workbook

Topic

New Reply

Hello Everyone,

I am trying to write Index Indirect Match function which will match the data from the closed workbook. But its not working if I close the workbook. The formula is;

=INDEX(INDIRECT(“‘”&”[File Name]”&D1&”‘!”&”$C:$C”),MATCH(F1&G1,INDIRECT(“‘”&”[File Name]”&D1&”‘!”&”$A:$A”)&INDIRECT(“‘”&”[File Name]”&D1&”‘!”&”$B:$B”),0))

It will match the data of F1 & G1 from columns A & B of the source file (D1 as Sheet Name) and display the matched value from column C of the source file. This function is absolutely working fine if I keep the Source file open but not if it’s closed. Hope I am able to explain clearly.

Is there any other option to resolve this issue. Thanks in Advance.

Best Regards,
Abhishek

Reply | Quote

Replies

Abhishek,

To the best of my knowledge you can not access data in a closed workbook. You need code to open the workbook, do your work, then close the workbook.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks for the reply. Is there any through VBA code.

Best Regards,
Abhishek

Reply | Quote

Thanks for the reply. Is there any through VBA code.

Best Regards,
Abhishek

Yes, but it will open then close the workbook.

Google it there are several examples available.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

You CAN access data in a closed workbook.
For example, to fetch the data from say, cell [F5], on tabsheet named [Bruce], in workbook named [myworkbook.xlsx], located in folder D:someplacesubfolder26, and put this in cell [A7] in the current workbook:
..in say Excel2010, you can use this

Code:

Sub fetchDataFromClosedWorkbook()

zPath = "D:someplacesubfolder26"    '<<don't forget last backslash  character
zFile = "myworkbook.xlsx"     '<<your workbook name
zSheet = "Bruce"                  '<<your sheetname
zRng = Range("F5").Address(1, 1, xlR1C1)
zRef = "'" & zPath & "[" & zFile & "]" & zSheet & "'!" & zRng
[a7] = ExecuteExcel4Macro(zRef)
    
End Sub

..but that doesn't help with what the poster wants.

zeddy

Reply | Quote

Zeddy,

If you believe the code doesn’t open the workbook (albeit behind the scenes) I have a bridge The Brooklyn Bridge I think you might be interested in! Of course being from the other side of the pond you might not understand that reference either!

Of course maybe it uses a quantum computer to be open and not open at the same time! :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

..and there I was thinking you were going to remind me about password protected closed files!
I need to look up my old stuff to remember what’s going on.

..I’ve been to that ‘London bridge’ (took a wrong turn) when I was driving to Las Vegas I believe.
Got pulled up for speeding too.
Just doing 55mph. Over the limit. My foot was just resting on that pedal. Didn’t notice the speed really. Just the desert.
The Trooper said he had re-entry burns on his hood trying to keep up with me. $110 fine – fair enough I thought.

zeddy

Reply | Quote

Thanks Zeddy for the code. It’s fetching the data from the closed workbook. But actually I’ve to pull out the data basis on some criteria’s as mentioned.
I need to pull out the data in Column D basis on criteria’s in Column A, B & C of “Sheet1” from “Sheet2” (multiple tabs). Hope I’m able to explain. Could you please help me out. I am scratching my head from the morning but not been able to resolve it. Attaching the sheets for your reference.

Though posted another thread but I think this is the one I’m looking for.

Many Thanks in Advance.

Best Regards,
Abhishek

Reply | Quote

Hi Abhishek

..I will have another look at this tomorrow

zeddy

Reply | Quote

Really appreciate your response Zeddy. If you need be any clarifications, please let me know. Thanks.

Best Regards,
Abhishek

Reply | Quote

Hi Zeddy,

Did you get the change to look at the code please. Thanks.

Best Regards,
Abhishek

Reply | Quote

Hi Abhishek

I’m looking at it now.

zeddy

Reply | Quote

Hi Abhishek

The problem is that you can’t use INDIRECT with a closed workbook, so I’ve been looking at other ways of solving this.
With very large workbooks, there are advantages of accessing ‘closed’ workbooks rather than opening them directly.
This is OK and easy, if you ‘know’ where the closed workbook is (i.e. the folder path for the closed workbook), the name of the closed workbook, the sheet that has the data you want, and the address of the cells you want etc etc etc.
If the ‘source’ file isn’t too large, you can efficiently open the file, unseen, for temporary access to ‘grab’ the required data, and then close the file.
I’m looking at various techniques such as ADO etc
I can easily get the sheetnames from a closed workbook, so
I’ll post back when I make a little more progress.

zeddy

Reply | Quote

Thanks Zeddy for identifying multiple options to resolve the issue.

I can fix the name and location of the source file. No idea if it would be of any help. Looking ahead… Many Thanks

Best Regards,
Abhishek

Reply | Quote

Abi:

As an alternative you might try using the Data Connection function. On the Data Ribbon find and select the “Connections” Icon. In the Dialog box Select “Add” and folllow the prompts. Excel should create a new worksheet in the Open Book with the data you need and you would change your formulas to reference this sheet rather than the closed workbook.

Regards.

TD

Reply | Quote

Thanks TD for the reply. I am trying got do it but not been able to. I try to connect Sheet2 in Sheet1, but as there are two tabs in Sheet2, which one to connect?……as I need to filter the data from both the tabs.

Might not understood well. Could you please do the same on the sampled sheets attached. Thank you.

Best Regards,
Abhishek

Reply | Quote

Abhi,

Attached is your modified worksheet renamed to Master Sheet.xlsm. In cell F1, enter the complete path, including the file name:

example: C:UsersMaudibeDesktopScores.xlsx

enter the search criteria in columns A, B, and C. When all 3 criteria on a row are entered, a search for the score will initiate and pulled from the source file. If no matches are found, the user will be presented with a popup box. An error message will warn the user if the file path is not correct or the sheet for the specified month does not exist.

HTH,
Maud

45300-Master-Sheet1

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=”#006400″]’————————————–
‘DECLARE VARIABLES[/COLOR]
Dim Row As Long, ShtName As String, I As Long, result As Integer
[COLOR=”#006400″]’————————————–
‘EVALUATE IF SEARCH CRITERIA IS COMPLETED (COL A-C)[/COLOR]
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, ActiveSheet.Columns(“A:C”)) Is Nothing Then
    Row = Target.Row
    For I = 1 To 3
        If Cells(Row, I) = “” Then Exit Sub
    Next I
Else: Exit Sub
End If
[COLOR=”#006400″]’————————————–
‘RETRIEVE SCORE[/COLOR]
On Error GoTo errorhandler
Application.ScreenUpdating = False
With ThisWorkbook.ActiveSheet
Workbooks.Open Filename:=Range(“F1”)
ShtName = .Cells(Row, 1)
Sheets(ShtName).Select
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For I = 2 To LastRow
    If ActiveSheet.Cells(I, 1) = .Cells(Row, 2) And ActiveSheet.Cells(I, 2) = .Cells(Row, 3) Then
        Application.EnableEvents = False
        .Cells(Row, 4) = ActiveSheet.Cells(I, 3)
        Application.EnableEvents = True
        result = 1
    End If
Next I
If result = 0 Then MsgBox “No results found”
ActiveWorkbook.Close
Exit Sub
End With
[COLOR=”#006400″]’————————————–
‘ERROR OPENING WORKBOOK OR SELECTING WORKSHEET[/COLOR]
errorhandler:
ActiveWorkbook.Close
MsgBox “Either the workbook path/name of the destination file is not correct or” & _
       “the month does not correspond to one of its worksheets.” & Chr(32) & _
       “Please check the path or the spelling of the month”
End Sub

Reply | Quote

Abhi:

Maudibe has provided an excellent solution. However if you are still interested in “Connections” A bit more detail on getting fully connected.

Step One:

Assuming that the data can be converted to a flat Table please make sure that the first row of the needed worksheets in the closed file have each Column Name in the first row.

Step Two:
In an Open workbook on the Data Ribbon select “Connections” Click Add and create a connection to the closed workbook to the first worksheet.
Next repeat the above but this time create a connection to the second worksheet.

Step Three
On the Data Ribbon Select the “Existing Connections” Icon. A dialog box will open showing all of the current connections available. Find the connection for the first worksheet in the closed workbook. “Double Click” on the item. A new dialog box will open, Check you want a table and check you want it in a new worksheet. This should created a new worksheet with the data. Repeat for the Second Worksheet. You will now have the same data as in the close workbook. But if anyone happens to open the Closed Workbook and changes some data you can refresh you connection and the new information will be connected to your open workbook.

Lastly If all the data is imported you will have to filter the data in the Open Workbook.

Hope this helps.

TD

Reply | Quote

:DThanks everybody for the wonderful solutions. These solutions could be a great inventory for others in future.

@ Maudibe, the solution you provided is great. It’s working absolutely fine and meeting my requirements. Thank you so much.

@ Duthiet, I have not tried yours as the solution provided by Maudibe is working for me. Anyways thank you so much.

Best Regards,
Abhishek

Reply | Quote