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

Pepito_child

New Member
Joined
Feb 13, 2016
Messages
49
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,495
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]

VBA Code:
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]

VBA Code:
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!
 
Solution

Pepito_child

New Member
Joined
Feb 13, 2016
Messages
49
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]

VBA Code:
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]

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,499
Messages
5,636,688
Members
416,935
Latest member
Atulcp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top