asked on

How to set Excel's default SaveAs folder, from Access VBA

I'd like to change Excel's save path in Access VBA.

I've tried using ChDrive and ChDir but even though these methods appear to work, in fact they don't: Excel still uses MyDocument as the default SaveAs folder.

The thing is, I don't want to specify the destination folder as part of the filespec in my ".SaveAs(Filespec)" instruction. I want the Filespec to contain only the filename. Why? Because of Excel VBA's 254 characters limitation on "Filespec".

Currently, the path of the destination folder that my Access application needs to save VBA created Excel files in is about 220 characters long, which leaves only 34 characters for the file name, which is not enough. So that's where my need stems from.

My strategy would be to set and save the destination folder ahead of time, using some unknown method that I hope you'll be able to tell me, and then issue SaveAs(Filename) instructions to save Excel files whose names could be up to 254 characters long, including the .xlsx extension.

Any solution?

Rey Obrero (Capricorn1)

how about using the Office FileDialog?

sample code

Function SaveToFolder() As String
On Error GoTo ErrHandler
  
   Dim strFolderPath As String
   Dim fd As Office.FileDialog
  
   Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  
   With fd
    .InitialFileName = "C:\Folder1\Folder2\ExcelFiles"
    .Title = "Browse for folder"
    .ButtonName = "Select"
    .InitialView = msoFileDialogViewDetails
    If .Show = -1 Then
       strFolderPath = CStr(fd.SelectedItems.Item(1)) & "\"
    Else
       strFolderPath = ""
    End If
   End With
  
   SaveToFolder = strFolderPath
    
ErrHandlerExit:
   Exit Function
  
ErrHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrHandlerExit
  
End Function

Open in new window

also see this similar thread
https://www.experts-exchange.com/questions/28927507/MS-Access-2010-VBA-FileDialog.html

J2015

ASKER

Thanks Ray, but your solution does not address my problem.

I don't need a function to return a path. I already have a path. What I need is a solution to store that path in Excel's little brain USING VBA so it can hopefully remember it.

Then, whenever I'll issue .SaveAs(filename) commands in VBA, it'll automatically use that memorized path to save the file in, without me having to tell it.

Hope this makes it clearer.

Jean

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

Your request is clear but I suggest that you reconsider. If this is an app you are building for others, they may object to you changing THEIR default path. If the app is for yourself, it is only a one time change and doesn't require automation.

When I want to create semi-permanent, customizable paths, I create a table for that purpose. I usually use a separate row for each path so I can add rows later without having to change the table structure. I create a maintenance form that the admin can use to change the default path for a particular type of export for everyone or I make the table custom for each user and so they control their own paths separately. It really depends on what the app needs. In most cases, I want everyone exporting to the same folder on the server so I want an admin to control the change if it is made. If I want to allow an override, I add a textbox on the form or I allow the user to type over the default I called up from the defaults table.

J2015

ASKER

Your solution worked better than ChDrive and ChDir, because it DID change the default path, whereas ChDrive and ChDir have no effect.

On the other hand, it did not work because if you specify only a filename, Excel adds this default path to the filename and the SaveAs method will still fails if the total exceeds 254 characters.

The solution that I found is to tell my users to create a mapped drive pointing to the invariable portion of their path. All they need to do after that is to use this mapped drive as a starting point. This makes the total length of the filespec 85 characters shorter, and it works.

Thank you Wayne, and thank you all!

Jean

PatHartman

You never told us what problem you were trying to solve. I would have posted the code to create a specific directory if it didn't already exist. You told us what "solution" you wanted and that still didn't work for you.