Maintain Sharepoint Server Url with subfolders in document list

Question

I want to iterate through Sharepoint document library items (files and folder and subfolders) then print every folder content in Excel sheet

I've the following code in my Macro

Sub WriteFileProp(mySourcePath As String)

    Dim objMyList As ListObject
    Dim objWksheet As Worksheet
    Dim strSPServer As String
    Dim SERVER As String
    Const LISTNAME As String = "Documents"
    Const VIEWNAME As String = ""
     SERVER = mySourcePath
   ' The SharePoint server URL pointing to
   ' the SharePoint list to import into Excel.

    strSPServer = "http:" & SERVER & "/_vti_bin"
    ' Add a new worksheet to the active workbook.
    Set objWksheet = Worksheets.Add
    ' Add a list range to the newly created worksheet
    ' and populated it with the data from the SharePoint list.
    Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _
        Array(strSPServer, LISTNAME, VIEWNAME), False, , Range("A1"))

 End Sub

and that function

 Dim objFSO As Object
 Dim objFolder As Object
 Dim ObjFile As Object
 Dim SubFolder As Object
 Dim pth As String
 Dim WBn As Workbook
 Dim ObCount As Long
 Dim FileNme As String

 Application.ScreenUpdating = False

 Set objFSO = CreateObject("Scripting.FileSystemObject")

 'Get the folder object associated with the directory
 Set objFolder = objFSO.GetFolder("//S1.S2.S3Portal/Shared%20Documents/")

 Call WriteFileProp("S1.S2.S3Portal.com/")
 'Print main view

For Each SubFolder In objFolder.SubFolders
            Call WriteFileProp(SubFolder.Path)
        Next

Everything works properly until Call WriteFileProp("S1.S2.S3Portal.com/") After that I loop for subfolders and the path isn't compatible with ServerUrl that's provided in

objWksheet.ListObjects.Add(xlSrcExternal, _
        Array(strSPServer, LISTNAME, VIEWNAME), False, , Range("A1"))

i.e. lets say the first subfolder name is Software its path is in the following format //S1.S2.S3Portal/Shared%20Documents/Software

but ListObjects.Add accepts only that format according to msdn

strSPServer = http://S1.S2.S3Portal

ListName= "Documents"

View=""

are there anyway to maintain subfolders Urls of that doc. library and add them to excel workbook using ListObjects.Add or similar function

Answer 1

I've used GetListItems with Soap query and webservice to get list items instead of the posted method