Convert Excel cells stored as text to date and number coming from notepad

Hi all !!


I am using the code below from the great Leith Ross to import delimited text from an unsaved file opened on Notepad to an Excel worksheet.

The problem is that coping all and pasting all the text from notepad to a worksheet is giving errors on the numbers and dates. Appears little green triangles on the left top o the cells asking to covert text to numbers and 2 year digit date to a 4 year digit date.

I tried to uncheck the Text date with 2 digit years and Number stored as text in the Excel error checking options as Leith suggested (I could use code for that). That will just make the green triangles disappear.

If I try to custom the format of the date like 25-Jul-08 or make operations with the numbers using vba code it won't work.

Strange enougth is that if copy all the text from notepad and paste on the worksheet it give no errors in the numbers and will make all 2 digit year date into 4 digit... just curious why no code can change the cells perhaps when I paste I need do some sort paste special....

The text file is attached and the code that is pulling the text done Leith is below :

 

'Written: May 21, 2010
'Author:  Leith Ross
'Summary: Looks for an open Notepad file either by file name (no path), partial file name,
'         or no name. If no name is specified the first Notepad file found is used.

Option Explicit

'GetWindow Constants
  Const GW_CHILD = 5
  Const GW_HWNDFIRST = 0
  Const GW_HWNDLAST = 1
  Const GW_HWNDNEXT = 2
  Const GW_HWNDPREV = 3
  Const GW_OWNER = 4

'Window Message Constants
  Private Const WM_CLOSE = &H10
  Private Const WM_GETTEXT = &HD
  Private Const WM_GETTEXTLENGTH = &HE

'You can use the GetDlgItem function with any parent-child window pair, not just with
'dialog boxes. As long as the hDlg (hWnd) parameter specifies a parent window and the
'child window has a unique identifier (as specified by the hMenu parameter in the
'CreateWindow  or CreateWindowEx  function that created the child window),
'GetDlgItem returns a valid handle to the child window.
  Private Declare Function GetDlgItem _
    Lib "User32.dll" _
      (ByVal hDlg As Long, _
       ByVal nIDDlgItem As Long) As Long

'Send messages to windows
  Private Declare Function SendMessage _
    Lib "User32.dll" _
      Alias "SendMessageA" _
        (ByVal hWnd As Long, _
         ByVal wMsg As Long, _
         ByVal wParam As Long, _
         ByRef lParam As Any) As Long
            
'Get the length of a Window's caption
  Private Declare Function GetWindowTextLength _
    Lib "User32.dll" _
      Alias "GetWindowTextLengthA" _
        (ByVal hWnd As Long) As Long
   
'Get the caption of a Window as a string
  Private Declare Function GetWindowText _
    Lib "User32.dll" _
      Alias "GetWindowTextA" _
        (ByVal hWnd As Long, _
         ByVal lpString As String, _
         ByVal nMaxCount As Long) As Long

'Return the length of a null terminated string
  Private Declare Function StrLen _
    Lib "kernel32.dll" _
      Alias "lstrlenA" _
        (ByVal lpszString As String) As Long
  
  Private Declare Function GetWindow _
    Lib "User32.dll" _
      (ByVal hWnd As Long, _
       ByVal wCmd As Long) As Long

  Private Declare Function GetDesktopWindow _
    Lib "User32.dll" () As Long

  Private Declare Function GetClassName _
    Lib "User32.dll" _
      Alias "GetClassNameA" _
        (ByVal hWnd As Long, _
         ByVal lpClassName As String, _
         ByVal nMaxCount As Long) As Long


