Saving GIF images from worksheet using VBA
 

Hi - can someone help me please?

For each row in my worksheet I need to be able to retrieve (from a URL) a
GIF image and save it to a folder on my hard-drive.
The URL strings includes some data from the corresponding row.

Any ideas or pointers please?

Searching this ng I found references to using chart.export and even
Stratos's low-level binary solution (from machine memory rather than a URL)
but are these really the best and only options?

Thanks

--
Sorby

Saving GIF images from worksheet using VBA
 

Believe Export and possibly Strato's solution are for saving a picture
originally located in an excel spreadsheet (or a picture of a range or
something like that). If you have a url in your worksheet that refers to a
gif file, then you probably want to use ftp to retrieve it and save it to a
local directory rather than open it in excel. A lot would depend on whether
you can access the file with ftp or not.

--
Regards,
Tom Ogilvy



"Sorby" wrote in message
...
Hi - can someone help me please?

For each row in my worksheet I need to be able to retrieve (from a URL) a
GIF image and save it to a folder on my hard-drive.
The URL strings includes some data from the corresponding row.

Any ideas or pointers please?

Searching this ng I found references to using chart.export and even
Stratos's low-level binary solution (from machine memory rather than a
URL)
but are these really the best and only options?

Thanks

--
Sorby

Saving GIF images from worksheet using VBA
 

Thanks for the prompt response Tom,

Sadly I don't have ftp access to the website in question.
Could I force the image to be opened in an IE window and instruct IE to save
the file (i.e. via 'Save picture as...") programatically?

Thanks again

--
Sorby


"Tom Ogilvy" wrote in message
...
Believe Export and possibly Strato's solution are for saving a picture
originally located in an excel spreadsheet (or a picture of a range or
something like that). If you have a url in your worksheet that refers to
a
gif file, then you probably want to use ftp to retrieve it and save it to
a
local directory rather than open it in excel. A lot would depend on
whether
you can access the file with ftp or not.

--
Regards,
Tom Ogilvy



"Sorby" wrote in message
...
Hi - can someone help me please?

For each row in my worksheet I need to be able to retrieve (from a URL)
a
GIF image and save it to a folder on my hard-drive.
The URL strings includes some data from the corresponding row.

Any ideas or pointers please?

Searching this ng I found references to using chart.export and even
Stratos's low-level binary solution (from machine memory rather than a
URL)
but are these really the best and only options?

Thanks

--
Sorby

Saving GIF images from worksheet using VBA
 

See my comment at the bottom.

'=======================================
Option Explicit
Private Declare Function InternetOpen Lib "wininet.dll" Alias "InternetOpenA" _
(ByVal sAgent As String, _
ByVal lAccessType As Long, _
ByVal sProxyName As String, _
ByVal sProxyBypass As String, _
ByVal lFlags As Long) As Long
Private Declare Function InternetOpenUrl Lib "wininet.dll" Alias "InternetOpenUrlA" _
(ByVal hOpen As Long, _
ByVal sUrl As String, _
ByVal sHeaders As String, _
ByVal lLength As Long, _
ByVal lFlags As Long, _
ByVal lContext As Long) As Long
Private Declare Function InternetReadFile Lib "wininet.dll" _
(ByVal hFile As Long, _
ByVal sBuffer As String, _
ByVal lNumBytesToRead As Long, _
lNumberOfBytesRead As Long) As Integer
Private Declare Function InternetCloseHandle Lib "wininet.dll" _
(ByVal hInet As Long) As Integer


Public Function fncGetInternetFile(URLocation As String, _
Optional OutputFileName As String = vbNullString) As String
'retrieves a remote file (http, ftp, etc.) and returns its contents in a string;
'if an OutputFileName has been specified, it also saves the file in the specified local location
'in case of an error it returns:
' "Error 0": an unexpected error occured
' "Error 1": internet connection could not be established
' "Error 2": the URL file could not be found/accessed
' "Error 3": the URL file was opened but contains no data
' "Error 4": the specified directory of filename for
' saving the remote data is invalid
'
'variable declarations
Dim OpenInternetConnection As Long
Dim OpenURLocation As Long
Dim ContinueDataCollection As Boolean
Dim DataChunkRetrieved As Boolean
Dim NumberOfBytestoRead As String * 2048
Dim NumberOfBytesRead As Long
Dim File_hWnd As Long
'
'required constants for the wininet.dll
Const INTERNET_OPEN_TYPE_PRECONFIG As Long = 0
Const INTERNET_OPEN_TYPE_DIRECT As Long = 1
Const INTERNET_OPEN_TYPE_PROXY As Long = 3
Const INTERNET_FLAG_RELOAD As Long = &H80000000
'
'initiate the result of the function to "Error 0"; assume unexpected failure

fncGetInternetFile = vbNullString
'
'establish Internet connection
OpenInternetConnection = InternetOpen(sAgent:="VB OpenUrl", _
lAccessType:=INTERNET_OPEN_TYPE_PRECONFIG, _
sProxyName:=vbNullString, _
sProxyBypass:=vbNullString, _
lFlags:=0)
'if Internet connection could not be established exit the function; return "Error 1"

If OpenInternetConnection = 0 Then: fncGetInternetFile = "Error 1": GoTo ExitFunction
'
'open the specified Internet URLocation
OpenURLocation = InternetOpenUrl(hOpen:=OpenInternetConnection, _
sUrl:=URLocation, _
sHeaders:=vbNullString, _
lLength:=0, _
lFlags:=INTERNET_FLAG_RELOAD, _
lContext:=0)
'if the specified Internet URL could not be opened exit the function; return "Error 2"
If OpenURLocation = 0 Then: fncGetInternetFile = "Error 2": GoTo

Saving GIF images from worksheet using VBA
 

Thank you *very* much Tom! It worked a treat.

--
Sorby

"Tom Ogilvy" wrote in message
...
See my comment at the bottom.

'=======================================
Option Explicit
Private Declare Function InternetOpen Lib "wininet.dll" Alias
"InternetOpenA" _

<snipped