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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Example of Using the Macros

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Hello Fire_d,

If the Notepad file you are looking for will always have the name "report.txt" change the CopyAndCloseNotepad macro to this:

Please Login or Register  to view this content.

[/b][/b]
The other problems I will need to spend some time on to correct.

Yes, a new thread, please, with an example of the text file.

Hello Fire_d,

You can reset the rules for flagging errors. Try this..

1. On the Main Menu, select Tools then Options...
2. Click the tab labeled Error Checking
3. Under the Rules section, look for the 2 check boxes: Text date with 2 digit years, Number stored as text.
4. If either is checked then click on the check box to clear it.

Hello Fire_d,

Can you zip the workbook and a sample text file together and post them? It would be best to use your program and data to isolate the problem.

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.