Filename Created with VBA Does Not Display In Save As Dialog Box

[censo]

PREFACE
The following code successfully copies a worksheet in a workbook into a completely different workbook using a specific naming convention:

Sheets("FFIL").Copy
    ActiveWorkbook.SaveAs Filename:=Format(Now(), "mm.dd.yy-hhnn-UPL"), FileFormat:=xlText
    ActiveSheet.Name = "FFIL"

PROBLEM
The issue I have is, the header across the top of Excel of the newly created workbook correctly shows the desired filename (e.g., "07.22.21-1643-UPL") but when I click on the Save As icon, the name of this file does not populate in the Save As dialog box. Instead, the File name: field is just empty (but the correct File Extension is selected, which is (*.txt)

OBJECTIVE
The end goal is for the user to choose what the destination directory will be when saving the document but have the name already populated such that they won't have to type the name in manually. What is missing from the code in order to achieve this functionality?

Thanks in advance.

 

[GWteB]

The following code successfully copies a worksheet in a workbook into a completely different workbook using a specific naming convention:

It does not!! Your observation about the naming is correct, but your assumption about the file format is not correct. You did not save a new Workbook. You saved a TAB-delimited text file instead (without formulas, number formatting, colors and so on). Because Excel assumes by default a workbook (xlsx/xlsm/xlsb) is saved, whilst Excel determines that this wasn't the case on the last save to disk, Excel will not propose a file name (ie blank). So it depends on your wishes, what the final code will look like.

 

[rlv01]

One way.

    Dim FName As String
    
    FName = Format(Now(), "mm.dd.yy-hhnn-UPL") & ".txt"
    FName = Application.GetSaveAsFilename(InitialFileName:=FName, fileFilter:="Text Files (*.txt), *.txt")
    
    If FName <> "False" Then
        Sheets("FFIL").Copy
        ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlText
        ActiveSheet.Name = "FFIL"
    End If

 

[censo]

You saved a TAB-delimited text file instead (without formulas, number formatting, colors and so on).

Because the document appears before me in the Excel application, I incorrectly called it a "new workbook". One could argue that's splitting hairs from the non-expert's point of view but I appreciate the syntax correction nevertheless.

Be that as it may, the data displayed in the TAB-delimited text file (displayed within the Excel application) which opens independently of the source from where it originated when the above code is ran, is 100% correct as shown; without formulas, number formatting, colors, etc - a flat file for all intents and purposes (hence the "FF" in "FFIL").

Our dialog actually helped me get one step closer to achieving the OBJECTIVE as stated in my OP, so many thanks to you for your thoughtful and productive contribution. Cheers.

 

[censo]

One way.

Ahh, your code works splendidly. I have one caveat however.

I use the following code as an AutoRun to kick off a series of Private macros in succession. With the proposed solution you provided, the user is automatically presented with the Save As dialog box without having the ability to review/alter the file first (plus, there would be other files being created as well which may cause a conflict/issue or confusion when saving).

Is there a way for the Save As dialog box to present the filename created when the user actually executes the Save vs. immediately when the new file is created?

Sub D_AutoRunInvChecks()
    Application.Run "'Summation_Macros_v1.22.xlsm'!A_ActiveChecks"
    Application.Run "'Summation_Macros_v1.22.xlsm'!B_ActiveChecks"
    Application.Run "'Summation_Macros_v1.22.xlsm'!C_ActiveChecks"
End Sub

 

[censo]

One way.

In thinking things over, perhaps what I desire as the end result is not possible as stated, so how about this:

The newly created files are automatically saved into the user's Download directory (without being prompted to Save), then when the user clicks on Save As, the dialog box would open with the name of the file already populated and the desired directory can be determined at that time.

Assuming that a user can configure their Download directory to deviate from the standard path, what would the code look like for a solution that "catches all" and always targets that directory accurately?

 

[rlv01]

You could tell your users not to use the Save-As icon and instead use the File-Save As menu command. That seems to work. Otherwise I think you need to be explicit about the ".txt" file extension. For example, this will work (the Save-As icon will populate the name):

    Dim FName As String
    
    FName = Format(Now(), "mm.dd.yy-hhnn-UPL") & ".txt"
    
Sheets("FFIL").Copy
    ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlText
    ActiveSheet.Name = "FFIL"

If you need the file name to be without an extension then as far as the Excel 'Save As' icon, I think you are going have to go with one of the workarounds mentioned above.

 

[censo]

    Dim FName As String
   
    FName = Format(Now(), "mm.dd.yy-hhnn-UPL") & ".txt"
   
Sheets("FFIL").Copy
    ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlText
    ActiveSheet.Name = "FFIL"

Bravo! This version of the code produced exactly what I was looking for. I am able to:

Use the Save As icon in the Quick Access Toolbar
The dialog box automatically populates the desired name of the newly created file
The file extension is correctly selected in the drop-down area for Save as type:
The code also worked exactly as desired for the other file creations which occur earlier in the macro that have a different file extension (.csv file, just modified the code accordingly and it worked like a charm)

The following observation is more preference than anything else but I did notice when I click the Save As icon and the dialog box appears (with everything perfectly the way I wanted it) the default location it continues to open where it thinks I want to save the file to, is My Documents. The reason this is not important is because the user will navigate to a destination directory. That said, would you happen to know how to get that default directory it always opens to, to be the Downloads directory instead?

Otherwise I'm super happy with the results and I thank you kindly for your engagement.

 

[GWteB]

One could argue that's splitting hairs from the non-expert's point of view but I appreciate the syntax correction nevertheless.

You are welcome! ?

The end goal is for the user to choose what the destination directory will be when saving the document but have the name already populated such that they won't have to type the name in manually.

In a strict sense this is not possible. Let me explain. What is on your screen in Excel after you have copied the worksheet FFIL to a new workbook (indeed a workbook, and indeed kind of hair splitting on my part because at that moment the data isn't actually yet secured on disk) it's a view from the PC's memory in Excel's own (internal) protocol.

Excel already gives this data a standard name, visible in the title bar as Book1, Book2, Book3 and so on. These names cannot be changed up front. The moment we save such a workbook to disk, only then we are able to change the (final) name of the file and its (binary) format (xlsx, xlam, csv, txt, ...). So when we click on Save As the eponymous dialog appears and Excel offers a proposal for both file name and file format. This proposal we cannot change in advance.

Now you're probably going to say right away: rvl01's code does this.
Unfortunately, this seems so. His code is a derivative of your code and in that code, in the following line:

ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlText

the workbook on screen is actually being saved to disk with a given name and file format. After that particular moment, the data has both a name and a format, so on a second (!!) Save As attempt using Excel's Save As dialog, Excel will show you the proposal as per your requirement with regard to name and format. Since the file is already on disk, there is no need for the user to use the Save As dialog. In my opinion, a simple Save (disk icon on QAT) will suffice. In the latter case, it is recommended to place the file immediately in the desired folder, which you already thought of yourself. We can enforce this through code.

The moment we want to choose a folder through a dialog prior to saving, which is apparently requested, rlv01's post #3 code provides that (provided the user doesn't press cancel). In this particular case I would have wrote code myself in this way too.

The above also means that your observation quoted below is an erroneous assumption.

I did notice when I click the Save As icon and the dialog box appears (with everything perfectly the way I wanted it) the default location it continues to open where it thinks I want to save the file to, is My Documents.

Excel doesn't think what you want, the file is already saved there. In such a scenario, enabling users to navigate to another folder, this also means you would produce two copies of each file, one through your code and one by user's choice.

I'd recommend going for rlv01's code, in a slightly modified form.

Sub rlv01_post3()
    Dim FName As String
    
    FName = Environ("USERPROFILE") & "\Downloads\" & Format(Now(), "mm.dd.yy-hhnn-UPL") & ".txt"
    FName = Application.GetSaveAsFilename(InitialFileName:=FName, fileFilter:="Text Files (*.txt), *.txt")
    
    If FName <> "False" Then
        Sheets("FFIL").Copy
        ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlText
        ActiveSheet.Name = "FFIL"
        ActiveWorkbook.Save
    End If
End Sub

 

[censo]

I'd recommend going for rlv01's code, in a slightly modified form.

Love the in-depth explanation. The modified code accomplishes my desired result and I'm okay with duplicate copies of the file being created in the process because the Download directory gets purged periodically, which is why I preferred if Excel needed to first save a copy in order to accomplish my objective, that it would be done in that directory.

I'm all squared away now, thank you both for your contributions. Much appreciated.