File name references

[intsoccersuperstar]

I have a series of files that are created daily and are formatted the exact same way every day. I want to create a new file that has pulls the last 5 values from a cell in this series of files.

Let's say the cell with the value I want to retrieve is at this location:

C:\Reports\Files\2015.1\[1.5.2015_data.xls]Sheetx'!$H$16

Now, I want my new excel file to be able to pull the following:

C:\Reports\Files\2015.1\[1.2.2015_data.xls]Sheetx'!$H$16
C:\Reports\Files\2014.12\[12.31.2015_data.xls]Sheetx'!$H$16
C:\Reports\Files\2014.12\[12.30.2015_data.xls]Sheetx'!$H$16
C:\Reports\Files\2014.12\[12.29.2015_data.xls]Sheetx'!$H$16

I know how to use the & and "" features to construct strings that have the correct file names depending on what the =NOW() function returns, and I know how to correct for Holidays (using the =WORKDAY function and a holiday array). I can construct a series of cells with the above strings.

My problem is that I cannot figure out how to get the strings to actually refer out of the workbook to the outside files. I had hoped that putting ' around them would, but I just get an error. Same thing happens when I try to use apostrophes in a different cell and try to refer to the cell with the string in it.

Any help would be greatly appreciated. Thanks in advance!

 

[AlphaFrog]

Excel doesn't create an external link from a concatenated string.

There is a custom User Defined function (UDF) called PULL that will link to a close file from a concatenated string.

Excel PULL Function

 

[intsoccersuperstar]

Thanks, AlphaFrog. Other than one small hiccup, this works exactly as I had hoped. The hiccup is it turns out the files (1.5.2015_data.xls, etc.) are all password protected, so I need to input the password every time the function cycles.

Fortunately all the passwords are the same, and they are short, so this function is still useful. However, if you know of a way to incorporate the password into the macro so I do not have to manually type it 5 times every time I run this, that would be a tremendous help.

 

[CharlesChuckieCharles]

this little bit of code

ActiveWorkbook.ChangeLink Name:="C:\Temp\DeleteMe1.xlsx", NewName:= _
"C:\Temp\DeleteMe.xlsx", Type:=xlExcelLinks

didn't seem to mind a password protected link

the line errors if Name:= is not the current link or NewName:= doesn't exist

 

[AlphaFrog]

the files (1.5.2015_data.xls, etc.) are all password protected, so I need to input the password every time the function cycles.

You're welcome.

I tried it with password protected files and it seemed to work without having to enter the password. So I'm not sure how yours is different?