Consulting

Results 1 to 2 of 2

Thread: Sleeper: Writing File Address to Save File Directly to a Shared Folder

  1. #1
    VBAX Newbie
    Joined
    Feb 2024
    Posts
    1
    Location

    Sleeper: Writing File Address to Save File Directly to a Shared Folder

    My users are sparse in their understanding of computers. I gave them a Form in Excel to use and included a "Save As" button. I want the VBA attached to the button to have the File Explorer open to a shared folder on SharePoint that all users have access to. I thought that would be easy, but the File Explorer only opens to their respective Documents folder which means the user then needs to browse to the needed folder to save the Form, which is problematic for some users. Any assistance, if this is possible, would be greatly appreciated.
    Thank you.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,721
    Location
    Don't have Sharepoint anymore, but try something like this

    I use the commented out part for local drives

    Option Explicit
    
    Sub Concept()
        Dim sFilename As String
        
        'i don;t have sharepoint anymore, but record a macro where you save it to get the server address, something like
        
        '"\\sp001-server@SSL\sites\SiteCollectionName\DocumentLibrary\               Filename.xlsm"
        
         sFilename = Application.GetSaveAsFilename(Title:="Where to save the file")
               
        If sFilename = "False" Then
            MsgBox "Not going to save it"
            Exit Sub
        End If
        
        sFilename = "\\sp001-server@SSL\sites\SiteCollectionName\DocumentLibrary\" & sFilename & ".xlsm"
        
        MsgBox sFilename
    
    
        'can't really save it
        ThisWorkbook.SaveAs sFilename, xlOpenXMLWorkbookMacroEnabled
        
        
        
        '----------------------------------------------- similar for local drive
    '    ChDrive "D:"
    '    ChDir "\test"
    '
    '
    '    sFilename = Application.GetSaveAsFilename(Title:="Where to save the file")
    '
    '    If sFilename = "False" Then
    '        MsgBox "Not going to save it"
    '        Exit Sub
    '    End If
    '
    '
    '    sFilename = sFilename & ".xlsm"
    '
    '    MsgBox sFilename
    '
    '    ThisWorkbook.SaveAs sFilename, xlOpenXMLWorkbookMacroEnabled
    
    
    
    
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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