VBA to create a dynamic link from a dynamic range

[Shoebird]

Good afternoon Ninja's.

My VBA is not the best and I have been searching the internet for an example that I can make work but I haven't had any luck.

What I am trying to do is link two columns that will be of variable length together. Once combined, this will be the path to a specific file and cell. I would like to have the VBA code to combine the columns and once completed provide the resultant from the specified cell address.

I know that this shouldn't be too difficult but any assistance would be appreciated.

I have included a file as an example.

To note, I have tried indirect but it is not working. I believe that the reason is that it is a share point site.

I can type in directly ='\\sharepoint\support\facop\Run%20Test\[Sheet1.xlsx]Table'!$B$3 and it pulls the information but I can't seem to get the concatenated version to work. The same is true if I use an http:// format.

Thanks in advance.

Sean



Post Moved by Moderator.

.

 

[PCosta87]

Good afternoon Ninja's.

My VBA is not the best and I have been searching the internet for an example that I can make work but I haven't had any luck.

What I am trying to do is link two columns that will be of variable length together. Once combined, this will be the path to a specific file and cell. I would like to have the VBA code to combine the columns and once completed provide the resultant from the specified cell address.

I know that this shouldn't be too difficult but any assistance would be appreciated.

I have included a file as an example.

To note, I have tried indirect but it is not working. I believe that the reason is that it is a share point site.

I can type in directly ='\\sharepoint\support\facop\Run%20Test\[Sheet1.xlsx]Table'!$B$3 and it pulls the information but I can't seem to get the concatenated version to work. The same is true if I use an http:// format.

Thanks in advance.

Sean

Post Moved by Moderator.

.

Hi Sean

Just noticed something... the direct input you mention is different from what you would get from concatenation alone since there is an extra "%201".
Is it possible that this is the issue?
If so, no VBA required... please see attached

 

[Shoebird]

Hi Sean

Just noticed something... the direct input you mention is different from what you would get from concatenation alone since there is an extra "%201".
Is it possible that this is the issue?
If so, no VBA required... please see attached

Hello PCosta.

I do see what you mean with "%201". That was a error in the construction of the example and should have been "[Sheet1.xlsx}Table'!$B$1" with the sheet numbers counting up.

I did open your attachment and unfortunately, I see "\\sharepoint\support\facop\Run%20Test\[Sheet.xlsx]Table'!$B$3 in Column D instead of the result of this location.

If I use indirect on my local files, it works great. When I either http:// or UNC structured locations, I get #REF! errors.

Thank you very much PCosta for your assistance though.

 

[PCosta87]

Hello PCosta.

I do see what you mean with "%201". That was a error in the construction of the example and should have been "[Sheet1.xlsx}Table'!$B$1" with the sheet numbers counting up.

I did open your attachment and unfortunately, I see "\\sharepoint\support\facop\Run%20Test\[Sheet.xlsx]Table'!$B$3 in Column D instead of the result of this location.

If I use indirect on my local files, it works great. When I either http:// or UNC structured locations, I get #REF! errors.

Thank you very much PCosta for your assistance though.

I'm sorry, it was never meant to return the result of the location... I must have misunderstood, sorry about that. I thought you were having trouble creating the link from both columns.

Since you are getting data from sharepoint, why not use a query?
It will return a table you can update at any time.
I believe that if you export from sharepoint to excel it will automatically create a query (no need to write it in SQL)

 

[Shoebird]

I'm sorry, it was never meant to return the result of the location... I must have misunderstood, sorry about that. I thought you were having trouble creating the link from both columns.

Since you are getting data from sharepoint, why not use a query?
It will return a table you can update at any time.
I believe that if you export from sharepoint to excel it will automatically create a query (no need to write it in SQL)

Brilliant suggestion PCosta.

I have done a Power Query request for files and I can only get to the file names but I can't extract the tables imbedded in the files (.xltx, .xlsx, .xlsm). That is why I was thinking that if I could do a concatenation of the file paths and name of the files, the tables are all located in the same location in the files, I could just specify the cells and extract the data that way.

If I manually create the links (which works), this is a very tedious process and is very prone to error. A VBA code that would auto detect the number of rows in a range and combine the file path with the file name, and cell location would be a clean work around.

Here is what the Power Query looks like.