Importing images into a range of cells using VBA Macro code...

In an Excel workbook I have a VBA Macro code that imports images into a designated cell range via a command button and I was wondering as someone with very little experience in VBA programming how I could rewrite the program to reference the file path of these images from designated cells in the workbook?


Sub protest()
        Sheets("Sheet1").Select
        Range("C1").Select
 
        Do Until Selection.Offset(0, -2).Value = ""
                Selection.Value = Selection.Offset(0, -2).Value & "" & Selection.Offset(0, -1)
                Selection.Offset(1, 0).Select
        Loop
 
        Range("A1").Select
 
End Sub

Open in new window

mikejcurtisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

athersaleemCommented:
PUT FULL FILE NAME/PATH LIKE .......  C:\PICTURE.JPG in cell  A1, the picture will appear in A5
For more than one pic , the loop can be build but need to know sheet format,etc

=================================================================================
Sub PicInsert()
    PICPATH = Range("A1").Value
    Range("A5").Select
    ActiveSheet.Pictures.Insert(PICPATH).Select
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
Incidentally, the code you posted has nothing to do with inserting images so it doesn't help us much! Can you post the actual routine you use? :)
Rory
0
mikejcurtisAuthor Commented:
Yes, woops on the code paste. Here's the code we're trying to use;
The Insert Picture Code minus the command buttons. . .

 

 

Sub TestInsertPictureInRange1()

    InsertPictureInRange "C:\Documents and Settings\bbaldus.BAC-WORLD\My Documents\My Pictures\90-020808.bmp", _

        Range("B18:E33")

End Sub

 

Sub InsertPictureInRange1(PictureFileName As String, TargetCells As Range)

' inserts a picture and resizes it to fit the TargetCells range

Dim p As Object, t As Double, l As Double, w As Double, h As Double

    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

    If Dir(PictureFileName) = "" Then Exit Sub

    ' import picture

    Set p = ActiveSheet.Pictures.Insert(PictureFileName)

    ' determine positions

    With TargetCells

        t = .Top

        l = .Left

        w = .Offset(0, .Columns.Count).Left - .Left

        h = .Offset(.Rows.Count, 0).Top - .Top

    End With

    ' position picture

    With p

        .Top = t

        .Left = l

        .Width = w

        .Height = h

    End With

    Set p = Nothing

End Sub

 

Sub TestInsertPictureInRange2()

    InsertPictureInRange "C:\Documents and Settings\bbaldus.BAC-WORLD\My Documents\My Pictures\180-020808.bmp", _

        Range("I18:O33")

End Sub

 

Sub InsertPictureInRange2(PictureFileName As String, TargetCells As Range)

' inserts a picture and resizes it to fit the TargetCells range

Dim p As Object, t As Double, l As Double, w As Double, h As Double

    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub

    If Dir(PictureFileName) = "" Then Exit Sub

    ' import picture

    Set p = ActiveSheet.Pictures.Insert(PictureFileName)

    ' determine positions

    With TargetCells

        t = .Top

        l = .Left

        w = .Offset(0, .Columns.Count).Left - .Left

        h = .Offset(.Rows.Count, 0).Top - .Top

    End With

    ' position picture

    With p

        .Top = t

        .Left = l

        .Width = w

        .Height = h

    End With

    Set p = Nothing

End Sub

0
Rory ArchibaldCommented:
Well the easiest way would be to change your calling macro(s) to something like this:

Sub TestInsertPictureInRange1()
   Dim strPath as String
   ' Assumes A1 contains the path
   strPath = Range("A1").Value
   if right$(strPath, 1) <> "\" then strPath = strPath & "\"
    InsertPictureInRange strPath & "90-020808.bmp", Range("B18:E33")

End Sub

Regards,
Rory
0
mikejcurtisAuthor Commented:
We were able to use your code successfully. Thank you, We had already implimented by the time we saw the second answer.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.