write Excel macro to convert smiles to structure

Hi, I need to write a Excel macro to convert smiles to structure image, what is the function i should use, looks like it takes two steps, turn smiles into some sort of coding, then call JCSYSStructure("A0B60DBC9B9F8E70D181227881ACF752"). I am trying to mimic ISIS for Excel's behavior

Thanks

Dong

never mind, i found jchem for excel manual, let me do my home work first ....

thanks

Dong

Public Sub ConvertSheetWithImage()
'Dim nativeInterface As JChemExcelCOMInterfacesLib.IJChemExcelAPI
Dim objAddin As Office.COMAddIn
 
Dim wsStructureSheet As Excel.Worksheet
Dim wsFrom As Excel.Worksheet
Dim wbFrom As Excel.Workbook
Dim wsTo As Excel.Worksheet
Dim newWorkBook As Excel.Workbook
Dim bDrawingState As Boolean
Dim lProcess As Long
Dim lCurrent As Long
 
    ' error handling on
    'On Error GoTo ERROR_HANDLER
     On Error Resume Next
  
    ' Excel addin is the list of Excel specific automation addins
    ' COM Addins are the real Office plugins loaded in at startup
    Set objAddin = Application.COMAddIns("JChemExcel.JChemExcelAddin")
    Set nativeInterface = objAddin.Object
   
    ' switch off drawing safety
    bDrawingState = nativeInterface.EnableDrawing
    nativeInterface.EnableDrawing = False
    
    ' select all structures on the sheet and convert them
   '  ................ e.g. Call ActiveSheet.Cells.Select

    Call nativeInterface.ExecuteAction("ConvertToStructureImageAction")
    
    
    ' activate the drawing again
    nativeInterface.EnableDrawing = bDrawingState
   
End Sub

Hi Dong,

 

the code above is just a hint how to start your 'homework', the key point here is to call built-in actions on the current selection simulating as if it is done manually from the Ribbonbar.

 Call nativeInterface.ExecuteAction("ConvertToStructureImageAction")

 

The action list you will find in the JChemExcelOffice2007Schmema or calling the GetAvailableActions function from your code.

Documentation how to write macros you will find here:

Functions in the table you can call:

http://www.chemaxon.com/jchem4excel/developerguide/introduction.html />
How to access the COM interface:

http://www.chemaxon.com/jchem4excel/developerguide/welcome_topic.html

 

Viktor

following is the macro, it retrieves the smiles from oracle based on compound id user provides, then convert the smiles to structure image, however i noticed that if the original xls file has mutiple columns, the redndered structure would sit on top of the string such as attched image. i cannot disclose the actual structures. Is there a way to fix this. and make things even worse if i am trying to resize the dimention of the structure image i am getting a MS error, and Exel would crash. If i cannot fix the problem i would have to get the smiles only, and let user convert smiles to image. I am just trying to provide them with a customized solution. We are using Jchem for excel 5.11.x , and Excel 2007.

Public Sub MergeData()
Dim oraSession
Dim oraDatabase
Dim oraDynaSet
Dim nativeInterface As Object
Dim objAddin As Office.COMAddIn

Set objAddin = Application.COMAddIns("JChemExcel.JChemExcelAddin")
Set nativeInterface = objAddin.Object
Dim asStringArray() As String

    Set oraSession = CreateObject("OracleInProcServer.XOraSession")
    Set oraDatabase = oraSession.DBOpenDatabase("dared2", "alc_exel" & "/" & "alcexel123", 0)
    r = ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ReDim asStringArray(0 To r - 2)
    Sql = "select smiles,id,exel_id from molecules where bms_no='id'"
    id = Selection.Column
    Columns(id+ 1).Insert Shift:=xlToLeft
    For Each cell In Selection
        If cell.Row < r + 1 Then 'And cell.Row <> 1 Then assume all column is selected
                NewSql = Replace(Sql, "id", cell.Value)
                Set oraDynaSet = oraDatabase.DBCreateDynaset(NewSql, 0)
                If oraDynaSet.RecordCount > 0 Then
                    asStringArray(cell.Row - 2) = oraDynaSet.Fields(0).Value
                End If
        Else
            Exit For
        End If
    Next cell

    nativeInterface.AddStructuresToColumn asStringArray, "smiles", id + 1, "Structure"
End Sub

 

This is because you formatted the cells to text rather than to general.

 

If I were you I would download the http://www.chemaxon.com/download.php?d=/data/download/jchem4xl/JChem_for_Excel_API_Guide.zip this material containing XLS, XLSX and a macro modeule.

Macro module contains an AddStructures demo code please use that when getting back the data from Oracle.

 

Hi Viktor,

the file got dumped into Excel from another application, all columns might have been formatted as text during the transporting. i added following after i insert column to get the structure image, and the formula backgroupd is gone, thing works as it should be.

Thanks for your help

Dong.

.....

Columns( id + 1 ).NumberFormat = "General"

......

Great, thanks.

Viktor