WorkbookXLSX R30 2

[gbcpastor]

I'm currently learning how to use a class called WorkbookXLSX and I know some of you are quite familiar with it. The problem I'm having is:

I'm trying to post a field to a cell and it has 15 leading spaces but every time I post it, it trims both leading and trailing spaces. This data must be posted exactly as original. Please help with any ideas you may have.

Thanks in advance!

 

[ggreen61]

Hello--

I wrote the class. If you are writing the value as a character, then the method is doing an ALLTRIM() on the value before saving. First you should download the current release which is R44. Then open the class and edit the method SetCellValue(). Go to line number 40 which is

txCellValue = ALLTRIM(txCellValue)

Comment out this line. Then the value should be saved with the spaces.

 

[gbcpastor]

Hey Greg, thanks for getting back to me. I tried your solution. I downloaded R44 and commented out line 40. When I write the cell, it still is being trimmed front and back.



The AOJ should begin at position 15.

Any other ideas?

 

[GriffMG]

Leading single quote?

 

[gbcpastor]

Unfortunately, it has to be an exact representation of the clients data. If I can figure out where the data is actually being written to the worksheet, perhaps I can figure it out.

 

[GriffMG]

If you find a way to do that, I would be interested, I use single quotes when data must be padded to the left

 

[gbcpastor]

If I find it, I'll publish for sure. This is a great Class Greg wrote and I love the way it works. Just need this to work now.

 

[gbcpastor]

Greg, can you tell me where it actually writes the worksheet? I may be able to find the fix there.

 

[ggreen61]

Look at the method WriteSheetXMLs, starting at line 163. This starts a loop to write all the cell values by row and column order. I found the problem at lines 220 AND 270. Here I do an ALLTRIM() again. This would eliminate the leading/trailing spaces from the value being written into the sheet. You could probably try a RTRIM() instead of an ALLTRIM().

 

[gbcpastor]

Thank you Greg. I'll let you know if it works.

 

[gbcpastor]

Hey Greg. Still no joy. The class works perfectly for everything else. But, it still is trimming leading and trailing spaces.

If you are able to make it leave leading spaces, please send an example. I really want this to work. I love the utility. I can send sample data if it helps. Just let me know.

 

[Chris Miller]

I think you still didn't really take into account Griffs advice to preceed the spaces you want untouched with a single quote. A single quote at the start of a cell value is
1. not displayed (so no visual annoyance)
2.. lets Excel interpret the rest of the cell value as character (string), which it leaves untouched.

So it should solve your problem. If you have sheets that look like they start with spaces, that are not trimmed away, then activate a cell and look at the actual cell value, I bet you will see a single quote at the start, it's the only way I know this works, otherwise it's Excel itself and the cell objects that automatically trim, unless something is right aligned. But aith right aligned values you can't determine the indentation to the left, also right aligned gives no control over the left indentation, will also mean no space at the right side of a cell and is only automatic for numeric values.

 

[ggreen61]

Please send me the xlsx file that you generated. I will check the internal value being written. I suspect that Chriss is right and the culprit is now Excel triming the spaces. I also know that a single quote should fix the issue as well.

 

[gbcpastor]

Greg, I'm going to try Chris's suggestion first. It's not ideal, but I may have to deal with it.

Non-the-less, I'll send you the xlsx

Also, is there a way to silence the status windows as it does the build?

 

[gbcpastor]

Sorry to keep this going guys.

How can I suppress the single quote programmatically? It looks like that is going to be the only way to make this happen.

 

[gbcpastor]

I'm surprised [URL='

https://www.tek-tips.com/members/mike-lewis.388080/'

]Mike Lewis[/URL] hasn't chimed in yet. I'm sure he has used this class as well.

 

[gbcpastor]

If you find a way to do that, I would be interested, I use single quotes when data must be padded to the left

Griff, can you tell me how you got the single quote not to show up in the screen? Every time I try this, the string begins with the single quote followed by the blank spaces. But I can't get rid of the single quote.

 

[gbcpastor]

Please send me the xlsx file that you generated. I will check the internal value being written. I suspect that Chriss is right and the culprit is now Excel triming the spaces. I also know that a single quote should fix the issue as well.

Were you able to figure anything out? I would love to use this class as I've got so much in it at this point.

Please help

 

[gbcpastor]

I think you still didn't really take into account Griffs advice to preceed the spaces you want untouched with a single quote. A single quote at the start of a cell value is
1. not displayed (so no visual annoyance)
2.. lets Excel interpret the rest of the cell value as character (string), which it leaves untouched.

So it should solve your problem. If you have sheets that look like they start with spaces, that are not trimmed away, then activate a cell and look at the actual cell value, I bet you will see a single quote at the start, it's the only way I know this works, otherwise it's Excel itself and the cell objects that automatically trim, unless something is right aligned. But aith right aligned values you can't determine the indentation to the left, also right aligned gives no control over the left indentation, will also mean no space at the right side of a cell and is only automatic for numeric values.

If you find a way to do that, I would be interested, I use single quotes when data must be padded to the left

 

[gbcpastor]

If you find a way to do that, I would be interested, I use single quotes when data must be padded to the left

Griff, can you please tell me how you, programmatically, put that single quote in the cell so it doesn't show up? Please help.