+ Reply to Thread
Results 1 to 20 of 20

Convert Notepad to Excel

  1. #1
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Convert Notepad to Excel

    I get invoice in the notepad form as attached.
    I get quite a few invoices like this every week.
    I need a VB macro to convert this invoice into excel sheet as attached.

    Once converted save the file with name as Date-Store#-Vendor-InvNumber; 2010-05-18-XYZ-ABC#454061


    Let me know if you have any further question.

    Please help ....

    RIZ MOMIN
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-05-2010
    Location
    SD, US
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Convert Notepad to Excel

    In VB, open the text file, read it line by line and perform any tests on the line as needed and write each line to a row on a spreadsheet. Copy this worksheet to it's own book and save.

  3. #3
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Convert Notepad to Excel

    I would still need help to complete this task.
    Need VB script to perfrom read each line in notepad and write in excel, etc..

    Please help...

    Riz

  4. #4
    Registered User
    Join Date
    12-10-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Convert Notepad to Excel

    the problem you will have is that there is no real structure to the text file. So its not CSV, tab deliminated etc. Even the spaces between columns are inconsistent from what I can see. The below will read through the file line by line although someone will need to spend a fair bit of time parsing each row to determine its contents and then extracting the relevant parts through VBA. You need to add a reference in VBA to Microsoft Scripting Runtime. Can the output txt file be altered to make it easier to parse?

    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Convert Notepad to Excel

    Yes you can alter the output txt file to make it easy to parse..

    Please help...

    Riz

  6. #6
    Registered User
    Join Date
    12-10-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Convert Notepad to Excel

    see attached. Only enhancement I would suggest to anyone else is its probably possible to match on multiple spaces using regex without the additional split().

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Convert Notepad to Excel

    Hi Phil;

    Its giving an error...

    Please help..

    Riz

  8. #8
    Registered User
    Join Date
    12-10-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Convert Notepad to Excel

    what is the error, did a message come up, did Excel crash? Please be explicit with any issues.
    Last edited by shg; 06-06-2010 at 06:03 PM. Reason: deleted spurious quote

  9. #9
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Convert Notepad to Excel

    i saved excel file you created "ParseData.xls.
    I changed the name of the txt file which needs to be read.
    when i run the macro, it gives Comile error: User defined type not define.
    Gives yellow highlight on
    Public Sub readfile()

    and the cursor is highted on
    Dim regex As New RegExp

    Please let me know if i am doing something wrong..

    Thank you so much for helping..

    Riz

  10. #10
    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: Convert Notepad to Excel

    In the VBE, Tools > References, scroll down and tick Microsoft VBScript Regular Expressions
    Entia non sunt multiplicanda sine necessitate

  11. #11
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Convert Notepad to Excel

    ok thanks...after making that changes now it is giving
    Compile error:
    Method or data member not found

    Cursor is now highted on

    regex.MultiLine = False

    Please help

    Riz

  12. #12
    Registered User
    Join Date
    12-10-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Convert Notepad to Excel

    yes as above, thanks. Looks like one of the references didn't get saved. Should now work as expected.

  13. #13
    Registered User
    Join Date
    12-10-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Convert Notepad to Excel

    Quote Originally Posted by rizmomin View Post
    ok thanks...after making that changes now it is giving
    Compile error:
    Method or data member not found

    Cursor is now highted on

    regex.MultiLine = False

    Please help

    Riz
    did you selected Microsoft VBScript Regular Expressions 5.5?

    Worst case just remove that line as it has little relevance in this situation.

  14. #14
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Convert Notepad to Excel

    Hi Phil:

    Yes after taking that line out, it works...
    However, it is only giving 3 column of data..
    Price/ CS CS Total Cost

    Is it possible to get full line of data...

    Thank U so much in helping

    Riz

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

    Re: Convert Notepad to Excel

    Hi rizmomin

    Just adding to save having to reference the library use
    Please Login or Register  to view this content.
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  16. #16
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Convert Notepad to Excel

    I need full line of data...please help...
    We can ignore alternate line where there is DQ only...

    Thanks
    Riz

  17. #17
    Registered User
    Join Date
    12-10-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    34

    Re: Convert Notepad to Excel

    hi on your first s/s it only shows these 3 columns.. I have enhanced the original version and it now will work on all columns. Difficulty was the description in one area has 2 spaces between words which meant I had to split the row into 3 sections.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by phil_b; 06-06-2010 at 07:01 PM.

  18. #18
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Convert Notepad to Excel

    Please help me get all data from line into excel.
    We can ignore alternate line where there is only DQ...

    Thank U

  19. #19
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Convert Notepad to Excel

    Hi Phil:

    You are super...it works

    Thanks a lot and let me check i need any further modifications...

    Thank U

    Riz

  20. #20
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Convert Notepad to Excel

    Hi Phil:

    On the last page of the invoice, at the bottom i see Ship Handing charges and Tax.
    I would like to see if this can be added ....

    Also i would like to see Invoice Date and Invoice # in Column 1 and 2...you can have same date and invoice # on all lines...

    Please see if this can be achieved...

    Thank U....

    Riz

+ 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