Sharepoint link to Excel via a cell variable [Solved/Closed]

Dave122 4 Posts Monday July 11, 2011Registration date July 12, 2011 Last seen - Jul 11, 2011 at 01:56 AM - Latest reply:  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
See more 

38 replies

Best answer
Dave122 4 Posts Monday July 11, 2011Registration date July 12, 2011 Last seen - Jul 12, 2011 at 01:40 AM
4
Thank you
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

Thank you, Dave122 4

Something to say? Add comment

CCM has helped 1704 users this month

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 14, 2011 at 04:02 AM
well just thought of one more way. You mentioned that if you hard code the address, it will work. The issue is making it dynamic. What if you make a custom function and get it to make it work. You may have to use some thing like Evaluate(<< string of the address>>)
I did wonder if that was the way to go. A few days ago tried, with the help of http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

So it works via a macro passing the value to a Msgbox window. But as it says in that article, you cannot use this function in a worksheet formula, don't entirely know why - tried everything to work round this.

We're so close - arrrgghhhh !!

I really appreciate your efforts to date, it's driving me to distraction.

Cheers
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 14, 2011 at 06:17 AM
Sorry Dave I kind of rushed through the article and your note above, so I might have missed some thing. There was some functions that you cannot use as WORKSHEET.FUNCTION
but (and as you know there is always a but) one can use evaluate function.
Since you mentioned that this works
='http://teamsite.company.com/corporate/[MySpreadsheet.xlsx]Sheet1'!F4

I was wondering if some this like this will work

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

   sInstring = "'http://teamsite.company.com/corporate/[MySpreadsheet.xlsx]Sheet1'!F4 "
   sOutAnswer = "A Dummy Value"
   
   On Error Resume Next
   sOutAnswer = Evaluate(sInstring)
   Err.Clear
   On Error GoTo 0
   If (sOutAnswer = "A Dummy Value") _
   Then
      MsgBox "Back to drawing board"
   Else
      MsgBox sOutAnswer
   End If
End Function
Interesting idea, hadn't thought of that. But(!), it didn't work, I'm afraid. Got 'Back to drawing board'

Nice try though!

Thanks
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 14, 2011 at 10:55 AM
Well here is what does work. Well at least worked for me with sharepoint on intranet using Win XP with office 2007


Function getValue()
   Dim sInstring                 As String
   Dim sOutAnswer                As String
   Dim sDir                      As String
   
   sDir = "Corporate"

   sInstring = "='http://teamsite.company.com/" & sDir & "/[MySpreadsheet.xlsx]Sheet1'!F4 "

   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
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 11, 2011 at 03:38 PM
0
Thank you
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
Thank you
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
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - 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)
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - 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
Thank you
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
Thank you
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
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - 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
Thank you
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
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 !!!
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - 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
Thank you
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
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - 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
Thank you
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
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - 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.
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
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - 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
Thank you
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
Thank you
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
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - 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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - 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.
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.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 29, 2011 at 06:49 AM
Good luck Sir. Thanks for teaching me few thing too.
Likewise. I learned alot from yourself. Thanks