I am having an issue in Microsoft Access 2007.
I wrote some code that takes data and exports it to Excel. When this code runs it prompts the user to enter a file name using Application.Filedialog(2).
The problem is when you type in the file name without an extension (.xls or .xlsx) and a file with that same name exists in that folder it will overwrite it without a prompt. If you type in the extension .xls or .xlsx and that file name with that extension exists it does give you the overwrite prompt.
I need a work around for this.
I found this on a forum which describes the same exact problem I am experiencing.
And Microsoft describes how to work around it here but this is not working for me or I am doing it wrong.
I have attached the original code I used which results in the file being overwritten.
Private Sub Command20_Click()
Dim filenm As String, commonDlg As Object
filenm = ""
Set commonDlg = Application.FileDialog(2)
.AllowMultiSelect = False
.InitialFileName = "F:\Filename"
.Title = "Please select a folder and give the file a name"
If .Show = True Then
filenm = Trim(.SelectedItems.Item(1))
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QRY_SearchAll2", filenm
msgbox "Your spreadsheet was saved successfully"
msgbox "You clicked Cancel in the file dialog box."
I tried adding this to my code but it just crashes with the error "Run-time error 438 object doesn't support this property or method"
.CreatePrompt = True
.OverwritePrompt = True
Thanks in advance for any help!