0

I am trying to access a folder in Sharepoint that is secured (https). I am using a little VBA macro in excel that uses the file system object . see code below that was found in similar thread. the macro would open the .csv file in the folder and scan for a certain string.

Sometimes it would work but sometimes it would say 'path not found' in that case I would open the sharepoint site with Microsoft Explorer (browser) and then it all would have worked. Now that explorer has been decommissioned I cannot open that path in explorer anymore .

any suggestion ? I know mapping/syncing the sharepoint would be another option but I want my tool to be used by different users and I guess mapping/syncing would make the new path , user dependent .

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(//companyname.sharepoint.com@ssl/sites/Invoices/Shared Documents/Tre/daily)

For each oFile in oFolder.Files
 ...etc etc.

thanks Marthomas

Ike
  • 9,580
  • 4
  • 13
  • 29
marthomas
  • 1
  • 1
  • 2
    There's no easy way to work with SP folders/files in VBA. MS has pretty much left its VBA users high and dry in this move over to SP. – Tim Williams Jul 25 '22 at 19:02
  • [Some of this might be of use](https://stackoverflow.com/questions/19505513/open-an-excel-file-from-sharepoint-site) – Cameron Critchlow Jul 25 '22 at 22:53
  • thanks both for your replies , I found it shocking , but I guess MS is trying to steer towards Power Automate and such. I will try the various approaches proposed in the suggested link. like webdav . is just seems overly complicated for such a simple thing ! – marthomas Jul 27 '22 at 14:50
  • little update , I found in Microsoft Edge , under options ....'Reload page with internet explorer mode' . I opened the sharepoint site and the macro magically works ! – marthomas Jul 27 '22 at 15:25

2 Answers2

0

While IE is not available from User Interface, you still can use it in macro, so it is possible to login to SharePoint using IE and work with SharePoint.

Arkush
  • 1
  • Hi , thanks, I guess I can use the macro to open and close a IE session with the sharepoint . any chance you can share the VBA command line ? – marthomas Dec 22 '22 at 03:43
0

I tried some way to access sharepoint file via filesystemobject. And find no way. I think it is impossible to access sharepoint with filesystemobject.

I shall use other excel function to access my files. Other excel method may be valid for sharepoint files.

Sub SharePoint()
Dim strTestFolder As String, strTestBook As String, MyBook As Workbook

strTestFolder = "https://XXXXXXXX.sharepoint.com/sites/msteams_YYYYYYYY/Shared%20Documents/General/TEST/"
strTestBook = "NewBook.xlsx"

'This works well.
Set MyBook = Workbooks.Open(Filename:=strTestFolder & strTestBook)
MyBook.Sheets(1).Cells(1, 1) = Date
MyBook.Sheets(1).Cells(2, 1) = Time
MyBook.Save
MyBook.Close
'My Excel can open workbooks in sharepoint folder

Dim FSO As Object, objMyFolder As Object, objFile As Object
Set FSO = CreateObject("Scripting.FileSystemObject")

'Check my workbook with FSO
If FSO.FileExists(strTestFolder & strTestBook) Then 'Same name as Workbooks.Open
    MsgBox "File Exists"
Else
    MsgBox strTestFolder & strTestBook & " does not Exists" 'Excel shows this message. FSO can not recognize my workbook.
End If


'Convert path Patern1
strTestFolder = "//XXXXXXXX.sharepoint.com/sites/msteams_YYYYYYYY/Shared%20Documents/General/TEST/"
If FSO.FileExists(strTestFolder & strTestBook) Then
    MsgBox "File Exists"
Else
    MsgBox strTestFolder & strTestBook & " does not Exists" 'Excel shows this message. FSO can not recognize my workbook.
End If

'Convert path Patern2
strTestFolder = "\\XXXXXXXX.sharepoint.com\sites\msteams_YYYYYYYY\Shared%20Documents\General\TEST\"
If FSO.FileExists(strTestFolder & strTestBook) Then
    MsgBox "File Exists"
Else
    MsgBox strTestFolder & strTestBook & " does not Exists" 'Excel shows this message. FSO can not recognize my workbook.
End If

'Convert path Patern3
strTestFolder = "https://XXXXXXXX.sharepoint.com/sites/msteams_YYYYYYYY/Shared Documents/General/TEST/"
If FSO.FileExists(strTestFolder & strTestBook) Then 'Same name as Workbooks.Open
    MsgBox "File Exists"
Else
    MsgBox strTestFolder & strTestBook & " does not Exists" 'Excel shows this message. FSO can not recognize my workbook.
End If

'Convert path Patern4
strTestFolder = "//XXXXXXXX.sharepoint.com/sites/msteams_YYYYYYYY/Shared Documents/General/TEST/"
If FSO.FileExists(strTestFolder & strTestBook) Then
    MsgBox "File Exists"
Else
    MsgBox strTestFolder & strTestBook & " does not Exists" 'Excel shows this message. FSO can not recognize my workbook.
End If

'Convert path Patern5
strTestFolder = "\\XXXXXXXX.sharepoint.com\sites\msteams_YYYYYYYY\Shared Documents\General\TEST\"
If FSO.FileExists(strTestFolder & strTestBook) Then
    MsgBox "File Exists"
Else
    MsgBox strTestFolder & strTestBook & " does not Exists" 'Excel shows this message. FSO can not recognize my workbook.
End If

End Sub

OLTO and SUGI-cube Project
  • 97
  • 1
  • 7