vba to save wksht as PDF but remove disallowed filename characters first

[cjcass]

Hi,
I have the following code that saves an excel worksheet as a PDF and give the user the ability to change a pre-defined filename using a cell value.
Can I have the code so it automatically changes a / (fwd slash) in the pre-defined filename to a , (comma)?
Thanks

Dim Opendialog As Variant


Opendialog = Application.GetSaveAsFilename(Sheet18.[B1].Value, "PDF (*.pdf), *.pdf")


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Opendialog _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

 

[louisH]

Hi,

Dim Opendialog As String

Opendialog = Application.GetSaveAsFilename(Replace(Sheet18.[B1].Value, "/", ","), "PDF (*.pdf), *.pdf")

Opendialog = Replace(Opendialog, "/", ",")

Isn't a comma also a forbidden character ? I'm not sure.

 

[cjcass]

Ok, thanks a lot that works well.
I've just spotted something else though that's not great with this code - when you get to the point of pressing 'Save' but you decide to 'Cancel' and not save the PDF, it still saves it and names the PDF 'FALSE'
Any thoughts on how I could get round this?

 

[louisH]

After your Opendialog line :

If Opendialog = "False" Then Exit Sub

 

[cjcass]

Brilliant, one final thing I've just noticed is that if you try and save a file with the same filename as an existing file it doesn't prompt you to see if you want to overwrite it, it just overwrites it automatically, any thoughts?

 

[louisH]

After Opendialog :

    If Dir(Opendialog) <> "" Then
        Err = MsgBox("File already exists. Do you want to overwrite existing file ?", vbQuestion + vbYesNo, "File already exists")
        If Err = vbNo Then Exit Sub
    End If

 

[cjcass]

That's great, thanks for your time on this, much appreciated