Inserting multiple images into a worksheet

[Nat]

Version: 2004

Hello

New to Excel 2004 - regular user of Excel 2007

I have a number of macros in a spreadhseet which were written for MS Excel 2007. They work fine on my pc.

One of the users of the spreadsheet is on a Mac using Excel 2004.

The following Excel 2007 macro (which inserts all jpg images contained in a folder in a directory into a worksheet - noting there will be a variable number of images each time the macro is run - hence reason they are not individually named) works fine in 2007 but does nothing in Excel 2004.

Suspect it has to do with my limited mac knowledge - especially about naming conventions and/or the use of the wildcard. Or maybe the file extension (something other than jpg perhaps). Yep, struggling to know what!!!

Any advice, or code which will work (i.e grab all images from a folder, insert them into one worksheet and then ensure all the same size)would be greatly appreciated.

The location of the jpgs on the Mac is Macintosh HD:Users:Rocuments:Atlashotos

Here is the 2007 code
Sub InsertPix()
'Inserts all pictures from selected directory on top of each other in worksheet.
Dim MyPath As String
Dim FName As String
MyPath = "C:\Users\Nat\Templates\"
FName = Dir(MyPath & "*.jpg", vbNormal)
Do While Len(FName) > 0
ActiveSheet.Pictures.Insert(MyPath & FName).Select
Selection.BottomRightCell.Offset(20).End(xlToLeft).Select
FName = Dir
Loop
'code to make all images the same size
For Each shp In ActiveWorkbook.ActiveSheet.Shapes
wfactor = 7 * 72 / shp.Width
shp.Height = shp.Height * wfactor
shp.Width = shp.Width * wfactor
Next shp
End Sub

Obviously I amended the file location in MyPath to "Macintosh HD:Users:Rocuments:Atlashotos"

Thanks heaps. Regards

 

[JE McGimpsey]

Version: 2004

Hello

New to Excel 2004 - regular user of Excel 2007

I have a number of macros in a spreadhseet which were written for MS Excel
2007. They work fine on my pc.

One of the users of the spreadsheet is on a Mac using Excel 2004.

The following Excel 2007 macro (which inserts all jpg images contained in a
folder in a directory into a worksheet - noting there will be a variable
number of images each time the macro is run - hence reason they are not
individually named) works fine in 2007 but does nothing in Excel 2004.

Suspect it has to do with my limited mac knowledge - especially about naming
conventions and/or the use of the wildcard. Or maybe the file extension
(something other than jpg perhaps). Yep, struggling to know what!!!

Any advice, or code which will work (i.e grab all images from a folder,
insert them into one worksheet and then ensure all the same size)would be
greatly appreciated.

The location of the jpgs on the Mac is Macintosh
HD:Users:Rocuments:Atlashotos

Since '*' is a valid character in Mac filenames, you can't use it as a
wild card on Macs. While you COULD use the creator type instead (see
Help for an explanation), I suspect you'll do just as well with
something like this (which should also work with XL07, except for the
path, of course):

Public Sub InsertPix()
Const MyPath = "Macintosh HD:Users:Rocuments:Atlashotos:"
Const cnWidth As Long = 504 ' = 7 * 72
Dim FName As String
Dim nTop As Long

FName = Dir(MyPath, vbNormal)
With ActiveSheet.Pictures
Do While Len(FName) > 0
If FName Like "*.jpg" Then
With .Insert(MyPath & FName)
.Top = nTop
.Left = 0
.Height = .Height * cnWidth / .Width
.Width = cnWidth
nTop = .BottomRightCell.Offset(20).Top
End With
End If
FName = Dir
Loop
End With
End Sub

Note that this positions and sizes in one step. You may find that a
different method of calculation nTop is optimal.

Also, any time you can deal with objects directly, rather than
selections, you're generally better off.

 

[Nat]

Thanks heaps for your prompt reply and advice. I will try it out in a few days (no access to a Mac until then) and advise on success.
Regards

 

[Nat]

Hey - thanks very much for your assistance. With a minor modification to the insert pictures line of code it works a treat.
Code below
Regards

Sub MacInsertPhotos()

Const MyPath = "Macintosh HD:Users:Rocuments:Atlashotos:"
Const cnWidth As Long = 200
Dim FName As String
Dim nTop As Long
Dim Sh As Shape

FName = Dir(MyPath, vbNormal)
Sheets("Photos").Select
With Worksheets("Photos")
Do While Len(FName) > 0
If FName Like "*.JPG" Then
With ActiveSheet.Pictures.Insert(MyPath & FName)
.Top = nTop
.Left = 0
.Height = .Height * cnWidth / .Width
.Width = cnWidth
nTop = .BottomRightCell.Offset(1).Top
End With
End If
FName = Dir
Loop
End With

End Sub

 

[JE McGimpsey]

Hey - thanks very much for your assistance. With a minor modification to the
insert pictures line of code it works a treat.
Code below

Glad that worked for you - if you'd like to do it without having to do
any selections:

Public Sub MacInsertPhotos()
Const MyPath = "Macintosh HD:Users:Rocuments:Atlashotos:"
Const cnWidth As Long = 200
Dim FName As String
Dim nTop As Long
Dim Sh As Shape

FName = Dir(MyPath, vbNormal)
With Worksheets("Photos").Pictures
Do While Len(FName) > 0
If UCase(FName) Like "*.JPG" Then
With .Insert(MyPath & FName)
.Top = nTop
.Left = 0
.Height = .Height * cnWidth / .Width
.Width = cnWidth
nTop = .BottomRightCell.Offset(1).Top
End With
End If
FName = Dir
Loop
End With
End Sub

 

[RichEditor]

Glad that worked for you - if you'd like to do it without having to do
any selections:

Public Sub MacInsertPhotos()
Const MyPath = "Macintosh HD:Users:Rocuments:Atlashotos:"
Const cnWidth As Long = 200
Dim FName As String
Dim nTop As Long
Dim Sh As Shape

FName = Dir(MyPath, vbNormal)
With Worksheets("Photos").Pictures
Do While Len(FName) > 0
If UCase(FName) Like "*.JPG" Then
With .Insert(MyPath & FName)
.Top = nTop
.Left = 0
.Height = .Height * cnWidth / .Width
.Width = cnWidth
nTop = .BottomRightCell.Offset(1).Top
End With
End If
FName = Dir
Loop
End With
End Sub

 

[RichEditor]

Sorry to hi-jack your thread but if you can assist me it would be a HUGE HUGE
help.

I regularly have to insert 100+ images into blank excel sheets and the way
I've been shown is to just drag and drop them one at a time. Does the macro
you've written do just this? What I need is for the first jpeg to appear in
cell 1A, the second jpeg to appear in cell 1B and so on. I've tried altering
your macro but I know nothing about this and can't make it work. Which
sections would I need to alter to access my file containing the jpegs?

If there's any chance you can give a quick explanation that would be great.

Many thanks in advance,

Rich.

 

[RichEditor]

Sorry, that should have read:

What I need is for the first jpeg to appear in
cell 1A, the second jpeg to appear in cell 2A and so on.