It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.
Need to prompt to save file from VBA if file exists
I have a function to create and save a PDF file. The file name is the company name + the date. The creates a "request " file. If two requests are amde for the same day, the first file gets overwritten. How do I prompt the user if the file name already exists?
Function email_binder()
Dim db As Database
Dim rs As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim address As String
Dim EmailTo As String
Dim EmailCc As String
Dim EmailSubject As String
Dim EmailMsg As String
Dim EmailAttach As String
Dim DisplayEmail As Boolean
Dim spar1 As String
Dim spar2 As String
Dim spar3 As String
Dim spar4 As String
Dim Cust As String
Dim strAttachment As String
Dim strReport As String
On Error Resume Next
Cust = Form_Client.Text82
address = Form_Client.Text115
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * " & _
"FROM info " & _
"WHERE id = 1")
strAttachment = Application.CurrentProject
& " " & Format(Date, "m-d-yyyy") & ".pdf"
Debug.Print "Attachment file name and path: " & strAttachment
strReport = "rpt_request"
DoCmd.OutputTo objecttype:=acOutputReport
objectname:=strReport, _
outputformat:=acFormatPDF,
outputfile:=strAttachment
End function
There is more code for emailing the file, but this is the creation of the file.
Function email_binder()
Dim db As Database
Dim rs As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim address As String
Dim EmailTo As String
Dim EmailCc As String
Dim EmailSubject As String
Dim EmailMsg As String
Dim EmailAttach As String
Dim DisplayEmail As Boolean
Dim spar1 As String
Dim spar2 As String
Dim spar3 As String
Dim spar4 As String
Dim Cust As String
Dim strAttachment As String
Dim strReport As String
On Error Resume Next
Cust = Form_Client.Text82
address = Form_Client.Text115
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * " & _
"FROM info " & _
"WHERE id = 1")
strAttachment = Application.CurrentProject
& " " & Format(Date, "m-d-yyyy") & ".pdf"
Debug.Print "Attachment file name and path: " & strAttachment
strReport = "rpt_request"
DoCmd.OutputTo objecttype:=acOutputReport
objectname:=strReport, _
outputformat:=acFormatPDF,
outputfile:=strAttachment
End function
There is more code for emailing the file, but this is the creation of the file.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
strAttachment = Application.CurrentProject.Path & "\Requests\Request " & Cust _
& " " & Format(Date, "m-d-yyyy") & ".pdf"
If dir(strAttachment) = "" then
strAttachment = SaveAsDialog()
end if
Function SaveAsDialog() As String
Dim dlgSaveAs As Object
Set dlgSaveAs = Application.FileDialog(2)
With dlgSaveAs
.InitialFileName = "C:\docs\this is the default name"
.Show
End With
SaveAsDialog = dlgSaveAs.SelectedItems(1)
End Function
Premium Content
You need an Expert Office subscription to comment.Start Free Trial