+ Reply to Thread
Results 1 to 6 of 6

MS Excel VBA to return a list of SharePoint files in a Folder accessed via https address

  1. #1
    Registered User
    Join Date
    07-25-2019
    Location
    Salt Lake City, UT
    MS-Off Ver
    2016 32 bit
    Posts
    21

    MS Excel VBA to return a list of SharePoint files in a Folder accessed via https address

    Up until recently, I had a couple of ways of accessing SharePoint folders. After our company's SharePoint update, some of those channels have been shut down.

    Here's what I'm trying to do:
    1. On a worksheet in Excel, the user pushes a button tied to a macro.
      Via the macro
    2. The button color changes to yellow and text says "please wait".
    3. Info from named ranges on the worksheet are gathered and compiled to help derive the correct SharePoint folder address.
    4. A data table has contents/rows deleted.
    5. The SharePoint folder contents populate a VBA list.
    6. The list values populate the data table in a loop until all of the list items have been added (the data table is used as a named range to populate a dropdown data-validation-list field).
    7. The button color changes back to its original color, and text says "Click to run".

    Previously my code worked great... . Now, it's broken. The only way people using this Excel tool can access the SharePoint is via their permissions (OneDrive login or Single Sign On via the Windows login) and connecting to an Intranet SharePoint Site via https.

    I can use the https url in Windows Explorer to access the folder. But, my macro doesn't recognize the address via this:

    aFileAddress = Dir("https://[my SharePoint-folder address]")
    Last edited by joeyslaptop; 03-03-2022 at 02:30 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    863

    Re: MS Excel VBA to return a list of SharePoint files in a Folder accessed via https addre

    No you cant use dir on sharepointfolders.

    If you are syncing sharepoint to your explorer, you should use the scripting.filesystemobject.
    Or you could always use power query, to create a direct link to sharepoint.

  3. #3
    Registered User
    Join Date
    07-25-2019
    Location
    Salt Lake City, UT
    MS-Off Ver
    2016 32 bit
    Posts
    21

    Re: MS Excel VBA to return a list of SharePoint files in a Folder accessed via https addre

    Is there an easy way to pull contents from a SharePoint folder via VBA? I'm seeing that it would take a lot of effort and filters, duplicating and splitting columns, etc to pull the specific folder contents due to the no-wildcard issue in PowerQuery.

  4. #4
    Valued Forum Contributor
    Join Date
    10-11-2021
    Location
    Netherlands
    MS-Off Ver
    365
    Posts
    863

    Re: MS Excel VBA to return a list of SharePoint files in a Folder accessed via https addre

    Have you synced your folder to your explorer?

  5. #5
    Registered User
    Join Date
    07-25-2019
    Location
    Salt Lake City, UT
    MS-Off Ver
    2016 32 bit
    Posts
    21
    Quote Originally Posted by JEC. View Post
    Have you synced your folder to your explorer?
    I can’t use this method because other people have to be able to run it on their machines.

    I think I figured it out though. In PowerQuery, I kept removing folders from the end of the url until it connected and pulled in ALL of the site files at once. One of the detail columns it pulls contains the sub folder names. So, I was able to filter on that in PowerQuery, and then make it dynamic based on an excel named-range cell value.

    It’s not overly complex either my file broke and I had to set it all up again.

    Thanks for your input. Ultimately, I ditched most of my VBA code and just did a PowerQuery db table refresh via VBA.

  6. #6
    Registered User
    Join Date
    07-25-2019
    Location
    Salt Lake City, UT
    MS-Off Ver
    2016 32 bit
    Posts
    21

    Re: MS Excel VBA to return a list of SharePoint files in a Folder accessed via https addre

    I can’t use this method because other people have to be able to run it on their machines. I don’t want them all to have to sync the drive. It’s a good thought - just isn’t practical in my scenario.

    I think I figured it out though. In PowerQuery, I kept removing folders from the end of the SharePoint-folder query’s url until it connected and pulled in ALL of the site files at once. One of the detail columns it pulls contains the sub folder names. So, I was able to filter on that column in PowerQuery, and then make it dynamic based on an excel named-range cell value.

    It’s not overly complex either. If my file were to break and I had to set it all up again, I’d be ok.

    Thanks for your input. Ultimately, I ditched most of my VBA code and just did a PowerQuery db table refresh via VBA.

    Range(“FileSelectionQuery”).ListObject.QueryTable.Refresh BackgroundQuery: = False

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to Extract Folder Path from SharePoint address by Formula?
    By sunnyschindler in forum Excel General
    Replies: 1
    Last Post: 11-10-2021, 05:53 AM
  2. Open list of https csv file links and save the files
    By fpanda009 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-03-2021, 11:27 AM
  3. open all excel files in a sharepoint folder
    By Ish23 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-13-2021, 02:36 AM
  4. Copy Files from One Folder to Another Folder based on a List In Excel
    By civram1982 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-21-2019, 06:34 AM
  5. problem with finding a file in https sharepoint
    By MKS2012 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-29-2012, 01:55 PM
  6. [SOLVED] vba to return the files and date last accessed in a directory
    By timbo1957 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-16-2012, 05:20 AM
  7. Search for old files by last accessed from a designated folder
    By bodiewil in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-25-2010, 04:38 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1