Using a string to reference another workbook

[eoghanf17]

Hi,

I need to have cell “A1” in my current worksheet linking to a particular cell in an external workbook, called for example Sample_workbook1.xlsx.


Cell “B1” contains the location of this cell in string format. Eg ‘x:\Main_Folder\File_Storage\[<wbr>Sample_workbook1.xlsx]Sheet1’!<wbr>A1



Suppose [Sample_workbook1.xlsx]Sheet1’<wbr>!A1 contains the figure €20,000,

I want to let cell “A1” in my current worksheet to equal the value in [Sample_workbook1.xlsx]Sheet1’<wbr>!A1, i.e. €20,000

If I use the formula =b1, inside cell A1 it obviously just returns the location in string form and not 20,000.

Also,
I can’t use the Indirect formula like such because it doesn’t work for external folders


=Indirect(“‘x:\Main_Folder\<wbr>File_Storage\[Sample_<wbr>workbook1.xlsx]Sheet1’!A1”,1)


Does anyone know of a way in VBA to convert strings to formulae? (I tried using Evaluate but it only appears to work for numerical calculations)

Alternatively Is there any possible work-arounds?

I'd be greatly appreciative of any help
Thanks very much
Eoghan

 

[crazydragon84]

i don't think i fully understand the question, but can you have the macro just copy paste values and copy formats? or are you looking for a way to do it without vba?

 

[eoghanf17]

thanks for the reply Crazydragon, if the task could be done without vba then that would be great although I think it probably requires vba.

Basically I'm trying to link a cell to a value located in a different workbook. I tried using the indirect function but it only works when the external workbook is open.

Since the filepath is just a string, I cannot simply use a formula such as: ="x:\Main_Folder\<wbr style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">File_Storage\[Sample_<wbr style="color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">workbook1.xlsx]Sheet1’!A1", therefore i need to find a way to convert this string to a regular cell input so that it outputs the contents of A1 in sheet1.

I hope that clarifies things.

Thanks

 

[JLGWhiz]

Did you try

=‘x:\Main_Folder\File_Storage\[Sample_workbook1.xlsx]Sheet1’!A1

?
I don't think you need the Indirect in formula to get the value from the other workbook.

 

[eoghanf17]

Did you try

=‘x:\Main_Folder\File_Storage\[Sample_workbook1.xlsx]Sheet1’!A1

?
I don't think you need the Indirect in formula to get the value from the other workbook.

thanks for the reply, You're correct about the single quote marks, although that isn't the issue as when i was doing it in excel i had single quote marks, in a rush i just mis-typed double quote marks here in error.

Also the fact that the filename location is in string format, excel doesn't read the location and hence direct to it, excel simply sees it as a sentence. So with that in mind, Perhaps using vba, do you know if it's possible to convert this string so that it's read as a formula?


Thanks very much once again.
Eoghan

 

[JLGWhiz]

accordding to Excel conventions, that formula should return the value from a closed workbook, if entered as a formula, eg. preceded with the equal (=) sign. Provided, of course, that the file path is valid. You can only enter it as a string if you omit the equal sign.
Regards, JLG

 

[eoghanf17]

thanks for the reply. I think it must be a quirk of excel or something, because if you have the other workbook open then the indirect function which links to it will return the correct value, as soon as you close that workbook however, the function no longer works and it returns an error

Cheers,
Eoghan

 

[crazydragon84]

Indirect works. you may just need to make the auto update on external links.

Are you familiar with VBA at all? if not, it may be a bit hard to explain.

I assume you want list of values to pull in from different workbooks in column A of the main file? if so you can
1. Put in file directory of each files in column B (make sure it ends with \)
2. Put in file name in column C
3. Put in sheet name of the data in the source workbook
4. Put in the cell address of the data in the source sheet (e.g. A1, B17, etc)
5. open your vba editor (Alt + F11) and put in something like this in a new module. then run the macro.

sub pull_data()

Dim last_Row as single, fil_dir as string, fir_name as string

Last_Row = Range("B" & rows.count).end(xlup).row 'this finds the last row in column B
for each rng in range("B2:B" & last_Row) 'assumes row 1 has headers thus range starts in B2
Workbooks.open rng.value & rng.offset(0,1).value
Sheets(rng.offset(0,2).value).select
Range(rng.offset(0,3).value).copy
rng.offset(0,-1).pastespecial xlpastevalues
rng.offset(0,-1).pastespecial xlpasteformats 'copies in cell format as well
Activeworkbook.close savechanges:=false
next rng

thanks for the reply. I think it must be a quirk of excel or something, because if you have the other workbook open then the indirect function which links to it will return the correct value, as soon as you close that workbook however, the function no longer works and it returns an error

Cheers,
Eoghan

 

[eoghanf17]

Hi Apologies for the slow reply,

I ran that code and it worked perfectly for my task,
Thanks a million for your help with that, it's much appreciated!!