writing to txt file, sendkeys and weird excel prob!

07-30-2008, 05:12 AM

OriginalNick2 OriginalNick2 is offline

Freshman

Join Date: Oct 2004

Posts: 37

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

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

07-30-2008, 05:31 AM

Colin Legg

Out Of Office

Retired Moderator
* Expert *

Join Date: Mar 2005

Location: London, UK

Posts: 3,398

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

Have you tried something like this?

Code:

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

__________________
RAD Excel Blog

07-30-2008, 05:42 AM

OriginalNick2 OriginalNick2 is offline

Freshman

Join Date: Oct 2004

Posts: 37

thanks for the quick response, i used this

Code:

        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

07-30-2008, 07:45 AM

geodekl

Centurion

Join Date: Feb 2004

Posts: 134

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

Code:

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:

Code:

Sub text2value()
    With Selection
        .Value = .Value
    End With
End Sub

Last edited by geodekl; 07-30-2008 at 11:18 AM. Reason: add comments to code