Exporting with custom name

[Design by Sue]

I have created 2 reports one that needs to be exported as an excel file and the other report as a PDF file. I have the exports working correctly for each on a button except this saves the files with the name of the report. The client wants the name of the file to appear as one of the fields on the report (the info that is in the description text box for example). I have thought of two ways to do this and neither seems to be great - one is create a copy of the report with the new name and then export that saved file then delete the renamed report OR similar to that but instead rename the file save and rename it back (this causes all kinds of problems if the user cancels out that would need to be handled in the error handling - this is not a good way to go. Can anyone suggest an good way to do this??


Thanks
Sue

 

[MarkK]

I don't understand this description of the process . . .

rename the file save and rename it back (this causes all kinds of problems if the user cancels out that would need to be handled in the error handling

If you know the saved filename, and the file was actually saved, can't you just rename it at that point, like . . .

With CreateObject("Scripting.FileSystemObject")
   .GetFile("C:\Path\SavedFile.txt").Name = "NewName.txt"
End With

 

[Design by Sue]

Thanks - but we will not know the path to the saved document.

 

[MarkK]

Your original post suggests you know the names of the files you export . . .

I have the exports working correctly for each on a button except this saves the files with the name of the report.

If you don't know the path to where they are saved, how do you know you "have the exports working?" What path did you check to verify this?

 

[Design by Sue]

When the file is saved, a browse window opens. In the browse window the file is automatically named with the name of the report and the user browses to where they want to save the file. (The user can manually change the name but we want to avoid this extra step) The could browse and save the file anywhere on their computer - it will not always be the same path.

The code we have tried that that renames the report and then does the export then renames the file back is as follows. In this case the original report names is Finance RPT. What we need the default in the browse window to be instead of "Finance RPT.xls" would be the info in the P Description, Columns 0 and 2. There seems there would be a better way than this.

Dim stDocName As String
stDocName = Me.[P Description].[Column](0) & " - " & Me.[P Description].[Column](2)
 
stDocName = RemoveIllegalFileCharacters(stDocName)
DoCmd.Rename stDocName, acReport, "Finance RPT"
DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS
DoCmd.Rename "Finance RPT", acReport, stDocName

Sue

 

[MarkK]

There are more parameters to the DoCmd.OutputTo method. You can specify a filename there, so you could do . . .

Dim stDocName As String
 
stDocName = RemoveIllegalFileCharacters(Me.PDescription.Column(0) & "-" & Me.PDescription.Column(2))
DoCmd.OutputTo acOutputReport, yourReportName, acFormatXLS, [COLOR="Red"]"C:\SomePath\" & stDocName[/COLOR]

Is that the kind of thing you are looking for?

 

[Design by Sue]

Close but this doesn't give the user a window to select the location - the location needs to be programmed in - and I can't do that.

Sue

 

[Gasman]

Close but this doesn't give the user a window to select the location - the location needs to be programmed in - and I can't do that.

Sue

PMFJI, Why can't you do something like this? That way you have the folder path and just add whatever name you require?

Amended from https://msdn.microsoft.com/en-us/library/office/aa219843(v=office.11).aspx

Sub PickFolder()

    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog

    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is a String,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd

        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the action button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is a String that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example simply displays the path in a message box.
                MsgBox "The path is: " & vrtSelectedItem

            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing

End Sub

 

[Minty]

I know this will sound a little draconian, but I would specify that the file is stored in a root level folder like c:\savedfiles .
You can check and make sure the folder exists and then save your file there , rename it ,version it what ever you like. You can also then add a command button to open the location in explorer so the end user can open it/email it/delete it as they wish.