access another UNSAVED Excel instance and UNSAVED Notepad text

Hi everyone newcomer here !!

I searched around all the internet and cannot find an answer or something that I could use to solve this issue... it seems impossible and I am really mad about it... I am coming into here to see if u guys are good..

I will try to make it simple:

The ERP system that runs where I work generates 2 formats of reports that could be .csv or .txt. So when it generates the reports it opens a Excel application or a Notepad application containing delimited text.

By this time I will have an Excel workbook already open containing a VBA macro module that will process and format properly all the information in the reports that I have to copy and paste manually from these .csv or .txt reports.

The problem:

The .csv workbook is open in another instance of Excel and both .csv and .txt file are UNSAVED, so there are NO PATH to get.

The question:

Is it possible to copy the text from the .txt file opened in Notepad and .csv opened in another Excel instance to my active Excel instance workbook knowing they are both unsaved and without paths ?

Sorry if my question is not clear... I believe that for the question no code was needed... all that I find over the internet needs the files paths... I tried to access by the windows processes like in the task manager too with now sucess... anyway please HELP ME

Hi Fire_d,
what result do you get if you enter the info function in a cell of the cvs workbook
=INFO("directory")

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

I will a make a new thread about the new problem. And really thanks Leith Ross. See you soon !

Link to the new thread:

http://www.excelforum.com/excel-prog...ml#post2315863

Alex,

By not following FORUM RULE #2 you are delaying getting an answer indefinitely.

Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants who may no longer participate on the forum at all.
New threads not only open you up to all possible participants again, they typically get faster response, too.