Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Index Indirect Match Function on Closed Workbook

    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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the reply. Is there any through VBA code.

    Best Regards,
    Abhishek

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by abhi_jain80 View Post
    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
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    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:\someplace\subfolder26, and put this in cell [A7] in the current workbook:
    ..in say Excel2010, you can use this
    Code:
    Sub fetchDataFromClosedWorkbook()
    
    zPath = "D:\someplace\subfolder26\"    '<<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
    Last edited by zeddy; 2016-08-04 at 14:23.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    If you believe the code doesn't open the workbook (albeit behind the scenes) I have a 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!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    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

  8. #8
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files
    Last edited by abhi_jain80; 2016-08-08 at 08:36.

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Abhishek

    ..I will have another look at this tomorrow

    zeddy

  10. #10
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Really appreciate your response Zeddy. If you need be any clarifications, please let me know. Thanks.

    Best Regards,
    Abhishek

  11. #11
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

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

    Best Regards,
    Abhishek

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Abhishek

    I'm looking at it now.

    zeddy

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    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

  14. #14
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  15. #15
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •