Vba Insert Picture Not Work in. PNG format

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,766
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
hi all..

i have macro code to insert picture..actually, this macro work properly in only jpg format..
how to modified this macro can work in both .jpg and .png format.
here this code
VBA Code:
Sub Button1_Click()
Dim pic As Picture, rng As Range
For Each pic In ActiveSheet.Pictures
    Set rng = Range(pic.TopLeftCell.Address, pic.BottomRightCell.Address)
    If Not Intersect(rng, Range("B6:F6")) Is Nothing Then
        pic.Delete
    End If
Next pic
End Sub

Sub Button2_Click()
On Error GoTo NOT_SHAPE
    Dim PicWtoHRatio As Single
    Dim CellWtoHRatio As Single
    Dim cel As Range
    Selection.Copy
    Set cel = Application.InputBox("Click on destination tab and cell", "", , , , , , 8)
    With cel
        .Parent.Activate
        .Activate
        .Parent.Paste
    End With
   
    With Selection
        PicWtoHRatio = .Width / .Height
    End With
    With cel
        CellWtoHRatio = .Width / .RowHeight
    End With
    Select Case PicWtoHRatio / CellWtoHRatio
        Case Is > 1
            With Selection
            .Width = cel.Width
            .Height = .Width / PicWtoHRatio
            End With
        Case Else
            With Selection
            .Height = cel.RowHeight
            .Width = .Height * PicWtoHRatio
            End With
    End Select
    With Selection
        .Top = cel.Top
        .Left = cel.Left
    End With
Exit Sub
NOT_SHAPE:
MsgBox "Select a picture before running this macro."
End Sub

any help me out . thanks in advance..

.sst
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,766
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
hi all...
how to rename extension file (.jpg) to .png format using Vba..
i found code below but not work, how to fix it..
VBA Code:
Option Explicit
Sub test()
Dim strSourceFile As String
    Dim strSourceDirectory As String
    Dim counter As Long
    
    strSourceDirectory = "C:\Test\ok"
        
    strSourceFile = Dir(strSourceDirectory & "*.jpg")
    
    Do While strSourceFile <> ""
        Name strSourceDirectory & strSourceFile As strSourceDirectory & Replace$(strSourceFile, [B]".jpg", ".png[/B]")
        counter = counter + 1
        strSourceFile = Dir()
    Loop
    
    MsgBox counter & " files renamed.", , "Rename Files Complete"
    End Sub

my images in folder c:/Test\ok..
or the other code with the same function

any help, greatly appreciated.

.sst
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,828
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Why do you want to change it's extension? just renaming a Jpeg's extension to a Png extension doesn't convert it to a Png :unsure:
 

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,766
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
this is in related with my previous a post...then my filename too much if use by manually

 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,828
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

What makes you think that it is the extension causing the issue?
 

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,766
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
cause when the macro running and browse location, only .png can't showing otherwise .jpg is showing
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,828
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

If your png is in the intersect range then your png file deletes fine for me when using your delete code so Excel is detecting it.

Which of your codes isn't working?
 

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
1,766
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
sorry Mark858,....I'm newbie about macro code
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,828
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
sorry Mark858,....I'm newbie about macro code
If your png is in the intersect range then your png file deletes fine for me
Your delete code is restricted to only the picture intersecting with the range B6:F6, if you have a Jpeg not intersecting with the range then it won't delete, exactly the same.

Maybe it would be better to explain in words exactly what you want the codes to do rather than trying to adapt code that doesn't do what you want.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,509
Messages
5,596,561
Members
414,077
Latest member
ammylar5

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