writing to txt file, sendkeys and weird excel prob!

Hiya,

I've got a problem with some data in Excel.

I use a spreadsheet to capture multiple reports and sort the data, the reports are gathered by copying the worksheet in to my workbook.
This all works fine but the data in one report refuses to be formatted as [hh]:mm:ss or anything other than text for that matter.

Apparently there is a hidden character that denotes the format of the cell ( ' being for text)
So i have all these times that are formatted as text, the only way to change them is to physically click into the cell, as if one was to edit the contents, and return out of it, the format that correctly displays as time.

It has been suggested that copying the data into notepad and back again solves this problem.
Unless anyone knows how to correct the source problem, i'm now stuck with this code


'Database logging
Dim fso, a, logFile, WSHNetwork

'change following log path and file name as needed
logFile = Left(ActiveWorkbook.FullName, (Len(ActiveWorkbook.FullName) - 8)) & "\holder.txt"

Set fso = CreateObject("Scripting.FileSystemObject")
Const OPEN_FILE_FOR_APPENDING = 8

Set a = fso.OpenTextFile(logFile, OPEN_FILE_FOR_APPENDING)
'write data
a.writeline
'close file
a.Close
Set fso = Nothing


at a.writeline i need paste, or the sendkey "^V" to work but cant get this to happen, further on i would also need select all, copy too.

Any ideas?

Many Thanks,
Nick

I'm pretty confident that using notepad is overkill here. ;)

Have you tried something like this?

Sub test()
Dim rngText As Range

Set rngText = Worksheets(1).Range("A1:A30")

With rngText
.Value = .Value
.NumberFormat = "[hh]:mm:ss"
End With
End Sub


If that doesn't work, could you attach a zip file with some sample data?

Colin

thanks for the quick response, i used this


rng = "D12:G" & (skurg - 3)
Range(rng).Select
Selection.NumberFormat = "[hh]:mm:ss"
Selection.Replace What:=":", Replacement:=":", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Apparently MS Excel only picks up a cell format change after it's contents have been edited. The above script does exactly that.

cheers all, have a good day

This is what I've been using to convert dates stored as text into whatever format I have applied to the cell:

Sub selection2date()
Dim arrOne(), A&
arrOne = Selection 'load the selection into an array
For A = LBound(arrOne) To UBound(arrOne) 'loop through the array
If WorksheetFunction.IsNumber(arrOne(A, 1)) Then 'determine whether value is a number
arrOne(A, 1) = CDate(arrOne(A, 1)) 'convert the number to a date
End If
Next
Selection = arrOne() 'return the array to the spreadsheet
End Sub

I keep this in my personal macro workbook and have a shortcut key assigned to it, so anytime I have a spreadsheet with dates stored as text I can quickly force Excel to recognize them as dates by selecting the cells (or entire columns/rows) then running the macro.

----edit----

I just went back to play with this a bit more; thanks to Colin's suggestion my macro has now been simplified to:
Sub text2value()
With Selection
.Value = .Value
End With
End Sub