Solved: SaveAs Text File Changes Display Value: Stop This

[YellowLabPro]

So the saga continues,
I have finally have my last excel worksheet displaying the values as I wish before publishing to the web. My sheet needs to be saved out as a Text File, upon doing this, Excel once again converts my display values back to text.
Here is the sub that converts the display values: the only way I can get this to work was to place a single quote in front of my replacement values. I am assuming that when the file is save to a text file excel drops the single quote-

[VBA]
Sub DougsFindAndReplacev2()
Dim Wsc As Worksheet
Dim c As Range, rng As Range
Dim LRowc As Long
Dim shCalcSetting As Long
Set Wsc = Workbooks("Complete_Upload_File.xls").Worksheets("EC Products")
LRowc = lr(Wsc, 1)

Range("J4:J" & LRowc).Replace "39208", "'5-6", xlWhole, , False
Range("J4:J" & LRowc).Replace "39271", "'7-8", xlWhole, , False
Range("J4:J" & LRowc).Replace "39272", "'7-9", xlWhole, , False
Range("J4:J" & LRowc).Replace "39335", "'9-10", xlWhole, , False
Range("J4:J" & LRowc).Replace "39336", "'9-11", xlWhole, , False
Range("J4:J" & LRowc).Replace "39398", "'11-12", xlWhole, , False
Range("J4:J" & LRowc).Replace "39399", "'11-13", xlWhole, , False
Range("J4:J" & LRowc).Replace "39366", "'10-11", xlWhole, , False
Range("J4:J" & LRowc).Replace "39211", "'5-9", xlWhole, , False
Range("J4:J" & LRowc).Replace "39096", "'1-14", xlWhole, , False

Application.ScreenUpdating = True

End Sub
[/VBA]

Any ideas here?

[rory]

Text files only store values not formatting. If you add the quote mark, the value of the cell is exactly the text you see so that's what you get. If you don't, you are correcting the display by formatting the cells but the values end up in the text file.

Edit: actually, this is completely the wrong way round! Text and CSV files both save the displayed value rather than the actual value.

[mdmackillop]

Rather than hard coding, convert back to date values
[vba]
ActiveCell = "'" & Month(ActiveCell) & "-" & Day(ActiveCell)

'or

With ActiveCell
.NumberFormat = "@"
.Value = Month(ActiveCell) & "-" & Day(ActiveCell)
End With
[/vba]

[Bob Phillips]

How about saving it as a CSV file? Might not be any good, depending upon what you do with it, but CSV seemns to preserve the format.

[YellowLabPro]

Yep- I was afraid you might say that, but at least it agrees w/ my findings.

So I am going to try a different approach.
Col J has the values displayed correctly. Once I perform the next sub it changes the display values.

What I think might be a way around this is to format the entire Column as Text, then place the values in there as the desired display method, e.g
9 1/2 or 9-10 would then be text and not change display methods on subsequent procedures.

Copy Col J to another column, K for example. Format col J as Text.


My question is how do I replicate in code as I would if I were typing the values in by hand?

Copying will not do it, assigning the value will also assign the cell format from col. K.

Does this approach have any possibilities of working?

[YellowLabPro]

Bob,
CSV will not work. The communication between this sheet and the site has to be a Text file.

[YellowLabPro]

Malcolm,
Here is the loop that deals w/ the values w/ a dash:
[VBA]With Wsc
For i = 4 To LRowc
If .Cells(i, "J").NumberFormat = "d-mmm" Then
With Cells(i, "J")
If Len(.Value) > 0 Then
.NumberFormat = "@"
strText = .Value
.Value = strText
.Interior.Color = vbYellow
.NumberFormat = "General"
End If
End With
End If
Next i
End With [/VBA]

If I understand your suggestion correctly, I would put your block in here somewhere, is that correct?

[rory]

Set the Numberformat to "@" (which is Text) and then set the Value property to the source cell's Text property.
PS Did you notice that I corrected my post?

[YellowLabPro]

Rory,
I did not see that you had edited your post.
I did change the "General" to "@", it was a typo on my part, I had had it commented out and then uncommented in the post by accident.

So you are saying that the .txt file should infact show the display value, not the underlying value?

I am testing now.

