XL2BB - Excel Range to BBCode 2.1

[Polanskiman]

Found the issue. I had a merged empty data validation cell which if selected generates the run-time error. Happens on both Windows and MAC v16.
Interestingly doesn't happen if the cells are no merged.

The other issue is the following. Only 2 rows are showing when in fact 4 rows have been selected

Formula support.xlsx
	B	C	D	E	F	G	H	I	J	K
33		Tank			WC			RO
34		Tank name	Volume	Real Depth
TkDD

 

[smozgur]

@Polanskiman - thanks for reporting the issue.

I'd just like to say that XL2BB is not an Excel Worksheet / Range creator but a converter that transforms the selected range to a custom BBCode to be used in MrExcel Message Board. It doesn't and is not supposed to cover or fulfill all kind of variation of rules / formats / structure that an Excel worksheet might contain. However, we are all trying to fix / implement / suggest anything possible to make it work better. So I will try to find out if this can be fixed.

In the meantime, I am sending you a private message for asking a sample worksheet that is causing the problem(s) you reported. This way I can see the actual problem that you are having with the tool. That would be great if you could share it with me (with no sensitive data of course).

Thank you again.

 

[smozgur]

@Polanskiman - thanks again for reporting these two issues and providing the sample workbook. I wanted to let you know my findings and possible actions.

1- Missing data in the generated XL2BB code.
The new line used in the cell values causing this problem.
This is a bug and it will be fixed in the next update.

2- Data validation in merged cells.
This is actually kind of an Excel inconsistency.
When we have a cell with data validation and merge it with adjacent cell later, merged range doesn't have the same data validation unless two cells are forced to have the same validation. Therefore VBA has nothing to do but fail if it is asked to return any information about the data validation on that range.

To see how Excel tries to handle this issue, please try this: Select G6 (merged with H6 that has the Data Validation) and click on Data Validation on the ribbon. See how Excel tries to solve it with user interaction before executing the actual requested command.
Side note: Confirming the following will end with copying the same data validation to G6. And if cells are un-merged later then there will be two adjacent cells with the same data validation.

XL2BB can't ask something like this as it is not supposed to alter anything in the workbook. Therefore, I will force XL2BB to stop execution and tell user about the merged cells which don't have the same validation. It will be surely better interaction than 1004 error.
Necessary change will be applied in the next update.

Some suggestions for the user side to make it work before the update (separate solutions that will avoid this problem):
1- Merge first, then create the data validation.
2- If merge should be done later, then select the merged range and click on Data Validation to have Excel to extend the Data Validation on the cells in the merged area. (above example).
3- Don't use cell merge in Excel (at least for nothing other than labeling) - Of course funny suggestion but personally I wish Excel never provided such thing. It is against whole tabular data logic.

Thanks again!

 

[Polanskiman]

Thanks Suat. Great support.

 

[yky]

I copied a table from post #7 of this thread. The start time and end time originally are 8:00 and 18:00. It became 9:00 and 17:00. Also, the last column, correct result, is filled with huge numbers. I guess this is due to the European number format.

Another problem is the date format. If I paste the dates (original in UK format) to my system (US format), dates are pasted as text. If I change my system to UK format, then, dates are pasted as true dates (numbers).

Book1
	A	B	C	D	E	F	G	H
1		SUPPORT START TIME	SUPORT END TIME	Assign Date	Owned Date		formula result	correct result
2		09:00	17:00	15/01/2020 13:44	15/01/2020 14:00		00:16:00	1.11111E+12
3		09:00	17:00	11/01/2020 18:54	13/01/2020 09:16		00:16:00	5.27778E+12
4		09:00	17:00	10/01/2020 17:03	10/01/2020 17:05		00:02:00	1.38889E+11
Sheet1

 

[smozgur]

Thanks for reporting these issues, @yky.

The start time and end time originally are 8:00 and 18:00. It became 9:00 and 17:00.

