+ Reply to Thread
Results 1 to 20 of 20

access another UNSAVED Excel instance and UNSAVED Notepad text

  1. #1
    Registered User
    Join Date
    05-18-2010
    Location
    Brazil
    MS-Off Ver
    Excel 2003
    Posts
    14

    Angry 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
    Last edited by Fire_d; 05-19-2010 at 12:09 AM.

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    Hi Fire_d,
    what result do you get if you enter the info function in a cell of the cvs workbook
    =INFO("directory")
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Registered User
    Join Date
    05-18-2010
    Location
    Brazil
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    Hi pike,


    Really good point. Now I have the path ! Both are saved in the same directory. Thank you !

    Now I can automate almost all the process...

    In the notepad case I have to close it manually the runs the macro to get the information using that path...

    For the .csv file I have to do some click to save the file before...

    You bring me one solution... but still there is not way to access the files because they have no path before a close notepad or save the .csv...

    I wish I could get the text from these without need of close or save them manually...

    If I could access even the notepad get the text and closes it by code would be nice... do you have any adeas ?

  4. #4
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,330

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

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

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    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.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  6. #6
    Registered User
    Join Date
    05-18-2010
    Location
    Brazil
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    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 !
    Last edited by Fire_d; 05-21-2010 at 12:54 AM.

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    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?

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    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.
    Last edited by Leith Ross; 05-21-2010 at 01:03 PM.

  9. #9
    Registered User
    Join Date
    05-18-2010
    Location
    Brazil
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    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 |
    --------------------------------+--------+--------+------+---+--+------+--------+--------+-----------+-------+--------+--------+---------------+--------+--------+--------+--+---------+---------+------------+--+---+------------

    +-----------------+----------
    Last edited by Fire_d; 05-22-2010 at 03:53 PM.

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    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.

  11. #11
    Registered User
    Join Date
    05-18-2010
    Location
    Brazil
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    Hi Mr. Leith Ross!

    I am so late in the responses really sorry for that. Lots of work and study… need more time...

    Well, I tested your code. And OK it runs GREAT !
    You are right only this file should be open with the name "report.txt". But if some another instance of notepad is open first it will not work… I tried to specify the FileName as "report.txt" and it did not work…sorry my ignorance.. May you make it gets only the file with the name I give even if there is another instance of notepad opened first or after the one I need ? In this case is report.txt… I just want that to skip errors in case more than one notepad is opened...

    Another point is the cells that have numbers. All the cells comes with formatting errors. I am without lucky and get stuck again….
    For the ones that hold date it gives the shows the errors about converting the date from a 2 year digit to a 4 digit and for the one that is not date it shows errors about converting text to number. Manually one way to solve is to pretend to edit the cell and go of it and the other is roll the cursor over the yellow exclamation point and make the change. I tried by code alter it with no success…perhaps change cell value and row back… any suggestion on this ?

    Again sorry all this text and questions… your being so nice with me… Really great forum I found…

    I have done good codes but this one is getting too especific...

  12. #12
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    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.

  13. #13
    Registered User
    Join Date
    05-18-2010
    Location
    Brazil
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    OK Leith,


    I did not notice the parameters, thanks. It worked but know really NICE ... I am facing another problem that seems to be really strange. Tell me if I have to open a new post for that.

    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. If you have an idea please tell because nothing I try seems to "move" the cell using code, it seem an crazy bug. The only way around seems to be manually "F2 and Enter". Please help on this too if you can...


    Thanks in advance...

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    Yes, a new thread, please, with an example of the text file.
    Entia non sunt multiplicanda sine necessitate

  15. #15
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    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.

  16. #16
    Registered User
    Join Date
    05-18-2010
    Location
    Brazil
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    OK Leith,

    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. And besides I wish I could run this code in other machines I will have to configure obrigatory.

    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....

  17. #17
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    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.

  18. #18
    Registered User
    Join Date
    05-18-2010
    Location
    Brazil
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    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
    Last edited by Fire_d; 05-29-2010 at 02:39 PM.

  19. #19
    Registered User
    Join Date
    12-04-2014
    Location
    London
    MS-Off Ver
    2010
    Posts
    1

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    Hi Leith!

    I found your code really useful. If you had two notepads open with the same name, how would you modify the code?

    Thank you!
    Alex

  20. #20
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: access another UNSAVED Excel instance and UNSAVED Notepad text

    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.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1