Access Excel

olevb1,

You mention that you succeeded in copying the ole object field to the clipboard using VB/VBA.

If you can show how you did that, I can surely help you to finalize this, all the rest is known to me.

Following function will export any SQL statement supplied to it to an Excel spreadsheet.  Also, with this code no reference has to be made to Excel 8.0 for it to function.

Public Function Export_SQL_To_Spreadsheet(strSQL As String)

On Error GoTo ErrorHandler

Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
If rst.EOF Then
    MsgBox "No records to export to Excel.", vbOKOnly, "No Export"
    GoTo SubExit
End If

Dim objXL As Object, objWkb As Object, i As Integer, j As Integer

Set objXL = CreateObject("Excel.Application")
objXL.Application.workbooks.Add
Set objWkb = objXL.Application.ActiveWorkBook

With objWkb
    'Export column headers
    For i = 1 To rst.Fields.count
        .worksheets(1).cells(1, i) = rst.Fields(i - 1).Name
    Next i
   
    'Export data
    j = 1
    While Not rst.EOF
        j = j + 1
        For i = 1 To rst.Fields.count
            .worksheets(1).cells(j, i) = rst.Fields(i - 1)
        Next i
        rst.MoveNext
    Wend
End With

objWkb.Close
objXL.Application.Quit

SubExit:
On Error Resume Next
Set dbs = Nothing
Set rst = Nothing
Set objXL = Nothing
Set objWkb = Nothing
Exit Function

ErrorHandler:
MsgBox "Error " & Err.Number & vbNewLine & vbNewLine & Err.Description
Resume SubExit

End Function

Maathias,

Your code indeed bears some advantages, but the real issue here is the exporting of the OLE Data Fields to Excel, which is absolutely not handled by Excel cells in a normal way, that's why I am working with the at first sight complicated copy to and paste from Clipboard, and also using a form with the OLE Data field contents to be able to copy something.

Indeed, you're right.  My code would not handle OLE objects.

olevb1,

You mailed me this

"Well i could come out of the database type problem:
The pb is i m mot able to understand what does your
following statement says:
requires that you create a form ("EETest" in the
macro), which shows all the objects for each record

What is expected to do here.

And now i m facing this pb:
Set mRecSet = mTbl.OpenRecordset("Select * From
PicTable")  --> gives type mismatch error-- i m not
able 2 sort this out

Reply Soon,
Bye"

I would prefer if you ask updates in the frame of this question.

Anyhow,

the form you need to make is an Access form which shows the three fields for the export you are trying to do to Excel. I need the form to show these fields for each record, to be able to copy them to the clipboard, and paste them into Excel. The name you choose for the form and the name of both OLE controls is indicated in these two lines

Set mControl1 = Forms("TestEE").Controls("Obj")
Set mControl2 = Forms("TestEE").Controls("Pic")


The Type mismatch message is probably because you have both DAO en ADO references set in Tools/References for your project as this article explains:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q181542

You can overcome it by doing this in the declaration section

Dim mTbl As Database, mRecSet As DAO.Recordset

for olevb1

It's time to clean up this TA, so I will leave a recommendation in Community Support that this question is:
 - Answered by: calacuccia  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)