• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to create a dynamic link from a dynamic range

Shoebird

Member
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.


.
 

Attachments

Last edited by a moderator:

PCosta87

Well-Known Member
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
 

Attachments

Shoebird

Member
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

Well-Known Member
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

Member
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.

Example.PNG
 
Top