ChemDraw for Excel - Automation of Structure file generation
I want to share the options I figured out for converting of chemical data (esp. SMILES to cdxml/mol files) in Excel using Chemdraw 22 which can be used for automation in vba.
The requirement for it to work is that 'ChemDrawExcel22' and 'CS ChemDraw Control 22.0 Library' library are referenced. The style of the file generated can be changed by selection of a corresponding ChemDraw Style Sheet [ChemDraw22 Ribbon-Tab -> Options -> Preferences -> Default Style Sheet]
This checks if the range is within a ChemDrawSheet ( … and if not converts the sheet into a Chemdraw Sheet) and then displays the Structure for the SMILES Code in that range …
Sub convert_smiles_to_structure_and_show(rng As Range)
Dim wksName As String
wksName = rng.Parent.Name
If Not ChemDrawExcel22.IsCSWorksheet(Worksheets(wksName)) Then _
ChemDrawExcel22.CSXL_ConvertCSWorksheet Worksheets(wksName), True
ChemDrawExcel22.CSXL_ConvertSMILES rng
End Sub
This saves the structure in given range in a given file (+format). Could be probably easily extended to other formats …
Sub save_structure_to_file(ByVal rng As Range, _
ByVal fileName As String, _
Optional ByVal fileType As String = "text/xml", _
Optional ByVal location As String = "")
If Not ChemDrawExcel22.CSXL_IsCSXLStructureCell(rng) Then End
Dim fileExt As String
Select Case fileType
Case "text/xml":fileExt = ".cdxml"
Case "chemical/mdl-molfile": fileExt = ".mol"
End Select
ChemDrawExcel22.CSXL_SaveMolecule_File location & fileName & fileExt, rng, True
End Sub
With the two routines above you have the option to correct to modify your structure before saving it. However, I’ve found that once you’ve converted any of your sheets into a ChemDrawSheet Excel becomes pretty unstable, therefore there’s the next one, where no ChemDrawSheet is required. It converts SMILES into cdxml or mol files without showing the structures.
Sub write_smiles_to_file(ByVal SMILES As String, _
ByVal fileName As String, _
Optional ByVal fileType As String = "text/xml", _
Optional ByVal location As String = "")
Dim cdCtl As ChemDrawCtl
Dim fso As Scripting.FileSystemObject
Dim file As Object
Dim fileExt As String
Select Case fileType
Case "text/xml":fileExt = ".cdxml"
Case "chemical/mdl-molfile": fileExt = ".mol"
End Select
If location = "" Then location = ThisWorkbook.Path
Set cdCtl = New ChemDrawCtl
Set fso = New Scripting.FileSystemObject
Set file = fso.CreateTextFile(location & "\" & fileName & fileExt)
cdCtl.Data("chemical/x-smiles") = SMILES
file.Write cdCtl.Data(fileType)
file.Close
Set cdCtl = Nothing
Set fso = Nothing
Set file = Nothing
End Sub
The last one converts a Structure in a range (within an ChemDrawSheet …) back to SMILES. To achieve this I needed to write the Structure as a file (using save_structure_to_file from above).
It cannot be used as a Cell Function in Excel, since it’s giving ‘Circular Reference Error’ – I have no clue why … but works for me if called from within vba.
Function convert_structure_to_smiles(ByVal rng As Range) As String
If Not ChemDrawExcel22.CSXL_IsCSXLStructureCell(rng) Then End
Dim cdCtl As ChemDrawCtl
Dim fso As Scripting.FileSystemObject
Dim file As Object
Dim cdxml_cont As String
save_structure_to_file rng, "temp"
Set fso = New Scripting.FileSystemObject
Set file = fso.OpenTextFile("temp.cdxml", ForReading)
Set cdCtl = New ChemDrawCtl
cdCtl.Data("text/xml") = file.ReadAll
file.Close
fso.DeleteFile "temp.cdxml"
convert_structure_to_smiles = cdCtl.Data("chemical/SMILES")
Set fso = Nothing
Set file = Nothing
Set cdCtl = Nothing
End Function
-
Official comment
Thanks!
-
It seems like you're discussing routines or processes related to chemical structure conversion using Microsoft Excel and ChemDraw. Based on your description, it appears you have two routines:
1. A routine that involves converting chemical structures into ChemDrawSheet format in Excel and provides the option to correct or modify the structure before saving it.
2. Another routine that converts SMILES (Simplified Molecular Input Line Entry System) into cdxml or mol files without displaying the structures.
You've mentioned an issue with Excel becoming unstable after converting sheets into ChemDrawSheet format. If you're experiencing instability issues, it's essential to ensure that you're using compatible versions of Excel and ChemDraw, and that your computer meets the system requirements for both applications.
For the routine that converts SMILES into cdxml or mol files without displaying the structures, you may want to provide more details or ask specific questions related to this process so I can offer more targeted assistance.
-1 -
It seems like you're discussing routines or processes related to chemical structure conversion using Microsoft Excel and ChemDraw. Basketball Stars Based on your description, it appears you have two routines:
1. A routine that involves converting chemical structures into ChemDrawSheet format in Excel and provides the option to correct or modify the structure before saving it.
2. Another routine that converts SMILES (Simplified Molecular Input Line Entry System) into cdxml or mol files without displaying the structures.
You've mentioned an issue with Excel becoming unstable after converting sheets into ChemDrawSheet format. If you're experiencing instability issues, it's essential to ensure that you're using compatible versions of Excel and ChemDraw, and that your computer meets the system requirements for both applications.
For the routine that converts SMILES into cdxml or mol files without displaying the structures, you may want to provide more details or ask specific questions related to this process so I can offer more targeted assistance.
It's helpful! Thank for your reply.
0
Please sign in to leave a comment.
Comments
3 comments