Using DIR from Excel VBA to list SharePoint Shared Documents RRS feed

  • Question

  • Hi Everybody,
    I have a number of excel files in SharePoint with data for different countries. They all have the same formatting and number of columns and are all .xlsm files.
    The idea is to get all data from these separate files and to merge them into a master spreadsheet I called Summary.xls. From this summary, it will be possible to have a pivot table for all managers to have an overview of the consolidated data from all countries.
    I have put together a macro on Summary.xls to search for *.xlsm on a particular folder, open each file and copy its content into Summary.xls. This works fine locally, but when I change the path from "C:\folder\name" to something like "\\\teams\NAME\Shared Folders" it simply doesn't work. Apparently I can't use the pathway like that because the files are stored on a Web Server, so I would have to use the \Forms\AllItems.aspx somehow to retrieve that information.
    Here is an example of the code I'm using:
    Sub Update_Summary()
    Dim oWbk As Workbook
    Dim sFil As String
    Dim sPath As String
    Dim answer As String
    sPath = "\\\teams\NAME\Shared Folders"
    'sPath = "C:\Users\me\Documents\Data"
    'The local directory works just fine
    ChDir sPath
    sFil = Dir("*.xlsm")
    'The issue is that here it can't find any .xlsm file, so the next line will end the loop
    Do While sFil <> ""
    Set oWbk = Workbooks.Open(sPath & "\" & sFil)
    oWbk.Close False
    sFil = Dir
    End Sub
    Any ideas?
    Thank you all.
    Wednesday, November 18, 2009 10:55 PM


  • Hello

    Please record a macro to check how Excel itself will handle this kind of situation, here's my result:

    Sub Macro1()
    ' Macro1 Macro

        Workbooks.Open Filename:="http://localhost/Book1Disable.xlsx"
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Tim Li Tuesday, November 24, 2009 9:55 AM
    Tuesday, November 24, 2009 9:55 AM