GetSaveAsName with predefined directory location Access/Excel Automation

[sxschech]

I am using access to create an excel file and perform a save as. The only part that doesn't seem to be working is defining the starting directory. It does put in the file name correctly. I have reviewed some of the threads and tried the suggestions such as below and they didn't seem to alter the starting directory. The starting directory continues to show as whatever the last one I pointed to via the mouse was. Is it because I'm trying to save it from Access rather than within Excel or because I'm trying to save to a network drive instead of hard disk, or do I need to use UNC instead of drive letter? Note: drive letter is defined by IT for all users so there is no issue of people having a different drive letter assignment.

FName = wb.Application.GetSaveAsFilename(InitialFileName:=stDefaultName, FileFilter:= _
                " Excel Macro Free Workbook (*.xlsx), *.xlsx", _
                FilterIndex:=2, Title:="Save to a new workbook")

I tried defining the default name as follows:
[tt]stDefaultName = "F:\Project Reports\Output\Upload1_" & Format(Forms!frmmain.txtEndDate, "yyyymmdd") & ".xlsx" [/tt]

The directory does not display as "F", stays at "C"

I also tried:

ChDrive Forms!frmmain.txtLocation & " Reports\Output\"
        ChDir Forms!frmmain.txtLocation & " Reports\Output\"

When I step through code and do a dir statement to the immediate window, it shows the directory has been changed, but maybe that is only changing the directory for Access and has no impact on Excel?

 

[SkipVought]

Hi,

GetSaveAsFilename is an Excel method, not an Access method. In order to use GetSaveAsFilename, you must first instantiate an Excel Application object.

However, there is no need to use GetSaveAsFilename in this instance, since you already have the entire path and filename. In order to save a workbook, you must 1) have an Excel Aplication Object and 2) use the Excel SaveAs method...

Dim xl as Object
Set xl = GetObject("Excel.Application")
xl.Workbooks.SaveAs "F:\Project Reports\Output\Upload1_" & Format(Forms!frmmain.txtEndDate, "yyyymmdd") & ".xlsx"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[sxschech]

Hi Skip, that was my starting point, what I was trying to do was expand the capability by using the GetSaveAsFilename so when the save as file dialog box appeared, everything would be filled out ready to go to the proper location, while at the same time allowing the user to change the location should there be a need to save the file somewhere other than the default location/name. Perhaps I'll need to create a prompt with inputbox instead?

 

[SkipVought]

https://msdn.microsoft.com/en-us/library/office/ff195734.aspx

Remarks

[li]This string passed in the FileFilter argument consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters, text and addin: "Text Files (*.txt), *.txt, Add-In Files (*.xla), *.xla".

[/li][li]To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, "Visual Basic Files (*.bas; *.txt),*.bas;*.txt".

[/li][li]This method returns the selected file name or the name entered by the user. The returned name may include a path specification. Returns False if the user cancels the dialog box.

[/li][li]This method may change the current drive or folder.[/li]

...emphasis mine.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[sxschech]

I reread that, still unclear how I get the

current drive or folder

set before the dialog opens so that user sees the predefined directory. I assume that the emphasis part refers to the user being able to change the directory after the fact. Appreciate you looking into it. Guess I'll have to tell them that if they want to save in the default location, they have to manually select it as the price to pay for having the familiar point and click interface to have the choice of saving elsewhere and/or renaming the file.

 

[sxschech]

Found this which seems to meet my need. Brings up the file dialog, shows the preferred starting directory and displays the default filename. User can then click on Save button to accept default or is free to change the location, change the name or both. Note: even though user is clicking on Save button, this code is not actually saving the file, only returning the file path and file name so that it can be used in the save code I already have. Only part that sometimes gave an error for me was the line [pre].FilterIndex = 2[/pre] If it happens again, I'll try to get the error message etc.

This goes in the event that would be for doing the save:

FName = GetSaveFilename(FilePath & FileName & ".xlsx")

This goes in a standard code module

Public Function GetSaveFilename(FileLoc As String) As String
'[URL unfurl="true"]https://stackoverflow.com/questions/7770030/preset-the-save-as-type-field-while-using-application-filedialogmsofiledialog[/URL]
'20170612
    Dim Dialog As FileDialog
    
    Set Dialog = Application.FileDialog(msoFileDialogSaveAs)
    
    With Dialog
        .InitialFileName = FileLoc 'CurrentProject.Path & "\*.ext"
        .FilterIndex = 2
        .title = "Save As"
        If .Show <> 0 Then
            GetSaveFilename = .SelectedItems(1)
        End If
    End With
End Function

 

[combo]

You do not manage (add/delete) Filters of FileDialog object, so FileDialog with setting FilterIndex=2 is out of your control.

combo

 

[sxschech]

So best to comment or remove that line? I wonder why OP included it?

 

[strongm]

Almost certainly a legacy of earlier code