Sharepoint link to Excel via a cell variableSolved/Closed

Question

Hello, 

Configuration: Windows XP / Internet Explorer 7.0Hello, 

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

Dave122 · Accepted Answer

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

rizvisa1 · Answer

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.

Dave122 · Answer

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

Dave122 · Answer

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

Dave122 · Answer

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

Dave122 · Answer

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

Dave122 · Answer

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

Dave122 · Answer

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

Dave122 · Answer

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

Dave122 · Answer

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

Sharepoint link to Excel via a cell variable

10 responses

Similar discussions

Subscribe To Our Newsletter!