to physical image path in excel file dynamically, have used below code.
=left(cell("filename",a1),find("*",substitute(cell("filename",a1),"\","*",len(cell("filename",a1))-len(substitute(cell("filename",a1),"\",""))),1))&"d04523.jpg" but excel file xml based urn:schemas-microsoft-com:office:spreadsheet format, have encode every " double quotes below.
=left(cell("filename",a1),find("*",substitute(cell("filename",a1),"\","*",len(cell("filename",a1))-len(substitute(cell("filename",a1),"\",""))),1))&"d04523.jpg" everything correct in xml file, problem excel file cannot decode ".
below xml file can opened in excel application.
<?xml version="1.0" encoding="utf-8"?> <?mso-application progid="excel.sheet"?> <ss:workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> <ss:worksheet ss:name="sheet1"> <ss:table> <ss:row> <ss:cell> <ss:data ss:type="string">1</ss:data> </ss:cell> <ss:cell> <ss:data ss:type="string">2</ss:data> </ss:cell> </ss:row> <ss:row> <!-- error happen : unexpected error has occured. --> <ss:cell ss:href="=left(cell("filename",a1),find("*",substitute(cell("filename",a1),"\","*",len(cell("filename",a1))-len(substitute(cell("filename",a1),"\",""))),1))&"d04523.jpg""> <ss:data ss:type="string">view image 1</ss:data> </ss:cell> <!-- can view image when user click. don't want use hard coded path.--> <ss:cell ss:href="d:\05.project\excelresearch\d04523.jpg"> <ss:data ss:type="string">view image 2</ss:data> </ss:cell> </ss:row> </ss:table> </ss:worksheet> </ss:workbook> so question
how can make excel understand encoded formula?
or
how can modify xml file escape double quote "?
tried using slash not work.
shouldn't using ss:formula , not ss:href ?
here's got after saving workbook formula posted:
<cell ss:index="2" ss:formula="=left(cell("filename",rc[-1]),find("*",substitute(cell("filename",rc[-1]),"\","*",len(cell("filename",rc[-1]))-len(substitute(cell("filename",rc[-1]),"\",""))),1))&"d04523.jpg""> <data ss:type="error">#value!</data> </cell> 
Comments
Post a Comment