+ Reply to Thread
Results 1 to 4 of 4

VBA Coding for Sharepoint

  1. #1
    Registered User
    Join Date
    07-16-2021
    Location
    Dallastown, PA
    MS-Off Ver
    Microsoft 365
    Posts
    2

    VBA Coding for Sharepoint

    We recently moved all of our drives to Sharepoint. The problem that we are having is the file paths that were set up for our macros no longer works as they are directing to the drive and not sharepoint. I have attempted to update these but having issues with the path/location. This macro is pulling a txt file and exporting the data into excel.



    Please help!!



    Here is the current macro with the old file path:

    Sub Import_All_Text_Files_2007()

    Dim nxt_row As Long

    'Change Path
    Const strPath As String = "S:\VMA\WEX TRANSACTIONS\WEX DQRY Data\NEW BULKS-CURRENT WEEK\"
    Dim strExtension As String

    'Stop Screen Flickering
    Application.ScreenUpdating = False

    ChDir strPath

    'Change extension
    strExtension = Dir(strPath & "*.txt")

    Do While strExtension <> ""

    'Adds File Name as title on next row
    Range("A65536").End(xlUp).Offset(1, 0).Value = strExtension

    'Sets Row Number for Data to Begin
    nxt_row = Range("A65536").End(xlUp).Offset(1, 0).Row

    'Below is from a recorded macro importing a text file
    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & strPath & strExtension, Destination:=Range("$A$" & nxt_row))
    .Name = strExtension
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    'Delimiter Settings:
    .TextFileConsecutiveDelimiter = True
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False

    .TextFileTrailingMinusNumbers = False
    .Refresh BackgroundQuery:=False
    End With

    strExtension = Dir
    Loop

    Application.ScreenUpdating = True

    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: VBA Coding for Sharepoint

    I literally just setup a macro to run off a file on our sharepoint server..

    This code works great so far:

    Please Login or Register  to view this content.
    If you find the suggestion or solution helpful, please consider adding reputation to the post.

  3. #3
    Registered User
    Join Date
    07-16-2021
    Location
    Dallastown, PA
    MS-Off Ver
    Microsoft 365
    Posts
    2

    Re: VBA Coding for Sharepoint

    I attempted the above coding but stating "Sorry, we couldn't find". The path I'm putting in the macro is directing to pull txt files from a folder on our Sharepoint.

    Also, when I copy the sharepoint link, its adding a lot of "extra" into the link. I removed the "extra" put still isn't working.

    For Example
    copied link:
    //xxxxxx.sharepoint.com/:f:/r/sites/SharedServices/DealerBilling/VMA/WEX%20TRANSACTIONS/WEX%20DQRY%20Data/NEW%20BULKS-CURRENT%20WEEK?csf=1&web=1&e=fL6fGd[/url]

    modified link:
    //xxxxxx.sharepoint.com/:f:/r/sites/SharedServices/DealerBilling/VMA/WEX[/url] TRANSACTIONS/WEX DQRY Data/NEW BULKS-CURRENT WEEK
    Last edited by Amber_Element; 07-16-2021 at 03:45 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: VBA Coding for Sharepoint

    For me, i had to login to the sharepoint server itself to find the appropriate path, it took me 3 different url's to finally get it working..
    Also make sure that you add the file name to the end of the link, mine looks like your "copied link" only pointing to our folder structure.

    And not sure if it matters on your setup, but for mine to work i had to provide the https:// as part of the path,

+ 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. sharepoint upload sticks on "Uploading to SharePoint" but still uploads
    By scottiex in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-30-2021, 04:19 PM
  2. Replies: 0
    Last Post: 10-28-2020, 02:32 PM
  3. Sharepoint coding
    By sahilkh18 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-31-2017, 04:37 AM
  4. VBA to select SharePoint Content Type when saving Excel file to SharePoint
    By Luffk73 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-24-2017, 05:22 PM
  5. [SOLVED] In need of help regarding combo box coding and button coding (Access form project)
    By mailblade in forum Access Tables & Databases
    Replies: 2
    Last Post: 01-09-2016, 01:34 AM
  6. Opening SharePoint file with VBA - disable SharePoint "Contacting the server" message
    By kopite2002 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-22-2013, 01:56 AM
  7. Replies: 0
    Last Post: 10-08-2013, 10:54 AM

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