Help with a VBA to process file save and PDF creation with existing file handling

[Morphies]

Good evening all. I hope you're all well!

I'm looking for some assistance with a vba macro I've been working on. I'm no expert with this (as will become apparent!)

Essentially what I'm trying to do is save a file and publish it to a certain folder in pdf format based on cell values. I've managed to get the macro to save the file and publish the pdf, then added an error handler to waarn for existing file names.

I now want to further develop the macro to allow overwrites of existing files by way of an ignore response to a message box. I also want the macro to check for the existance of a folder and create it if it doesn't exist.

This is the initial code i had:

[COLOR=#000000]Sub SAVEREPORTANDPDF()[/COLOR]
Dim FileName As String
Dim Path As String

Path = "R:\"
FileName = Range("AU10").Value & ".xlsm" ' sets the filename to the value of cell AU10 and adds the extension


If Dir(Path & FileName) <> "" Then 'checks for existance of file you are about to create
MsgBox "File already exists. Please use correct revision number or investigate duplicate file." 'warns user file already exists

Else

'start to save the excel file
Path = "R:\" '
FileName = Range("AU10").Value & ".xlsm" 'sets the filename as the value of cell AU10 and sets the extension
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbookMacroEnabled 'selects the file format

'now create the pdf. the Filename range selects the cell contents as the filename, note this is an absolute path and extension in this case.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Range("AU15").Value, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    
End If
End Sub

I've now 'developed' this into this:

Sub SAVEREPORTANDPDF()


Dim FileName As String
Dim Path As String
Dim Cust As String
Dim pdfpath As String
Dim iret As Integer


Path = "R:\"
FileName = Range("AU10").Value & ".xlsm" ' sets the filename to the value of cell AU10 and adds the extension
Cust = Range("B11").Value
pdfpath = "X:\"
If Dir(Path & FileName) <> "" Then 'checks for existance of file you are about to create
iret = MsgBox("This file already exists. Please use correct revision number or check for duplicates. Click Ignore to overwrite the existing file.", vbAbortRetryIgnore) 'warns user file already exists
    ElseIf iret <> "5" Then 'processes user input
    Exit Sub
        ElseIf Len(Dir(pdfpath & Cust, vbDirectory)) = 0 Then 'create directory for new customers
        MkDir (pdfpath & Cust)
Else


'start to save the excel file
Path = "R:\" '
FileName = Range("AU10").Value & ".xlsm" 'sets the filename as the value of cell AU10 and sets the extension
ActiveWorkbook.SaveAs Path & FileName, xlOpenXMLWorkbookMacroEnabled 'selects the file format


'now create the pdf. the Filename range selects the cell contents as the filename, note this is an absolute path and extension in this case.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=Range("AU15").Value, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    
        End If


End Sub

But, I'm now lost in my own mess.

 

[Morphies]

This is an extremely fast moving forum, surprisingly I couldn't find any rules on bumping threads? Ordinarily 30 minutes would be far to quick but it's already half way down page 2!

 

[Morphies]

incase this happens to help anyone out I managed to get it working by moving part of the code into another routine. I'm sure its very messy but it appears to work

Sub SAVEREPORTANDPDF()

Dim filename As String
Dim path As String
Dim Cust As String
Dim pdfpath As String
Dim Iret As Integer


path = "R:\"
filename = Range("AU10").Value & ".xlsm" ' sets the filename to the value of cell AU10 and adds the extension
Cust = Range("B11").Value
pdfpath = "X:\"


If Len(Dir(pdfpath & Cust, vbDirectory)) = 0 Then 'create directory for new customers
MkDir (pdfpath & Cust)
End If


If Dir(path & filename) = "" Then 'checks for existance of file you are about to create
Call saveexcelcreatepdf
Exit Sub
Else
Iret = MsgBox("This file already exists. Please use correct revision number or check for duplicates. Click Ignore to overwrite the existing file.", vbAbortRetryIgnore + vbInformation) 'warns user file already exists
If Iret <> "5" Then 'processes user input
Exit Sub
Else
Call saveexcelcreatepdf


End If
End If
End Sub


Sub saveexcelcreatepdf()
Dim path As String
Dim filename As String


path = "R:\"
filename = Range("AU10").Value & ".xlsm" 'sets the filename as the value of cell AU10 and sets the extension
ActiveWorkbook.SaveAs path & filename, xlOpenXMLWorkbookMacroEnabled 'selects the file format


'now create the pdf. the Filename range selects the cell contents as the filename, note this is an absolute path and extension in this case.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:=Range("AU15").Value, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


End Sub