Moving pictures from an excel file into an access table

[gusz1]

Hi,
is there a way with VBA to achieve the following:
I have a picture in an excel file, and I want to insert it into an access
table. The records of the access table look like this:
col1, col2, ... col5
where col1, ... are text fields and col5 is an OLEObject field.
So is there a way to write a cross-application script that does this?

Thx,
Gus

 

[Jamie Collins]

(e-mail address removed) wrote ...

is there a way with VBA to achieve the following:
I have a picture in an excel file, and I want to insert it into an access
table. The records of the access table look like this:
col1, col2, ... col5
where col1, ... are text fields and col5 is an OLEObject field.

Probably best to use a recordset object on the Excel side e.g.

Sub TestEarlyBound()

Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim strSql As String

Const DB_PATH = "C:\Tempo\New_Jet_DB.mdb"

Set oConn = New ADODB.Connection

With oConn
.CursorLocation = adUseClient
.ConnectionString = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DB_PATH
.Open
End With

strSql = "SELECT MyOleObjectCol" & _
" FROM Blobs"

Set oRs = New ADODB.Recordset
With oRs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.ActiveConnection = oConn
.Source = strSql

.Open

.AddNew
.Fields(0).Value = Sheet1.Image1.Picture
.UpdateBatch

.Close

End With

oConn.Close

End Sub

Jamie.

--