Forum Discussion

sunnyschindler's avatar
sunnyschindler
Brass Contributor
Jul 25, 2023

LOOKUP to include formatting with its Return Value? Formatting Code Function? non-VBA request

Without VBA, is it possible for LOOKUP to return formatting of its Return Value? It is much easier if Formatting Code Function (similar to CODE function) can be exposed to end user.   I need to di...
excel
Formulas and Functions
office 365
bosinander's avatar
bosinander
Iron Contributor
Jul 29, 2023

> Is it because Unicode

Well, yes - they have to be HTML coded,

I continued on your path using keywords in the input for the unichars and appended a lookup table with the HTML codes.

A lambda function in col G loops through the rows in [Name in text] and replaces the keywords with [HTML]. 

Thus, column N shows also unicharacters with formats available in html.

 

BTW: FILTERXML is already available in Excel. Maybe FILTERHTML could be in a future version.

 

-/-

I ran some tests with converting the strings using calls to other program libraries like Internet Explorer but found no big upside in doing so.

Attached an enhanced version ending with "_XL.xlsm" (like not using IE) where you also can have your calculations and the output on separate sheets.

Using html to format calculated results may in deed come handy.

    • sunnyschindler's avatar
      sunnyschindler
      Brass Contributor
      Aug 03, 2023

      bosinander Thanks for more help. But my VBA literacy is insufficient and needs to change line from 

          ' Path = ThisWorkbook.Path & " \ " & tmpFile      'get path to a temp file in same folder
    Path = TargetFolder & " \ " & tmpFile      'get path to a temp file in same folder

      The Target Folder works for code as follows

      'https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/freefile-function
Sub FreeFile_Example1()

 Dim Path As String
 Dim FileNumber As Integer

Dim TargetFolder As String
TargetFolder = "C:\Users\wongsunn\OneDrive - Schindler\Documents\scrap"

 Path = TargetFolder & "\Articles2019File 1.txt"
 FileNumber = FreeFile

 Open Path For Output As FileNumber

 Path = TargetFolder & "\Articles2019File 2.txt"
 FileNumber = FreeFile

 Open Path For Output As FileNumber

End Sub

      Now I have to resolve error "Open Path For Output As #fileNo". 

       

      • bosinander's avatar
        bosinander
        Iron Contributor
        Aug 03, 2023

        sunnyschindler Use a hard coded target folder of your choice instead of ThisWorkbook.Path

        '    path = ThisWorkbook.path & "\" & tmpFile        'get path to a temp file in same folder
    path = "C:\Users\wongsunn\OneDrive - Schindler\Documents\scrap" & "\" & tmpFile        'get path to a temp file

         

Resources