Public Function FindNotepad(Optional FileName As String) As Long

  Dim Caption As String
  Dim ClassName As String
  Dim L As Long
  Dim TopWnd As Long
  
     'Find any open Notepad file or one whose file name matches (whole or partial name)
      If FileName = "" Then
         FileName = "*"
      Else
         FileName = "*" & FileName & "*"
      End If
      
     'Start with the Top most window that has the focus
      TopWnd = GetWindow(GetDesktopWindow, GW_CHILD)

     'Loop while the hWnd returned by GetWindow is valid.
      While TopWnd <> 0
        'Get Window caption
         L = GetWindowTextLength(TopWnd) + 1
           Caption = String(L, Chr$(0))
           L = GetWindowText(TopWnd, Caption, L)
         Caption = IIf(L > 0, Left(Caption, L), "")
         
        'Get the Window Class name
         L = GetWindowTextLength(TopWnd) + 1
           ClassName = String(L, Chr$(0))
           L = GetClassName(TopWnd, ClassName, L)
         ClassName = IIf(L > 0, Left(ClassName, L), "")
         
         If Caption Like FileName And ClassName = "Notepad" Then
            FindNotepad = TopWnd
            Exit Function
         End If
        
        'Get the next Window
         TopWnd = GetWindow(TopWnd, GW_HWNDNEXT)
         
        'Process Windows events.
         DoEvents
      Wend
                 
End Function

Public Function ReadNotepad(hWnd As Long) As String

  Dim Buffer As String
  Dim BuffSize As Long
  Dim chWnd As Long
  Dim nEditID As Long
  Dim RetVal As Long
       
    'Get the window handle of the Edit Control which is a child window of Notepad:
      nEditID = 15
      chWnd = GetDlgItem(hWnd, nEditID)
    
    'Get the character count of the text and setup a buffer to hold it:
      BuffSize = SendMessage(chWnd, WM_GETTEXTLENGTH, 0, 0) + 1
      Buffer = String(BuffSize, Chr$(0))
    
    'Read the text from Notepad into the buffer:
      RetVal = SendMessage(chWnd, WM_GETTEXT, BuffSize, ByVal Buffer)
      If RetVal <> 0 Then
         ReadNotepad = Left(Buffer, StrLen(Buffer))
      End If
      
End Function

Public Sub CloseNotepad(ByVal hWnd As Long)

  Dim RetVal As Long
  
    If hWnd <> 0 Then
       RetVal = SendMessage(hWnd, WM_CLOSE, 0&, 0&)
    End If
    
End Sub

Sub CopyNotepadToWorksheet(Optional ByVal Separator As String, Optional FirstCell As Range, Optional ByVal FileName As String)
  
 'This macro copies the Notepad file with the given title to the designated cell. Data is divided into
 'columns based on the separator character string.
 '
 'Default settings:
 '   Separator = Comma
 '   FirstCell = "A1" of the Active Worksheet
 '   FileName = The first opened instance of Notepad found.
 
  Dim C As Long
  Dim Data As Variant
  Dim EOL As String
  Dim hWnd As Long
  Dim I As Long
  Dim LineRead As String
  Dim N As Long
  Dim R As Long
  Dim Text As String
  
    If FirstCell Is Nothing Then
       Set FirstCell = Range("A1")
    Else
       Set FirstCell = FirstCell.Cells(1, 1)
    End If
    
    Separator = IIf(Separator = "", ",", Separator)
    
    hWnd = FindNotepad(FileName)
    If hWnd = 0 Then
       MsgBox "Notepad File not Open.", vbOKOnly + vbExclamation
       Exit Sub
    End If
    
    I = 1
    EOL = vbCrLf
    Text = ReadNotepad(hWnd)
    
      Do
       'Find the first character marking the end of the line
        N = InStr(I, Text, EOL)
        If N > 0 Then
          'Line with end of line string
           LineRead = Mid(Text, I, N - I)
        Else
          'Line without end of line string
           LineRead = Mid(Text, I, Len(Text) - I + 1)
        End If
       'If N = I the character(s) read is/are a separator
        If N - I <> 0 Then
          If LineRead <> "" Then
             Data = Split(LineRead, Separator)
             FirstCell.Offset(R, 0).Resize(1, UBound(Data) + 1) = Data
             If N > I Then R = R + 1
          End If
        End If
       'Advance the search Index to the next line
        I = N + Len(EOL)
      Loop While N > 0
      
   'Close the Notepad file without prompts or saving
    CloseNotepad hWnd
     
End Sub


Sub CopyAndCloseNotepad()

  CopyNotepadToWorksheet "|", , "report.txt"   'The file name is case sensitive
  
End Sub

Link from previous threat:

http://www.excelforum.com/excel-prog...epad-text.html