suppress run-time error when cancel is chosen in save file dialog (1 Viewer)

anski · May 20, 2018

hi. i copied this code from someone and did some changes. it works fine to export my query to a csv-formatted file. the Save File dialog box pops up. when i choose cancel, the 2522 run-time error message appears. what can i do to suppress this message? tia.

Private Sub Command42_Click()
Dim fd As Object, csvPath As String, strCsv As String
strCsv = "MYFILE.CSV"
Set fd = Application.FileDialog(2)
With fd
.Title = "Save File As"
.ButtonName = "Save As"
.InitialFileName = CurrentProject.Path & "" & strCsv
If .Show Then
csvPath = .SelectedItems(1)
End If
End With
DoCmd.TransferText acExportDelim, , "Qpurch_detail", csvPath, False
Name strCsv As "myfile.xyz"

MsgBox "Export successful.", vbOKOnly
End Sub

arnelgp · May 20, 2018

Add a condition statement:

...
...
If csvPath & "" <> vbNullString Then
DoCmd.TransferText acExportDelim, , "Qpurch_detail", csvPath, False
Name strCsv As "myfile.xyz"

MsgBox "Export successful.", vbOKOnly

Else
Msgbox "Export was cancelled"
End If

June7 · May 20, 2018

Bing: access vba filedialog cancel

Review: http://www.utteraccess.com/forum/index.php?showtopic=1854103

Change code to:

Code:

[FONT=Arial]If .Show <> 0 Then[/FONT]
[FONT=Arial]    csvPath = .SelectedItems(1)
    DoCmd.TransferText acExportDelim, , "Qpurch_detail", csvPath, False
    Name strCsv As "myfile.xyz"[/FONT]
[FONT=Arial]    MsgBox "Export successful.", vbOKOnly[/FONT]
[FONT=Arial]End If
[/FONT]

Please use CODE tags when posting code to retain indentation and readability.

anski · May 20, 2018

thanks! both work!
what is the syntax to bring up the dialog box to just choose where to save the file? i realized that i do not want the user to be able to make up his own filename on generation. and, what do i add to automatically replace existing files? (this is for the line: Name strCsv As "myfile.xyz". a user can run the process more than once; therefore generating the same filename again.). tia.

June7 · May 20, 2018

Isn't that what you are doing? The FileDialog allows user to browse to and select desired folder location. Code exports file to that location then renames the file. Instead of renaming, include the desired output file name in the TransferText method FileName argument.

DoCmd.TransferText acExportDelim, , "Qpurch_detail", csvPath & "\" & "Myfile.xyz", False

CJ_London · May 20, 2018

what is the syntax to bring up the dialog box to just choose where to save the file?

basically the same code, just a different number after filedialog - I can't remember what it is, google 'vba filedialog' to find all the options

anski · May 20, 2018

June7 said:
Isn't that what you are doing? The FileDialog allows user to browse to and select desired folder location. Code exports file to that location then renames the file. Instead of renaming, include the desired output file name in the TransferText method FileName argument.

DoCmd.TransferText acExportDelim, , "Qpurch_detail", csvPath & "" & "Myfile.xyz", False

^that syntax does not work. access will only accept a filename with an extension of .csv. this is why i had to resort to the rename command (i need a filename with an extension that is not.csv but csv formatted)

the save as filedialog allows the user to select the desired folder location AND also allows the user to use his own filename (supersede/change the filename that is already in the File Name field).
.

June7 · May 20, 2018

Okay, as CJ_London suggested, change the FileDialog mode. Review: https://msdn.microsoft.com/en-us/VBA/Office-Shared-VBA/articles/msofiledialogtype-enumeration-office

arnelgp · May 21, 2018

Const msoFileDialogFilePicker As Integer = 3
Const msoFileDialogFolderPicker As Integer = 4
Const msoFileDialogOpen As Integer = 1
Const msoFileDialogSaveAs As Integer = 2

You need msoFileDialogSaveAs

suppress run-time error when cancel is chosen in save file dialog (1 Viewer)

anski

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

June7

AWF VIP

anski

Registered User.

June7

AWF VIP

CJ_London

Super Moderator

anski

Registered User.

June7

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread