'Written: May 21, 2010
'Updated: May 29, 2010 - Corrected Date format problem
'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 Variant
Dim Data As Variant
Dim EOL As String
Dim hWnd As Long
Dim I As Long
Dim LineRead As Variant
Dim N As Long
Dim R As Long
Dim Rng As Range
Dim Text As String
Dim Wks As Worksheet
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
'Format columns with dates
Application.ScreenUpdating = False
For Each C In Array("C", "H", "I", "J", "O")
With Columns(C).Cells
.Formula = Columns(C).Cells.Formula
.NumberFormat = "dd/mm/yy"
.HorizontalAlignment = xlHAlignCenter
End With
Next C
Application.ScreenUpdating = True
'Close the Notepad file without prompts or saving
CloseNotepad hWnd
End Sub
LinkBack URL
About LinkBacks
Fire_d 
Convert Excel cells stored as text to date and number coming from notepad
to import delimited text from an unsaved file opened on Notepad to an Excel worksheet.
Register To Reply
below the post.
Bookmarks