access another UNSAVED Excel instance and UNSAVED Notepad text

if you put
=INFO("directory")
in the cvs befor saving what is the path?

Hello Fire_d,

Your question about reading the text from an unsaved Notepad file roused my curiosity. I don't have any functions in my library to do that nor have I ever tried to do so. So, this morning I decided to write some code to read the data from an unsaved Notepad file.

In this code are 2 VBA macros: StartNotepad and ReadNotepad. When you run StartNotepad, it creates a new (blank) Notepad document.You can type in or paste in the information you want. When you run ReadNotepad, it will return all the text in Notepad as a string. I can modify this for you if needed.

Copy All This Code into a VBA Module

'Written: May 20, 2010
'Author:  Leith Ross
'Summary: This module has 2 macros. One Starts a new instance Notepad and saves the
'         window handle in the Public varaible hWndNote. The other reads all of the
'         current text of this Notepad instance and returns it as a string.

Option Explicit

  Public hWndNote As Long
  
'Window Message Constants
  Private Const WM_GETTEXT = &HD
  Private Const WM_GETTEXTLENGTH = &HE

'Miscellaneous API Constants
  Private Const NORMAL_PRIORITY_CLASS As Long = &H20&
  Private Const INFINITE As Long = -1&
   
  Private Type STARTUPINFO
    cb As Long
    lpReserved As String
    lpDesktop As String
    lpTitle As String
    dwX As Long
    dwY As Long
    dwXSize As Long
    dwYSize As Long
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute As Long
    dwFlags As Long
    wShowWindow As Integer
    cbReserved2 As Integer
    lpReserved2 As Long
    hStdInput As Long
    hStdOutput As Long
    hStdError As Long
  End Type

  Private Type PROCESS_INFORMATION
    hProcess As Long
    hThread As Long
    dwProcessID As Long
    dwThreadID As Long
  End Type

'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
         
'Create a new process
  Private Declare Function CreateProcessA _
    Lib "kernel32.dll" _
      (ByVal lpApplicationName As String, _
       ByVal lpCommandLine As String, _
       ByVal lpProcessAttributes As Long, _
       ByVal lpThreadAttributes As Long, _
       ByVal bInheritHandles As Long, _
       ByVal dwCreationFlags As Long, _
       ByVal lpEnvironment As Long, _
       ByVal lpCurrentDirectory As String, _
       ByRef lpStartupInfo As STARTUPINFO, _
       ByRef lpProcessInformation As PROCESS_INFORMATION) As Long

'Returns the Window Handle of the Window that is accepting User input.
   Public Declare Function GetForegroundWindow _
     Lib "User32.dll" () As Long
        
