Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1

    Unanswered: [VBA] Clear File Save Folder

    So i have code that opens up Excel, Word, and Ppt docs from Access. Curious if there is a way to clear or change the save folder for the file. For example, when I open an Excel doc and go to save, the user will get a save as promopt (it is read only) and then it will suggest a file save location. The default location is the folder that the file lives in, but I don't want the user to see that folder. Any way to clear or change this location?

    Example Excel code:
    Code:
    Set xlApp = New Excel.Application
        xlApp.Visible = True
        Set xlWb = xlApp.Workbooks.Open(docLoc, ReadOnly:=True)

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Get the path via file dialog:
    '------------BE SURE TO SET THIS IN VBE menu,TOOLS,REFERENCES---
    'reference Microsoft Office XX.0 Object Library
    '-----------------------------------
    Set fd = Application.FileDialog(msoFileDialogSaveAs)


    usage:
    vDir = UserPickFile2Save("c:\")
    vFile = vDir & activeworkbook.Name
    if vDir <>"" then activeworkbook.saveas vFile


    Code:
    Private Function UserPickFile2Save(Optional pvDir)
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialog As String, sDecr  As String, sExt As String
    Dim vVal, vFile, vPath
    Dim vRet
    Dim fd As FileDialog
    
    
    If IsMissing(pvDir) Then  pvDir = "c:\"
    vPath = pvDir & vFile
    
    '------------BE SURE TO SET THIS IN VBE menu,TOOLS,REFERENCES---
    'reference  Microsoft Office 11.0 Object Library
    '-----------------------------------
    Set fd = Application.FileDialog(msoFileDialogSaveAs)    'msoFileDialogPicker
    With fd
        .AllowMultiSelect = False
        .Title = "Save File AS"
        .ButtonName = "Save"
    
        '.Filters.Clear
        '.Filters.Add sDecr, sExt
        '.Filters.Add "Excel Files", "*.xlsx"
        '.Filters.Add "All Files", "*.*"
        
        .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
        .InitialFileName = vPath
            
         If .Show = 0 Then Exit Function                      'There is a problem
    
         'Save the first file selected
       UserPickFile2Save = Trim(.SelectedItems(1))
    End With
    End Function

Posting Permissions

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