Excel Formula: Pick file & sheet name from cell value

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,475
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends,

I am using this formula to get some contents from a closed workbook

=SUMPRODUCT('C:\OneDrive\Internal Sheets - Excel Files\Lomotex\[510.xlsm]327744'!$F$58)

I want the formula to pick the file name from lets say cell A1 and sheet name from cell A2

Any help would be appreciated

Regards,

Humayun
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi @hrayani , assuming your above sumproduct formula is in cell A1,
Excel Formula:
=+MID(A1,((FIND("[",A1)))+1,((FIND("]",A1))-(FIND("[",A1)))-6)
will extract file's name and
Excel Formula:
=+MID(A1,(FIND("]",A1))+1,((FIND("'!",A1))-(FIND("]",A1)))-1)
will extract sheet's name.
 
Upvote 0
Hi Pankaj,

Thanks for the reply.

The formula you provided is extracting values from the range but that's not what I want.

What I need is the formula to look at the cell values for the file and sheet names and the only way of doing that (to the best of my knowledge) is to use the indirect function but the drawback is that the indirect function does not work on closed workbooks.

For Example

There are multiple files in the destination folder > C:\OneDrive\Internal Sheets - Excel Files\Lomotex
And in this folder I have multiple files lets say 510, 512, 735, 265 etc
And in these file I have multiple sheets lets say 342281, 342275, 342026 etc
I want to pull values from these files

Now in the current workbook cell A1 will be having values lets say 510, 512, 735
And in the current workbook cell A2 will be have values like 342281, 342275, 342026

Now all I want from the formula is to look at this destination folder > C:\OneDrive\Internal Sheets - Excel Files\Lomotex & pick the file name from cell A1 and sheet name from cell A2 and pull the result

So as soon as the cell A1 or A2 value changes then the formula will look at that particular file and sheet to pull the result

A VBA solution is also welcome

Regards,

Regards,

Humayun
 
Upvote 0
Hi @hrayani , I don't know of any method that will extract data from a closed workbook. From VBA too, I generally automate the opening and closing of workbook at the backend so that user can't see that happening. If you want to go to that route, here's a code that might help. Try to modify the code ranges to your liking.

VBA Code:
Sub PullClosedData()
Application.ScreenUpdating = False
    Dim filePath As String
    Dim SourceWb As Workbook
    Dim TargetWb As Workbook
    Dim SourceWs As String
    Dim Sourcerng As String

    Set TargetWb = ActiveWorkbook
    SourceWs = ActiveSheet.Range("A2").Value 'example = 342281 or 342275 or 342026 etc
    Sourcerng = ActiveSheet.Range("A3").Value 'example = $F$58

    filePath = TargetWb.ActiveSheet.Range("A1").Value 'example = C:\OneDrive\Internal Sheets - Excel Files\Lomotex\510.xlsm
    Set SourceWb = Workbooks.Open(filePath)

    SourceWb.Sheets(SourceWs).Range(Sourcerng).Copy Destination:=TargetWb.ActiveSheet.Range("A5") 'this is where your value is getting copied for now. Change it to your liking.

    SourceWb.Close (False)

    MsgBox "Data Copied!"
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,582
Messages
6,114,470
Members
448,574
Latest member
bestresearch

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
Back
Top