Read Range question

Help Activities
, ,
1

Hi,
I am reading from an Excel into a DataTable. Is there a way to preserve format such as Bold letter lines, Strike through lines etc.? DataTable using WriteRange shows all normal text. I did select “Preserve Format” Property in Read Range.

Thanks,

1 Like
2

dont think you can. preserve format only preserves the format of the cell value, not the cell format.

if your format is fixed you can create a excel template and format it first then use write range to write to that excel , if its dynamic you need to use another way e.g. VBA

3

Thank you @jack.chan
Is it possible to provide VBA Samples to preserve bold letter format, indentation, strike through sentences etc.?

Thanks again,

4

can i see the excel and which cells do you need to preserve bold format/strike through etc…

5

image
As you can see in the image of Excel, there are some dark text lines which are parent tasks, there is indentation for child tasks. And some lines have strike through indicating inactive. How do I carry out this formatting to the datatables and to other Excels when I do ReadRange and WriteRange. Thanks a lot,

6

@A_Learner

If possible update the records in this file itself instead of writing into separate file.

7

Hey @A_Learner

What’s your actual requirement here please ?

I mean what is the need to preserve the format ?

May be will be easy to think for some workarounds.

Thanks
#nK

9

Im still not sure about the requirement but here are some samples

Function StrikeThrough(sheetName as string, cellAddress as string)
    ActiveWorkbook.Sheets(sheetName).Activate
    Range(cellAddress).Select
    With Selection.Font
        .Strikethrough = True
    End With
End Sub

Function Bold(sheetName as string, cellAddress as string)
    ActiveWorkbook.Sheets(sheetName).Activate
    Range(cellAddress).Select
    Selection.Font.Bold = True
End Sub

Function Indent(sheetName as string, cellAddress as string)
    ActiveWorkbook.Sheets(sheetName).Activate
    Range(cellAddress).Select
    Selection.InsertIndent 1
End Sub

you can use it like this

  1. create text file can vbaFunctions.txt and add the above code
    vbaFunctions.txt (602 Bytes)
  2. invoke vba from vbaFunctions.txt like the sequence below
    test.xaml (6.9 KB)
    image
    image487×633 14.5 KB

Example
testf.xlsx (9.7 KB)

Before
image

after
image
e