Sharepoint link to Excel via a cell variable

Solved/Closed
Dave122
Posts
4
Registration date
Monday July 11, 2011
Status
Member
Last seen
July 12, 2011
- Jul 11, 2011 at 01:56 AM
 Dave122 - Jul 29, 2011 at 07:10 AM
Hello,

Hello,

Wonder if any of you gurus can help with the following, as it has got me totall stumped!! I'm looking to allow a cell value to dictate a link to a spreadsheet in Sharepoint. So if Cell A1 is "Corporate", then, I need a link in cell B2 to get data from the relevant spreadsheet in Sharepoint, so something like ='//teamsite.company.com/"&A1&".xlsx[Sheet1]!F4"
But the above doesn't work.... Help!!

Anyone got any ideas ? Appreciate any help you can provide.

Thanks very much

10 replies

Dave122
Posts
4
Registration date
Monday July 11, 2011
Status
Member
Last seen
July 12, 2011
4
Jul 12, 2011 at 01:40 AM
Thank you for your response, however you can definitely get a value from a remote file(e.g. Sharepoint). The problem I have is to complete part of the path via a variable, thus:-

Works:
='http://teamsite.company.com/corporate/[MySpreadsheet.xlsx]Sheet1'!F4

What I am attempting, is to have the word corporate in the above filled in by a value from another cell. So I could have 'Corporate' or 'UK' to match the different folders in Sharepoint.

Thanks for any assistance you can provide, as this is one of those things that should be so easy, but driving me nuts!!

Cheers
4
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 12, 2011 at 06:10 AM
Ah thanks for that Info.

How about trying to use indirect
=INDIRECT("''http://teamsite.company.com/" & A1 & "/[MySpreadsheet.xlsx]Sheet1'!F4 ")
I have not tired it, so not sure if it will work.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 12, 2011 at 06:14 AM
Just occurred to be that perhaps indirect works with open workbook only. If that is the case, may be you can download and install MOREFUNC, there was some function that was able to get the value from a closed book too
0
Dave122
Posts
4
Registration date
Monday July 11, 2011
Status
Member
Last seen
July 12, 2011
4
Jul 12, 2011 at 06:45 AM
Thanks for the tips, but as you say INDIRECT only seems to work on open workbooks. Frustrating, as you'd think Microsoft must have a way round this?!!!

First I've heard of MOREFUNC, will have to look into that! However, ideally would be rolled out to various users, hence prefer to use the inbuilt functions if poss.

Sorry to set yourselves such a challenge!

Thanks for any advice you can provide.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 12, 2011 at 06:54 AM
I dont think you will have that issue with morefunc. It embeds (good or bad thing depends on your call) into the workbook. So if you pass that workbook to some user, they will still have that functionality
0
Dave122
Posts
4
Registration date
Monday July 11, 2011
Status
Member
Last seen
July 12, 2011
4
Jul 12, 2011 at 07:04 AM
Ah, okay, thought it was a separate Add-in. Can't download Morefunc from work due to Proxy restricting access to CNET! Will try at home. Must admit the Indirect.ext does look promising. Will let you know how I get on. Thanks for the quick response.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 11, 2011 at 03:38 PM
I am not sure that you can get value for a file via hyperlink. My understanding is that file need to a location that is mapped as a drive.
0
I tried this on my setup, but didn't work, so I got suspicious to understand what was differnet between out setups.

