I am out of ideasand am after some lateral thinking. I have a folder containing images that i would like to make available within a workbook. I dont know if i should simply use a link to the folder or if there is a better way. There are approx 30 images within the folder and the workbook may only require 5-6 of them.

Any ideas??

"Gav" wrote in message
...
I am out of ideasand am after some lateral thinking. I have a folder
containing images that i would like to make available within a workbook. I
dont know if i should simply use a link to the folder or if there is a
better way. There are approx 30 images within the folder and the workbook
may only require 5-6 of them.

Hi Gav,

To make an informed recommendation on this we would need more
information. Here are some examples of the additional information required
and why it matters in the decision making process:

1) What are the size of these images? - If they are relatively small, the
simplest route may be to simply place them all on a hidden sheet in the
workbook where they would be readily available.

2) How often do these images change? - If they change frequently, placing
them within the workbook is probably not a good idea because an updated set
of images would not propagate to existing workbooks. Sub-question - does it
matter if new images don't propagate to existing workbooks?

3) Will the image path be the same for every user of this workbook? - If
not, you will either need to write the code required for the user to located
the image folder on their system or put all the images in the workbook.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

thanks ro

In response, the images are around 100-200KB each, depending. The location would be the same for each user on their local hard drive (my documents). The images would rarely change...it would serve only as a database of images for the user. Any new images would be updated directly into the target folder

This ofcourse is only an idea i had and would be open to alternatives

Cheers!!!!

Hi Gav,

If that's the case then you should probably leave the images in the
folder and write code to allow the user to select the image they want. I did
forget to ask what exactly you wanted to do with the image, but regardless,
here's some code to get you started. The most complicated part is getting
the path to the My Documents folder on the user's computer. Allowing them to
select an image and stick it on a worksheet is very easy.

Private Const S_OK As Long = 0
Private Const SHGFP_TYPE_CURRENT As Long = 0
Private Const CSIDL_PERSONAL As Long = 5
Private Const MAX_PATH As Long = 256

Private Declare Function SHGetFolderPathA Lib "Shell32.dll" _
(ByVal hWndOwner As Long, _
ByVal nFolder As Long, _
ByVal hToken As Long, _
ByVal dwFlags As Long, _
ByVal szPath As String) As Long

Public Sub InsertImage()
Dim szPath As String
Dim vFullName As Variant
szPath = szGetMyDocsPath() & "\"
If Len(szPath) 0 Then
ChDrive szPath
ChDir szPath
vFullName = Application.GetOpenFilename( _
"Image Files (*.jpg),*.jpg", , "Select an Image")
If vFullName < False Then
Sheet1.Pictures.Insert CStr(vFullName)
End If
Else
MsgBox "My Documents folder not found."
End If
End Sub

Private Function szGetMyDocsPath() As String
Dim szPath As String
szPath = String$(MAX_PATH, vbNullChar)
If SHGetFolderPathA(0&, CSIDL_PERSONAL, 0&, SHGFP_TYPE_CURRENT, _
szPath) = S_OK Then
szGetMyDocsPath = Left$(szPath, InStr(szPath, vbNullChar))
End If
End Function

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Gav" wrote in message
...
thanks rob

In response, the images are around 100-200KB each, depending. The location
would be the same for each user on their local hard drive (my documents).
The images would rarely change...it would serve only as a database of images
for the user. Any new images would be updated directly into the target
folder.

This ofcourse is only an idea i had and would be open to alternatives.

Cheers!!!!

thanks again bob....before i go any further, i have a query......to ditermine where the image pastes, can i use a heading??

IE: the sheet has certain sub headings like 'machine' and 'roller'. Can i use these headings as the targets in some way. I would like the image to paste into the empty cell beneath the heading. For each heading, the user would select an image

Thanks!!!!!

----- Rob Bovey wrote: ----

Hi Gav

If that's the case then you should probably leave the images in th
folder and write code to allow the user to select the image they want. I di
forget to ask what exactly you wanted to do with the image, but regardless
here's some code to get you started. The most complicated part is gettin
the path to the My Documents folder on the user's computer. Allowing them t
select an image and stick it on a worksheet is very easy

