Export image file - Change file path and warning message for similar names

[Pepito_child]

Hello dear experts,

I kindly ask for your support!

How should the bellow export file code be changed with a different path, instead of "Desktop" to this new path from server: X:\Production\PLAN?
Is there any idea of how it could also have a warning message when it will already exist a similar file name in this new path - e.g. replace or skip files "The destination already has a file named "xxxxx.png"?

Thank you and I appreciate your hard work here!

Sub ExportImage()

Dim sFilePath As String

Dim sView As String

sView = ActiveWindow.View

ActiveWindow.View = xlNormalView

Application.ScreenUpdating = False

Set Sheet = ActiveSheet

sFilePath = CreateObject("WScript.Shell").specialfolders("Desktop") & "\" & Range("M2") & ".png"

zoom_coef = 300 / Sheet.Parent.Windows(1).Zoom

Set area = Sheet.Range("M1:X27")

area.CopyPicture xlPrinter

Set chartobj = Sheet.ChartObjects.Add(0, 0, area.Width * zoom_coef, area.Height * zoom_coef)
chartobj.Activate
chartobj.Chart.Paste

chartobj.Chart.Export sFilePath, "png"

chartobj.Delete

ActiveWindow.View = sView

Application.ScreenUpdating = True

MsgBox ("Export completed! The file can be found here:" & Chr(10) & Chr(10) & sFilePath)

End Sub

 

[Domenic]

Here are two examples. The first one simply checks whether the file already exists. If so, it asks the user whether to replace the existing file. If so, the existing file is replaced. If not, it simply exits the sub. The second one also checks whether the file already exists. And, it also asks the user whether to replace the existing file. However, if not, it gives the user an opportunity to change the path and/or filename.

[Example One]

Sub ExportImage()

    Dim sFilePath As String
    Dim ans As VbMsgBoxResult

    sFilePath = "X:\Production\PLAN\" & Range("M2").Value & ".png"

    If Len(Dir(sFilePath, vbNormal)) > 0 Then
        ans = MsgBox(sFilePath & " already exists, replace?", vbQuestion + vbYesNo + vbDefaultButton2, "Replace?")
        If ans = vbNo Then Exit Sub
    End If

    'Your code here

End Sub

[Example Two]

Sub ExportImage2()

    Dim sFilePath As Variant
    Dim ans As VbMsgBoxResult
    Dim proceed As Boolean

    sFilePath = "X:\Production\PLAN\" & Range("M2").Value & ".png"

    proceed = False
    Do
        If Len(Dir(sFilePath, vbNormal)) = 0 Then
            proceed = True
        Else
            ans = MsgBox("'" & sFilePath & "' already exists, replace?", vbQuestion + vbYesNo + vbDefaultButton2, "Replace?")
            Select Case ans
                Case vbYes
                    proceed = True
                Case Else
                    sFilePath = Application.GetSaveAsFilename(InitialFileName:=sFilePath, FileFilter:="Image File (*.png), *.png", Title:="Save As")
                    If sFilePath = False Then
                        Exit Sub
                    End If
            End Select
        End If
    Loop Until proceed

    'Your code here

End Sub

Hope this helps!

 

[Pepito_child]

Here are two examples. The first one simply checks whether the file already exists. If so, it asks the user whether to replace the existing file. If so, the existing file is replaced. If not, it simply exits the sub. The second one also checks whether the file already exists. And, it also asks the user whether to replace the existing file. However, if not, it gives the user an opportunity to change the path and/or filename.

[Example One]

Sub ExportImage()

    Dim sFilePath As String
    Dim ans As VbMsgBoxResult

    sFilePath = "X:\Production\PLAN\" & Range("M2").Value & ".png"

    If Len(Dir(sFilePath, vbNormal)) > 0 Then
        ans = MsgBox(sFilePath & " already exists, replace?", vbQuestion + vbYesNo + vbDefaultButton2, "Replace?")
        If ans = vbNo Then Exit Sub
    End If

    'Your code here

End Sub

[Example Two]

Sub ExportImage2()

    Dim sFilePath As Variant
    Dim ans As VbMsgBoxResult
    Dim proceed As Boolean

    sFilePath = "X:\Production\PLAN\" & Range("M2").Value & ".png"

    proceed = False
    Do
        If Len(Dir(sFilePath, vbNormal)) = 0 Then
            proceed = True
        Else
            ans = MsgBox("'" & sFilePath & "' already exists, replace?", vbQuestion + vbYesNo + vbDefaultButton2, "Replace?")
            Select Case ans
                Case vbYes
                    proceed = True
                Case Else
                    sFilePath = Application.GetSaveAsFilename(InitialFileName:=sFilePath, FileFilter:="Image File (*.png), *.png", Title:="Save As")
                    If sFilePath = False Then
                        Exit Sub
                    End If
            End Select
        End If
    Loop Until proceed

    'Your code here

End Sub

Hope this helps!

Thank you, Domenic!
Your too generous!
I will test them tomorrow and let you know how it goes.

 

[Domenic]

You're very welcome, cheers!

 

[Pepito_child]

You're very welcome, cheers!

I was very happy and I didn't wait until tomorrow.
I decided to use the second option to have a backup. It works like a charm!

Thank you very much for your efforts!

 

[Domenic]

That's great to hear, glad I could help, and thanks for the feedback.

Cheers!