Now I think I know why. In the example I gave, (naturally I couldn't give the real paths!), the folder names didn't have spaces. But the real one I am worknig with has spaces both in the folder and in the spreadsheet itself.

So if you try your example with say the following (which better represents my real scenario), it unfortunately fails (coming back with #Value!):-

sInstring = "'http://teamsite.company.com/corporate/next level/[My New Spreadsheet.xlsx]Sheet1'!F4 "

Sorry for the bad example I provided originally.

So do you think there is a way round the spaces issue ?

Thanks
Dave
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 15, 2011 at 05:44 AM
Well actually the test link that used had space. This is what I did. I uploaded a test file to sharepoint. Then copied the link by removing all %20 (space) characters and made it a formula call to see if it works within my setup. Surprisingly it did return me the value. So then it took that string and simple transplanted in my routine and had it write the formula to the cell (may be you need to say cell(rows.count, columns.count).formula = sinstring ?)
Once that was successful, then I replaced one of the dir in the full path as a variable.

Looking at your example only difference between your and mine is that my file name itself did not had space. May be that was the issue. I have to see at work

Try to copy my steps. May be some thing will jump out. One thing that I did not pay attention then was when I initially put the formula in the cell I saw a #REF error for a sec and then it got replaced by actual value. May be there is some time delay (based on network call etc)
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 15, 2011 at 09:07 AM
Ok I just tried with space in the name and I got the value back
#value says to me that it is able to get to the file but some how there is other issue with data
0
Hmmm, now I'm really confused. I think I must be doing something really dumb....!

I've started afresh, created a new spreadsheet, and in Module1 used the following code (basically from your example earlier). So as you'll see just using the raw link, not even sDir at this stage. (pathwise, I've only changed the company name, nothing else). :-

Function getValue()
Dim sInstring As String
Dim sOutAnswer As String
Dim sDir As String

'sDir = "corporate"

sInstring = "='http://teamsites.corp.xxxxplanet.com/corporate/ICT/ICT Team Documents/Reports/[Breakdown of Users Int and Ext 090308.xls]Summary'!$E$5"

sOutAnswer = "A Dummy Value"
Cells(Rows.Count, Columns.Count) = sInstring
sOutAnswer = Cells(Rows.Count, Columns.Count)
Cells(Rows.Count, Columns.Count) = vbNullString
If (sOutAnswer = "A Dummy Value") _
Then
MsgBox "Back to drawing board"
Else
MsgBox sOutAnswer
End If
End Function

Now in one cell (F16), I've entered the following which gives the correct result (which should be All Users in Oracle):-
='http://teamsites.corp.xxxxplanet.com/corporate/ICT/ICT Team Documents/Reports/[Breakdown of Users Int and Ext 090308.xls]Summary'!$E$5

In Cell F17, I've entered the formula, =getValue() to which I get #VALUE!

Can you see where I've cocked up?!!!!! I feel I'm really gonna kick myself when we find out what it is!

Thanks for your patience on this!

Dave
0

Didn't find the answer you are looking for?

Ask a question
Okay, I must be doing something really stupid!

I started a fresh spreadsheet, created a Module1, of which I put the following code (basically your example earlier):-

Function getValue()
Dim sInstring As String
Dim sOutAnswer As String
Dim sDir As String

'sDir = "corporate"

sInstring = "='http://teamsites.corp.xxxplanet.com/corporate/ICT/ICT Team Documents/Reports/[Breakdown of Users Int and Ext 090308.xls]Summary'!$E$5"

sOutAnswer = "A Dummy Value"
Cells(Rows.Count, Columns.Count) = sInstring
sOutAnswer = Cells(Rows.Count, Columns.Count)
Cells(Rows.Count, Columns.Count) = vbNullString
If (sOutAnswer = "A Dummy Value") _
Then
MsgBox "Back to drawing board"
Else
MsgBox sOutAnswer
End If
End Function

As you can see, I've put the entire link in (even commented out sDir), I have only changed the company name, nothing else.

In cell F16, I put the following formula which provides the correct result (All users in Oracle):-

='http://teamsites.corp.xxxplanet.com/corporate/ICT/ICT Team Documents/Reports/[Breakdown of Users Int and Ext 090308.xls]Summary'!$E$5

In cell F17, I entered =getValue() which produces #VALUE!

I can't see the error???

I feel this is a dumb mistake I am making here, but can't see it.

Thanks for your patience on this one!

Dave
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 15, 2011 at 11:20 AM
If you notice I am writing the formula to a cell and reading the result back. Now this is all good when it is a stand alone call. However when it is a function that is not possible. Here is one possible way

If you have less than 7 possible dirs, one easy way might be to use an if statement
some thing like this

=IF(B3="Corporate",'http://teamsites.corp.xxxplanet.com/corporate/ICT/ICT Team Documents/Reports/[Breakdown of Users Int and Ext 090308.xls]Summary'!$E$5,IF(B3="Local",'http://teamsites.corp.xxxplanet.com/local/ICT/ICT Team Documents/Reports/[Breakdown of Users Int and Ext 090308.xls]Summary'!$E$5,""))

If you have more than 7, then you keep the function but then you have to use Worksheet_Change event. Here you would call that function. The challenge in that would be to know when to call the function and when not to call the function. Since I dont know this it is hard to tell, But there is a gist of it. In the sample code, If I change some thing in range (a2:a10) in correspoding cell I am putting the result

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim vValue           As Variant
   Dim cell             As Range
   
   If (Union(Target, Range("A2:A10")).Address <> Range("A2:A10").Address) Then Exit Sub
   
   Application.EnableEvents = False
   For Each cell In Target
      If cell.Value = vbNullString _
      Then
         vValue = vbNullString
      Else
         vValue = getValue(cell.Value)
      End If
      cell.Offset(0, 1) = vValue
   Next
   Application.EnableEvents = True
End Sub

Public Function getValue(sDir As String) As Variant

   Dim sInstring        As String
   Dim sOutAnswer       As String

   If (Trim(sDir) <> vbNullString) Then sDir = sDir & "/"
   sInstring = "='http://teamsites.corp.xxxplanet.com/" & sDir & "ICT/ICT Team Documents/Reports/[Breakdown of Users Int and Ext 090308.xls]Summary'!$E$5"
   sOutAnswer = "Unable to Read remote file"
   On Error Resume Next
   Cells(Rows.Count, Columns.Count).Formula = sInstring
   sOutAnswer = Cells(Rows.Count, Columns.Count)
   Cells(Rows.Count, Columns.Count) = vbNullString
   Err.Clear
   On Error GoTo 0
   
   getValue = sOutAnswer
End Function
0
YOU ARE A STAR!!! Still not quite sure why Getvalue doesn't work direct, but this is a great work around. Did some tweaking, so I could also select the cells I want from the remote file via another part of the spreadsheet (in this case the first row). I made A1, a count of the number of cells filled in row 1, and did a loop, thus:-

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim vValue As Variant
Dim cell As Range
Dim nextCell As Variant

If Target.Column = 1 Then

Application.EnableEvents = False
For Each cell In Target
For nextCell = 1 To Range("A1").Value
vValue = getValue(cell.Value, Range("A1").Offset(0, nextCell).Value)
cell.Offset(0, nextCell) = vValue
Next nextCell
Next
Application.EnableEvents = True
End If

End Sub

Public Function getValue(sDir As String, cellLocation As String) As Variant

Dim sInstring As String
Dim sOutAnswer As String

If (Trim(sDir) <> vbNullString) Then sDir = sDir & "/"
sInstring = "='http://teamsites.corp.xxxplanet.com/" & sDir & _
"ICT/ICT Team Documents/Reports/[Breakdown of Users Int and Ext 090308.xls]Summary'!" & cellLocation
sOutAnswer = ""
On Error Resume Next
Cells(Rows.Count, Columns.Count).Formula = sInstring
sOutAnswer = Cells(Rows.Count, Columns.Count)
Cells(Rows.Count, Columns.Count) = vbNullString
Err.Clear
On Error GoTo 0

getValue = sOutAnswer
End Function


Works brilliantly. Thanks very much. Seriously appreciate your efforts in what I consider to be a strange ommission from Microsoft.

If you're ever in Aberdeen, Scotland, I owe you a few beers!!
Cheers
Dave
0
Okay, tried to develop this a bit more by trying to get a vlookup performed within the relevant remote file. What I've done is to put certain lookup values in row 2, and paasing that value to GetValue. But even though the values are passing, the vlookup isn't working. Hopefully a much easier problem, haven't used vlookup in VBA before.

Alternatively, storing the lookup range in an array, and pull out the values as required, rather than re-reading the file each time?


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim vValue As Variant
Dim vOldValue As Variant
Dim cell As Range
Dim vNextCell As Variant
Dim cTextSearch As String

If (Union(Target, Range("A2:A500")).Address <> Range("A2:A500").Address) Then Exit Sub

Application.EnableEvents = False

For Each cell In Target

For vNextCell = 1 To Range("A1").Value

vOldValue = cell.Offset(0, vNextCell).Value
cTextSearch = Range("B2").Offset(0, vNextCell).Value
vValue = getValue(cell.Value, cTextSearch)
cell.Offset(0, vNextCell) = IIf(vValue = "", vOldValue, vValue)

Next vNextCell

Next

Application.EnableEvents = True

End Sub

Public Function getValue(sDir As String, sCellText As String) As Variant

Dim sInstring As String
Dim sOutAnswer As String

On Error Resume Next
sInstring = "'http://teamsites.corp.xxxplanet.com/corporate/ICT/ICT Team Documents/Reports/[Breakdown of Users Int and Ext 090308.xls]Summary'!$E$5:$F$10"

Cells(Rows.Count, Columns.Count).Formula = Application.VLookup(sCellText, sInstring, 2, 0)
sOutAnswer = Cells(Rows.Count, Columns.Count)
Cells(Rows.Count, Columns.Count) = vbNullString
Err.Clear
On Error GoTo 0

getValue = sOutAnswer
End Function


Thoughts,

Thanks (yet) again !!!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 18, 2011 at 01:06 PM
Can one do a VLOOKUP on a remote file ?. I tried to do that but I get error. So either it is not possible or I am missing some thing. Have you tried a standalone vlookup on a remote file ? if yes could you paste a sample call.
0
Sure can. Here's the example I used to know it's possible:-

=VLOOKUP(L2,'http://teamsites.corp.xxxplanet.com/corporate/ICT/ICT Team Documents/Reports/[Breakdown of Users Int and Ext 090308.xls]Summary'!$E$5:$F$10,2,0)

I just need to do the same thing via VBA, bearing in mind the code further up this trail...

Cheers
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 19, 2011 at 06:50 AM
For some reason it was not working for me. I will try on some other PC at work
Just looking at the code, I have doubts about this line

Cells(Rows.Count, Columns.Count).Formula = Application.VLookup(sCellText, sInstring, 2, 0)

Try some thing like this

Cells(Rows.Count, Columns.Count).Formula = "=VLookup(" & sCellText & "," & sInstring & ",2, 0)"
0
Okay, I see how you've done that now! I had to make a slight tweak as for some reason, the quotes around sCellText were left off. Modified thus:-

Cells(Rows.Count, Columns.Count).Formula = "=VLookup(""" & sCellText & """," & sInstring & ",2, 0)"

Wondering if the application.vlookup would have been slightly faster(trying to reduce any overhead), but this works fine so many thanks again. As I have to do various lookups in the said file, would loading the range into an array be much faster? Unfortunately, I would then be massively out of my depth!

Anyway, thanks for all your help.
Dave
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 19, 2011 at 08:40 AM
MY bad Dave, I never tested it. You did need those extra quotes. Makes perfect sense. About performance part, my first preference would be in this case to use some thing like this (there might be typos etc as I have not tested it)

On error resume next
X = Application.VLookup(""" & sCellText & """," & sInstring & ",2, 0)"
on error goto 0
This would have worked faster, (if is possible)

Every time you select, read or write a value to/from a cell/sheet/range, it takes a bit of time. If the object is in memory, then it is faster. In your case, I am thinking it would be even slower as we have additional layer of intranet traffic.
0
Hmmm, couldn't get this to quite work. Tried removing the quote at the end (you had an uneven number), and various other combinations of quotes and no quotes, but no success.

Even tried it direct with the http reference directly, i.e.:

X = Application.VLookup(sCellText, "'http://teamsites.corp.xxxplanet.com/corporate/ICT/ICT Team Documents/Reports/[Breakdown of Users Int and Ext 090308.xls]Summary'!$E$5:$F$10",2, 0)

Again no success. Have to put the quotes round, as it doesn't like the apostrophe on its' own. Still quite fast, but as you say this would use memory rather than cell references, which would improve things further.

Cheers
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 22, 2011 at 05:28 PM
Sorry Dave, I did not had a moment to spare to even look at this at work. Hopefully monday will be better.
0
Not a problem. In the interim, I got a little carried away with it over the past few days (things are slow here at the moment!). Have altered the code, so you can tell the code whether to do a horizontal or vertical lookup (row 2 of the spreadsheet starting at 'B'), and the range to lookup(row 1, starting at 'B'). Also have the path in A1. In A2, have the number of lookups it needs to cycle through using a basic Counta), and A3 is the row where the lookup phrases are(just to give the users a bit of flexibility if they want to add headers, etc). However(there's always a catch!), there is just one problem left. When you first open up the spreadsheet, it ignores the first entry you make in column 'A' ????!!! You have to enter it a second time for it to work. I think our original code was doing the same. I suspect error handling, but just can't quite figure it out.

The code is:-

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim cell As Range ' Cells that have changed in 'A' Column
Dim vNextCell As Variant ' Counter to sweep along Phrases
Dim sSearch As String ' Phrase to search for
Dim sDataFile As String ' Remote Excel file to search, INCLUDING Range
Dim sResult As String ' Result from vLookup in Excel file
Dim sDefaultPath As String ' Default path
Dim sPhraseStart As String ' Position to start for phrases

' Only changes within the following range will be processed
If (Union(Target, Range("A4:A500")).Address <> Range("A4:A500").Address) Or IsEmpty(Target) Then Exit Sub

' Check if user has put "/" at the end of the path or not
sDefaultPath = "'" & Range("A1").Value & IIf(Right(Range("A1").Value, 1) = "/", "[", "/[")
' Get the position where the phrases are located
sPhraseStart = "A" & Range("A3").Value

' For cells which have changed (in column 'A')
For Each cell In Target

' Lets go through each of the phrases
For vNextCell = 1 To Range("A2").Value
' Find phrase to search for
sSearch = Range(sPhraseStart).Offset(0, vNextCell).Value
' Combine the path and range
sDataFile = sDefaultPath & cell.Value & ".xlsx]" & cell.Value & "'!" & _
Range("A1").Offset(0, vNextCell).Value

' Certain cells have data stored horizontally or vertically
Cells(1, 100).Formula = "=" & Range("A2").Offset(0, vNextCell).Value & _
"Lookup(""" & sSearch & """," & sDataFile & ",2, 0)"

' Pull value into memory, for dropping back into live cell
sResult = ""
On Error Resume Next
sResult = Cells(1, 100).Value
Cells(1, 100).Formula = vbNullString
Err.Clear
If sResult <> "" Then
cell.Offset(0, vNextCell) = sResult
End If

Next vNextCell

Next

End Sub


Any thoughts?

Thanks
Dave
0
I should add that in row 2, an 'H' or a 'V' signifies whether horizontal or vertical. Thus turning the formula into vlookup or hlookup.

Cheers
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 28, 2011 at 04:06 PM
Sorry Dave, you lost me with your last 2 msgs
0
Sorry mate, I was just making it more flexible. But even if you stick with the original code you provided, you'll notice that when you first open the spreadsheet, it doesn't populate the cells when you make an entry in column 'A'. Any subsequent entries, do appear. It is just after the open that it doesn't. Not sure if it is a bug in Excel or what? I notice that the cell which temporarily holds the value does have the correct value, but when it is tranferred to the variable, it isn't transferred, which makes me think it is a bug in Excel.?

Thanks
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 29, 2011 at 04:50 AM
Sorry I was being thick here. I think the reason is the code with kick in when there is a change in the sheet. I would have thought that when you open the book it will still display what was the picture when you closed it last time. If it does not kick how about adding
Private Sub Workbook_Open()
   Application.CalculateFull
End Sub

That I believe would force full calculation of workbook
On other way might be, what if you dont keep the formula in the cell but rather paste it as values. The upside would be one less formula and downside would be complete disconnect from the other workbook so any update in that workbook would not be reflected in this.
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Jul 29, 2011 at 05:12 AM
Ok sorry for being idiot,. You dont have the formula in the first place. So my above rant was just a rant. Ok so my solutions will not work. BUT what if you reverse the logic. Instead of putting the value to the cell, you put the formula

sOutAnswer = Cells(Rows.Count, Columns.Count).Formula

Since you are using in you call to see if the return value is NULL or not. I would suggest that modify the function to pass in a parameter to hold either the value or function string (as you may see fit) and then use appropriately

Public Function getValue(sDir As String, sCellText As String, sFormula as variant) As Variant
....
Cells(Rows.Count, Columns.Count).Formula = sInstring
sOutAnswer = Cells(Rows.Count, Columns.Count)
sFormula = mid(sInstring, 2)
Cells(Rows.Count, Columns.Count) = vbNullString
....
End Functiuon

vValue = getValue(cell.Value, cTextSearch, sFormula)
if (vValue <> vbnullstring) _
then
cell.Offset(0, vNextCell).formula = "=" & sFormula
end if

I dont know if that will lead to the initial issue or not. I think it will not. I dont have access to sharepoint from home and I am very much afraid that I will not be able to look at this from work.
0
Thanks for that. Its' my last day in the office (I resigned), so like you, I won't have access to Sharepoint. Hence won't be able to try out your suggestion.

Many thanks for all your assistance over this.
0