Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    65
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Reading Sharepoint Document Folders from VBA

    Hi,

    I don't know if what I'm trying to do is possible - hopefully someone can advise me.

    I've developed an app in Access which uses VBA to read a set of spreadsheets in a folder, sucks the data out and stores it in the Access db to allow reporting to be done.

    I use Application.FileDialog (msoFileDialogFolderPicker) to allow the user to select the folder where the spreadsheets are.

    Then storing the path selected by this in strINPUTDIR I then use the following code to get the first file.

    strFileName = Dir(strINPUTDIR, vbNormal) ' Retrieve the first file in the input

    This is followed by a loop to read other files in the folder.

    This has worked fine on mapped drives. However I would like it to be able to read files from a Sharepoint Shared Documents folder but I cannot get it working.

    I tend to get the error "Bad File Name or Number".

    strINPUTDIR is set to https://server.company.com/offices/u...orms/Document/

    When I navigate to the Sharepoint folder using IE I use the link https://server.company.com/offices/u.../AllItems.aspx

    (In previewing this the message editor has replaced middle bit of the URL with ..... )

    I guess the AllItems.aspx calls some sort of View of the folder.

    I've tried replacing the space charaters in my string with %20 but no luck. I've tried removing the /Forms and /Document bit of the Sharepoint URL also - same result.

    I'm running Access 2007 and XP SP3. Not sure what Sharepoint version.

    Is what I am trying to achieve possible and if so how should I go about it?

    Thanks
    Last edited by njl100; 2011-03-24 at 11:28. Reason: Access Version added

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    I suspect DIR is going to have problems with a web server.

    Can you somehow instantiate a SharedWorkspace object (SharedWorkspace Object Members)? It's easy if you have a file already open from the Sharepoint server, but I don't know whether you can find a way to access it using a FileOpen dialog.

  3. The Following User Says Thank You to jscher2000 For This Useful Post:

    njl100 (2011-03-25)

  4. #3
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    65
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Thanks - looks interesting. I'll give it a go but as Captain Oates said "I might be a while." :-)

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Try this link.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    njl100 (2011-03-25)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •