0

I want to open an Excel file which is stored in my SharePoint under the Folder

//Documents // Design //Excel //items.xlsx

I searched through Internet and came up with the below code. The Problem with the code is it is opening the "File open " Dialogue box in the local drive.

Can anyone, suggest me a code that I could work with and could open an Excel file from SharePoint

Here is what i tried, with the previous example tried in the Forum 

Sub Share()
Dim S As Workbook
Dim WB As Variant

With Application.FileDialog(msoFileDialogOpen)
.InitialFileName = "https://Sharepoint.de/Content/0030/default.aspx" & "/RootFolder=%2Fcontent%2F00008200%2FTeam%20Documents%2F02%20%2D%20Design%2F0001%20Design%2FExcel&FolderCTID=0x01200083BC38D90EC5674491B520CC48282737&View={28035ED9-59EF-42BE-BA4B-A36193C54539}&InitialTabId=Ribbon%2EDocument&VisibilityContext=WSSTabPersistence"

.AllowMultiSelect = False
.show

For Each WB In .SelectedItems
Set S = Workbooks.Open(WB)
Next
End With

If S Is Nothing Then Exit Sub

End Sub

Thanking you in advance

Jenny
  • 441
  • 2
  • 7
  • 19
  • If you already know the filename, why are you showing the FileDialog? – Ben Jul 17 '17 at 10:21
  • @BensaysNotoPoliticsonSO then how do i proceed ? – Jenny Jul 17 '17 at 11:13
  • @BensaysNotoPoliticsonSO sorry for the direct question, I am new to this platform – Jenny Jul 17 '17 at 11:24
  • 2
    Possible duplicate of [Open an Excel file from SharePoint site](https://stackoverflow.com/questions/19505513/open-an-excel-file-from-sharepoint-site) – Ben Jul 17 '17 at 12:05
  • @BensaysNotoPoliticsonSO i tried the first solutin and it dint work – Jenny Jul 17 '17 at 12:07
  • .... and what happened? What was the error message? etc... – Ben Jul 17 '17 at 12:55
  • @BensaysNotoPoliticsonSO it showed the file open Dialogue box from the local directory – Jenny Jul 18 '17 at 08:29
  • @BensaysNotoPoliticsonSO it dint direct me to the path Name mentioned – Jenny Jul 18 '17 at 08:29
  • Why are you trying to open the file dialog if you already know what file you want? The file dialog is for you to *ask the user* what file to open. If you already know, you can skip that step and go straight to `Workbooks.Open` – Ben Jul 18 '17 at 08:36
  • @BensaysNotoPoliticsonSO ok, i will try like, i will give the path and give workbooks.open. I will try this and let you know, if i have any erros – Jenny Jul 18 '17 at 09:18

1 Answers1

1

This SO article covers your question quite well. To access files within your filesystem (including network):

This method opens a certain workbook:

Workbooks.Open ("yourWorkbook.xls")

Documentation and examples for this method.

If you actually want to open the folder, u can use this:

Application.FollowHyperlink "FolderLocation"

Documentation for this method.

Edit:

Use the UNC path as described in This SO article. Add your file name to the path and access it with the methods from above.

You can substitute the Cells(RowCtr, 1).Value = f.Name from the sample code in the article with a simple Debug.Print f.Name to check the outcome.