Private Const S_OK As Long =
Private Const SHGFP_TYPE_CURRENT As Long =
Private Const CSIDL_PERSONAL As Long =
Private Const MAX_PATH As Long = 25

Private Declare Function SHGetFolderPathA Lib "Shell32.dll"
(ByVal hWndOwner As Long,
ByVal nFolder As Long,
ByVal hToken As Long,
ByVal dwFlags As Long,
ByVal szPath As String) As Lon

Public Sub InsertImage(
Dim szPath As Strin
Dim vFullName As Varian
szPath = szGetMyDocsPath() & "\
If Len(szPath) 0 The
ChDrive szPat
ChDir szPat
vFullName = Application.GetOpenFilename(
"Image Files (*.jpg),*.jpg", , "Select an Image"
If vFullName < False The
Sheet1.Pictures.Insert CStr(vFullName
End I
Els
MsgBox "My Documents folder not found.
End I
End Su

Private Function szGetMyDocsPath() As Strin
Dim szPath As Strin
szPath = String$(MAX_PATH, vbNullChar
If SHGetFolderPathA(0&, CSIDL_PERSONAL, 0&, SHGFP_TYPE_CURRENT,
szPath) = S_OK The
szGetMyDocsPath = Left$(szPath, InStr(szPath, vbNullChar)
End I
End Functio

--
Rob Bovey, MCSE, MCSD, Excel MV
Application Professional
http://www.appspro.com

* Please post all replies to this newsgroup
* I delete all unsolicited e-mail responses


"Gav" wrote in messag
..
thanks ro
In response, the images are around 100-200KB each, depending. The locatio
would be the same for each user on their local hard drive (my documents)
The images would rarely change...it would serve only as a database of image
for the user. Any new images would be updated directly into the targe
folder
This ofcourse is only an idea i had and would be open to alternatives
Cheers!!!

Hi Gav,

I've made a modification to the code (shown below) that causes the top
left corner of the specified picture to be positioned in the top left corner
of the cell directly below the current selection. So if the user selects the
header cell prior to running the macro it should do what you want.

Private Const S_OK As Long = 0
Private Const SHGFP_TYPE_CURRENT As Long = 0
Private Const CSIDL_PERSONAL As Long = 5
Private Const MAX_PATH As Long = 256

Private Declare Function SHGetFolderPathA Lib "Shell32.dll" _
(ByVal hWndOwner As Long, _
ByVal nFolder As Long, _
ByVal hToken As Long, _
ByVal dwFlags As Long, _
ByVal szPath As String) As Long

Public Sub InsertImageBelowRange()
Dim objImage As Picture
Dim rngCell As Range
Dim szPath As String
Dim vFullName As Variant
Set rngCell = Selection.Offset(1, 0)
szPath = szGetMyDocsPath() & "\"
If Len(szPath) 0 Then
ChDrive szPath
ChDir szPath
vFullName = Application.GetOpenFilename( _
"Image Files (*.jpg),*.jpg", , "Select an Image")
If vFullName < False Then
Set objImage = Sheet1.Pictures.Insert(CStr(vFullName))
objImage.Top = rngCell.Top
objImage.Left = rngCell.Left
End If
Else
MsgBox "My Documents folder not found."
End If
End Sub

Private Function szGetMyDocsPath() As String
Dim szPath As String
szPath = String$(MAX_PATH, vbNullChar)
If SHGetFolderPathA(0&, CSIDL_PERSONAL, 0&, SHGFP_TYPE_CURRENT, _
szPath) = S_OK Then
szGetMyDocsPath = Left$(szPath, InStr(szPath, vbNullChar))
End If
End Function

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *


"Gav" wrote in message
...
thanks again bob....before i go any further, i have a query......to
ditermine where the image pastes, can i use a heading???

IE: the sheet has certain sub headings like 'machine' and 'roller'. Can i
use these headings as the targets in some way. I would like the image to
paste into the empty cell beneath the heading. For each heading, the user
would select an image.

Thanks!!!!!!