That's because A column cells which are referred in B cell formulas should have LUFTHANSA but we don't see that data in the mini-sheet range.

the last column, correct result, is filled with huge numbers. I guess this is due to the European number format.

I will need to ask you the actual worksheet sample with the same data (without any sensitive information), so I can reproduce the same behavior in the development environment to see the actual problem and try to fix any possible bug. I'll be sending you my email address as private message. That would be great if you could provide me a sample workbook.

Another problem is the date format. If I paste the dates (original in UK format) to my system (US format), dates are pasted as text. If I change my system to UK format, then, dates are pasted as true dates (numbers).

I am surprised with this one as I believe it was already handled at one of the very early patches of XL2BB. I will check this out in different regional settings to make sure. Your sample workbook will be also great help for this. Because if data is saved as "text" then XL2BB has nothing to do as it cannot format copied value from forum to worksheet (reverse XL2BB => BB2XL as we call it). Please see the sample data below - cell values are date values but formatted in d/m/yy format. When you move your mouse pointer over the cells, you will see the values as in m/d/yy format. This way XL2BB makes sure it is properly exchanged as values to provide portability.

Book1
	B
1	14/1/20
2	15/1/20
3	16/1/20
4	17/1/20
Sheet1

However, when I move the mouse over the cells in your sample or in post #7, I don't see any tooltip that tells me those are not actually entered as date values but as text. Perhaps imported data.

I've been already preparing to release a new patch version by the end of July for the issues that @Polanskiman reported earlier. It is great you posted these ones so we can have more bugs solved in a single release.

Thank you, again.

 

[yky]

I am surprised with this one as I believe it was already handled at one of the very early patches of XL2BB. I will check this out in different regional settings to make sure. Your sample workbook will be also great help for this. Because if data is saved as "text" then XL2BB has nothing to do as it cannot format copied value from forum to worksheet (reverse XL2BB => BB2XL as we call it). Please see the sample data below - cell values are date values but formatted in d/m/yy format. When you move your mouse pointer over the cells, you will see the values as in m/d/yy format. This way XL2BB makes sure it is properly exchanged as values to provide portability.

Book1
	B
1	14/1/20
2	15/1/20
3	16/1/20
4	17/1/20
Sheet1

However, when I move the mouse over the cells in your sample or in post #7, I don't see any tooltip that tells me those are not actually entered as date values but as text. Perhaps imported data.

I have mailed you the workbook.

You are right. Most of the date/time are text. I copied the table in #7 and pasted it to Excel. I then copied the pasted data and pasted it again by value. Most of the date/time are TEXT. However, D3, D4, and E4 are pasted as number, implying they are true dates. Yet, when I hovered the mouse over them in #7, no tooltip showed up.

 

[smozgur]

However, D3, D4, and E4 are pasted as number, implying they are true dates.

That's right. Because those cells have valid date values no matter the format is d/m/y or m/d/y, so Excel treats them as date values.

Once the range was rendered as a mini-sheet in the forum page, all it has what it was given to it in Excel. If a numeric value has a different look than its formatted version in a cell then it is saved in the tags during the XL2BB process, so it provides portability copying the values back form the mini-sheet with a JavaScript process during the BB2XL.

exclude non-business hours and weekends
This mini-sheet was clearly created that column D and E cells values are text, certainly not date values. That’s why it doesn’t work when it was copied back to Excel, because there is no actual values saved in the background for the formatted values rendered in the mini-sheet cell (as in my sample post in my reply).

Therefore, everything is working how it is supposed to be at this point.

However, I believe that you found a bug about column H values. I will work on it.

Thanks again for sending the workbook and your help, @yky!

Suat

 

[HeyItsDizzy]

Where is the download Link?

 

[MARK858]

Click the XL2BB icon in the reply window to take you to the "XL2BB - Excel Range to BBCode 1.2.7" page, there is an Orange box top right that is a download link or there is a link in the Installation section.