[rory]

Yes, it should do.

[YellowLabPro]

This problem seems to be bigger than I first thought. Other sizes are losing their display formatting too, my values that are fractions are getting converted to back to decimals.

The reason, I think this is happening is that after copying the data from two sheets to the one target sheet, which is successful, the issue arises when combining of a like items. This is moving records around-, rows are being deleted.

Here is the code that performs the combine like items function:

[VBA]
Sub removeDupes()
Dim dic As Object, w(), d(), y
Dim i As Long, ii As Long, iii As Long, lastr As Long
Dim Wsf As Worksheet, wstemp As Worksheet
Dim sPath As String
Set Wsf = Sheets("EC Products")
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbBinaryCompare
lastr = Wsf.Range("a" & Rows.Count).End(xlUp).Row
w = Wsf.Range("a4:ab" & lastr)
ii = 1
For i = 1 To UBound(w, 1)
If Not dic.exists(w(i, 2)) Then
dic.Add w(i, 2), i
Else
w(dic(w(i, 2)), 18) = w(i, 18) + w(dic(w(i, 2)), 18)
For ii = 1 To 28
w(i, ii) = Empty
Next
End If
Next: Wsf.Range("a4:ab" & lastr).ClearContents
Range("a4").Resize(UBound(w, 1), UBound(w, 2)) = w
y = Wsf.Range(Wsf.Cells(4, 1), Wsf.Cells(lastr, 28)): iii = 1
ReDim z(1 To UBound(y, 1), 1 To UBound(y, 2))
For i = 1 To UBound(y)
If Not IsEmpty(y(i, 1)) Then
For ii = 1 To UBound(y, 2)
z(iii, ii) = y(i, ii)
Next
iii = iii + 1
End If
Next
Wsf.Range("a4").Resize(UBound(z, 1), UBound(z, 2)) = z

End Sub
[/VBA]

[rory]

I think this line is the culprit:
Range("a4").Resize(UBound(w, 1), UBound(w, 2)) = w

A couple of other observations:
1. Your code could really do with some comments!!
2. I personally hate the use of : to put multiple statements on one line - it just serves to make the code less readable and less maintainable.

I think in the long run, you should be looking controlling the format in which the data gets to you rather than all this clean-up; but in the meantime, it might be easiest to only do the formatting at the last step of the process rather than doing it repeatedly!

[YellowLabPro]

Here is an interesting note:
I set up a test file and put the value of 7 1/2, 9-10, 1/8 in cells that were formatted as text, i.e. they displayed these values. I save this file out as a text file and Excel still converted the values as dates,
9-10 turned out to be 10-Sep, 1/8 turned out to be 8-Jan, 7 1/2 was able to retain its display value.

So I really don't know what to do from here.

I am going to continue to test and see if there are any alternatives. I will follow Bob's suggestion, save as CVS and then from there save as a .txt file.

Anyone who comes up w/ a solution, please let me know- getting balder by the moment w/ this one.

[YellowLabPro]

Results:
No matter what I have tested, once the file gets out to a .txt format, Excel converts the dash values into a date. Sorry for the rant here- but that stinks!
I tried going to CSV as xld suggested, but does not good either. So I have taken an alternative approach and substituted the tilde character for the dash.
I am not a big fan of this approach, but seems the only one that is viable at this moment.
I am open to trying something else if anyone has any ideas.

[YellowLabPro]

Bob and Rory,
Thanks for your help on this. We finally resolved it last night. Somewhat of an oversight/blunder on my part.
After saving the file as a .txt file, I was reopening it into Excel to verify my values were the correct display value. That was the error, instead I should have been using Notepad. That provided me w/ accurate file data.
Thanks for all the help.
Ps. Mdmackillop is the one who pointed this out.

[rory]

Doug,
Glad you got it sorted out! All finished now?

[YellowLabPro]

Not quite, but very close. The darn dates are now giving me another fit. I am drafting a post... and will start a new thread. But want to clearly explain it. I am going out for a morning run and then on a few pressing things first, and then send up the other question to the details of it.

Thanks again for the help on this- you were a big help. Even though we did not find the answer directly, the suggestions helped to eventually find the answer- I am just really glad there was one :-)

Best,

Doug