[Solved] Getting Formatted Cell value

[Dave The Hat]

Hi. I am trying to write a macro to convert values in a range of cells into a format suitable for pasting into a forum. I am having problems getting the formatted value of the cell. For example the cell value is 0.1293847273723, but in the spreadsheet is it displayed as 12.94%. In the macro I want it to use the 12.94% formatted value. How do I get the formatted value. Here is the code as it stands:

Code: Select all

Function HTMLTableRow (rSource01 as range, Optional rSource02 as range)

	Dim i as Integer
	Dim sReturn as string
	
	sReturn = "[tr]"
	
	for i = 1 to uBound(rSource01, 2)
		sReturn = sReturn + HTMLTableCell(rSource01(1, i))
	next i
	if not IsMissing(rSource02) then
		for i = 1 to uBound(rSource02, 2)
			sReturn = sReturn + HTMLTableCell(rSource02(1, i))
		next i
	end if
	
	sReturn = sReturn + "[/tr]"

	HTMLTableRow = sReturn

End Function

Function HTMLTableCell (sValue, Optional bBold as Boolean, Optional bItalic as Boolean)

	if IsMissing(bBold) then
		bBold = false
	end if

	if IsMissing(bItalic) then
		bItalic = false
	end if

	HTMLTableCell = "[td]"
	if bBold then
		HTMLTableCell = HTMLTableCell + "[b]"
	end if
	if bItalic then
		HTMLTableCell = HTMLTableCell + "[i]"
	end if
	HTMLTableCell = HTMLTableCell + sValue
	if bItalic then
		HTMLTableCell = HTMLTableCell + "[/i]"
	end if
	if bBold then
		HTMLTableCell = HTMLTableCell + "[/b]"
	end if
	HTMLTableCell = HTMLTableCell + "[/td]"

End Function

I am guessing I have to change the "HTMLTableCell(rSource01(1, i))" part of the code. It would also be nice to know how to tell if the cell is bold or italic.

I apologise if this has been previously answered, but my google searching has failed to find the answer.

[Zizi64]

It would also be nice to know how to tell if the cell is bold or italic.

It is better to use the Cell Styles instead of the direct formatting method.You can get/set the applied style of a cell by a very simple method in your macro code:

viewtopic.php?t=43351
viewtopic.php?t=48960

[Zizi64]

Otherwise, the "formatted cell value" is a string, but not a number.
Every cell can store 3 different contents in SAME TIME:
- a Numeric value: Double precision floating number (8 byte wide)
- a String (converted from the double precision floating type numeric value to textual format: "1.23456" - yes, it is a text with seven characters - ; or a pure textual content, like the: "Hello World!")
- a Formula (and/or the FormulaLocal for the localised displaying)

You can get the textual content of a cell by the command

Code: Select all

Dim sCelltext as string
Dim oCell as object

...
...

sCellText = oCell.String

It gives you the string content - without the visual formatting (bold/italic/color... etc) properties.

[Villeroy]

[Calc, Basic] Introspective cell functions
Return style name of this row's cell in column #1: =CELL_STYLE(SHEET();ROW();1)
Apply a cell style to a numeric value with return value 0: =A1 +STYLE("MyStyle")
Apply a cell style to a text value eliminating the return value 0: =A1 &T(STYLE("MyStyle"))

Combined: =IF(ISNUMBER(A1) ; A1 +STYLE(CELL_STYLE(SHEET();ROW();1)) ; A1 &T(CELL_STYLE(SHEET();ROW();1))

[Lupp]

Did you consider to use what I offered in viewtopic.php?f=21&t=85152/ (enhanced version viewtopic.php?f=21&t=85152/#p432302/ with control parameter "bisulp")?
It should provide what you want. Concerning textual content you also get attributed differently formatted TextPortions.
QA simple help is included with the code in REM lines (making th code longer, of course).

The inconvenience concerning the way the cell to work on is passed to the function: Using LibreOffice this could be done much simpler placing the function into a module with Option VBAsupport 1 enabled. A single SheetCell or SheetCellRange on a parameter position would then not only pass data, but also information about the source range itself.

By the way: In most cases it will be simpler to first make a new sheet containing the parts for export using Ctrl+C and Ctrl+Shift+P and then to export it in html format by board means of OpenOffice. (This is also easily feasible with a script - probably just needing a recorded macro.)

[Dave The Hat]

That's it Lupp. Thank you