Get image (picture) from Oracle database

[Guest]

Hi there,

I have an Excel file that uses an ADO connection string to connect to an
Oracle database and retrieves the Top 10 selling items for last week. I can
get the style and quantity ok. However, we also have a picture of the item
stored in the Oracle database, and I would like to imbed that picture onto
the excel worksheet, on the same line as the style and quantities.

The Oracle database has one field that defines the image type, and another
field that stores the image.

How does one achieve this ?

 

[Tim Williams]

Have some code for this - will post tomorrow when I'm back at work.

Tim

 

[Guest]

hi Gary
can u post the code to connect oracle database using ado?
what reference should i select to run the code?

 

[Tim Williams]

'********************************
Sub PlaceFile(lSequenceNumber As Long, rngPos as range)
Const stempfile = "C:\local files\temp.gif"
Dim rs As ADODB.Recordset

'oConn is a global connection object
Set rs = oConn.Execute("SELECT t.blob1 FROM docs t WHERE t.id = " & lSequenceNumber)

'Write it to disk
Dim oStream
Set oStream = CreateObject("adodb.stream")
With oStream
.Type = adTypeBinary
.Open
.Write (rs.Fields("dcmtblob2").Value)
.SaveToFile stempfile, adSaveCreateOverWrite
.Close
End With

rngPos.Select
shtData.OLEObjects.Add Filename:=stempfile, Link:=False, DisplayAsIcon:=False

rs.Close
Set rs = Nothing
Set oStream = Nothing

End Sub
'********************************

 

[Tim Williams]

Typo in
..Write (rs.Fields("dcmtblob2").Value)

should be
..Write (rs.Fields("blob1").Value)