'Waits until the specified process has finished processing its initial input
'and is waiting for user input with no input pending, or until the time-out
'interval has elapsed.
  Private Declare Function WaitForInputIdle _
    Lib "User32.dll" _
      (ByVal hProcess As Long, _
       ByVal dwMilliseconds 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 CloseHandle _
    Lib "kernel32.dll" _
      (ByVal hObject As Long) As Long


Public Sub StartNotepad()
   
   Dim RetVal As Long
   Dim PI As PROCESS_INFORMATION
   Dim SI As STARTUPINFO

      'Initialize the STARTUPINFO structure:
        SI.cb = Len(SI)
        
      'Start the application:
        RetVal = CreateProcessA(lpApplicationName:=vbNullString, _
                                lpCommandLine:="Notepad.exe", _
                                lpProcessAttributes:=0&, _
                                lpThreadAttributes:=0&, _
                                bInheritHandles:=1&, _
                                dwCreationFlags:=NORMAL_PRIORITY_CLASS, _
                                lpEnvironment:=0&, _
                                lpCurrentDirectory:=vbNullString, _
                                lpStartupInfo:=SI, _
                                lpProcessInformation:=PI)

      'Wait for the application to finish loading:
       While WaitForInputIdle(PI.hProcess, INFINITE) <> 0
          DoEvents
       Wend
         
      'Get the application's window handle:
        hWndNote = GetForegroundWindow()
       
   'Close all handles before exiting:
     Call CloseHandle(PI.hThread)
     Call CloseHandle(PI.hProcess)
                 
End Sub

Public Function ReadNotepad() 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(hWndNote, 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

Hi Leith,

First sorry in the late reply...

I checked your code (great work) and yes I if you may do modifications I would really appreciate.

Let explain better...

The system that generates the text file automatically opens the Notepad with the text already inside of it. I wish I could read from it but the problem is that the document is not saved. I know where it will be saved but it does not matter. In fact when I close the notepad it curiosly get saved without any dialog box. But like I said I wish I could read from it and close it using code...

To simulate that just open the notepad and insert some text manually pretending you are the system that generated in for you. Now try to access it using code.

Your code opens the notepad so that you can track it but the case is that is not my code or yours that will open the notepad is the system that generates the report and opens it with the text....

I see you like some challenge I really liked your code and should say you are lightyears from me... hope you can do it if it's possible... I know there are somethings impossible to do with VBA but this one I really did find out yet...

I had an idea by now... this will not be pretty.. but I will try to kill all notepad processes and see if it still will saved automatically like I said that is strange.. then I will have just to access it... the file always come with name of report.txt... if even I could use that to kill and not all notepad that is open.. well I will see here...


Thank you so much !

Hello Fire_d,

Not a problem. I can change the code to access an opened Notepad file and read the text. It doesn't matter if it has been saved or not. The macro can also close Notepad automatically for you, if you like. Will you have more than Notepad file open at a time? If so, what is the file name?

Hello Fire_d,

I made the following changes to the code. There are 2 macros: FindNotepad and ReadNotepad. FindNotepad searches for an open instance of Notepad. You can use the file name like "Book1.xls" or a partial name like "Report", or no name at all. If no name is supplied then the first open instance of Notepad is used. The ReadNotepad takes the Window handle returned by FindNotepad and returns all of the text as a string. Delete the old code you have and paste this code in it is place. A sample of using the macros is included.

'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_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

Example of Using the Macros

Public Sub ReadNotepadTest()
  Dim Text As String
  'Read the text from any open Notepad file
    Text = ReadNotepad(FindNotepad)
    Range("A1") = Text
End Sub

Hi Leith !

You are the master here !! Wow really great ! you did it !

As you asked before the only file that will be open on the Notepas is the one with the name report.txt. But would like it just to close the one with this name. May I ask you just a final favor ?

This report is a delimeted text separated with the character "|". So I will need that the code see that and fill the cells and and columns as the character "|" apper. Also there is a Tab delimiter afther the columns "DtEmb.Plan". So is like to select Other with the character "|" and Tab in the Convert text to Columns dialog box using excel.

You know that there are a lot of sample over the internet that do that but I did not find one that fit into this code... sorry my inexperience here. Plese could insert this function and the one close the notepad one once all the text is export to a Excell worksheet ?

I was saving the .txt file and open with vba and all the text is being separate into columns automatically... but getting text this way will need this functionality... I think the way you can do this by judging these codes of yours probably will be far better the another source... check bellow a tiny bit of the report raw text just some rows. ..just need into columns... I will be formating this garbage afterwards. You are the god in here man really magical. If you can tell what books I should read the know this stuff.... I programming is basic near these codes... Thank you again !



Código do Item |Descri. | Data PO| PO #|Pos|St|Refer.| Dt Nec| Dt Emb|Dt Conf Emb|Atr EMB| Pick Up|Dt Invc.|Invoice da PO |Dt Entr |Atr Entr|Qtde Ord|UM|Sald Pend|Qtd Receb| Preço |UP|Moe| Total|Fornecedor

|DtEmb.Plan
--------------------------------+--------+--------+------+---+--+------+--------+--------+-----------+-------+--------+--------+---------------+--------+--------+--------+--+---------+---------+------------+--+---+------------

+-----------------+----------
ERB-BML162180/9 |CST-75/S|06/06/08|402808| 7|AR| |06/06/08|26/05/08|31/12/09 |0 | | | |23/01/10|0 | 2388|pc| 2388| | 8,64000|pc| R$| 20632,32000|N90074-SALCOMP IN|
--------------------------------+--------+--------+------+---+--+------+--------+--------+-----------+-------+--------+--------+---------------+--------+--------+--------+--+---------+---------+------------+--+---+------------

+-----------------+----------
ODM01-14537 |R 0R1 0W|08/07/08|912258| 6|AR| |08/07/08|22/06/08|30/12/09 |0 | | | |22/01/10|0 | 100000|pc| 100000| | 0,00560|pc|USD| 560,00000| I029-KOA SPEER |
--------------------------------+--------+--------+------+---+--+------+--------+--------+-----------+-------+--------+--------+---------------+--------+--------+--------+--+---------+---------+------------+--+---+------------

+-----------------+----------

Hello Fire_d,

I believe that the macro is parsing the data in your example correctly, but you should double check it. If you had posted a copy of the text file, I could be sure. The main macro CopyAndCloseNotepad calls the macro CopyNotepadToWorksheet. It separates the data into columns using a pipe character "|". Since you said you have only one Notepad file open, the macro uses the open Notepad file. Once the data is copied to the worksheet, Notepad is closed without prompts or saving the file. Copy all this code into a single VBA module. If you have questions or run into problems, let me know.

'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 "|"
  
End Sub