1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code: Writing to Notepad

Discussion in 'VBA Macros' started by Monty, Apr 19, 2017.

  1. Monty

    Monty Well-Known Member

    Messages:
    717
    Hello Everyone.

    Simple one but killed several hours to do this..

    Basically wanted to write one column excel to note pad which is working as below..but wanted the output to be continuous rather then in a single column.

    Attached the excel file with code.

    Present output

    upload_2017-4-20_0-13-4.png

    EXPECTED OUTPUT

    upload_2017-4-20_0-13-20.png

    This can also be achieved with simple Concatenation formula but it has limitation to 8000 characters...But i have 2,00,000 + rows.

    So trying for alternate solution with vba.

    Thanks
    Monty!
  2. Monty

    Monty Well-Known Member

    Messages:
    717
    Sorry Missed Attachment!

    Attached Files:

  3. Deepak

    Deepak Excel Ninja

    Messages:
    2,731
    Monty likes this.
  4. Monty

    Monty Well-Known Member

    Messages:
    717
    Deepak..

    Great piece of code..Tested but unfortunately it is supporting upto 1500 rows only more then that shows you as #Value.

    As my requirement goes in 200,000 +...Need to see alternate.

    Thanks
  5. Deepak

    Deepak Excel Ninja

    Messages:
    2,731
    Complete read & check the comments....
  6. Monty

    Monty Well-Known Member

    Messages:
    717
    Hey Deepak.

    Tried all the functions available in the link provided none of them able to take more then 1500 rows..

    Monty!
  7. Marc L

    Marc L Excel Ninja

    Messages:
    3,025
    Hi !
    As writing to Notepad is like writing in Excel,
    a no sense as you just need to create a file ! (text file « for Notepad »)
    So you also forgot to attach the expected text file
    according to source workbook …

    Just notice this is at very beginner level just reading VBA inner help
    of Print # for example - and without the need to concatenate - or
    any tutorial on web about reading / writing a text file …

    Edit Print #1, Range("A" & iCntr);
    Chihiro likes this.
  8. Monty

    Monty Well-Known Member

    Messages:
    717
    Hello Marc.

    A simple concatenate of all rows into a single row what am looking.

    Tried with concatenate formula ...But it has limitations.
    So trying with VBA.

    Finally want all rows into one cell..Then simply we can copy and paste to notepad.

    Monty
  9. Marc L

    Marc L Excel Ninja

    Messages:
    3,025

    No need any concatenate as per solution in my previous post !
    And as it's very not a « paste to Notepad » but just a text file writing ‼
  10. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,225
    @Monty
    Just like Marc wrote, add ";" argument to end of your Print line. That's all you need.
  11. Deepak

    Deepak Excel Ninja

    Messages:
    2,731
    This is what you need to change..

    Code (vb):
    Print #1, Range("A" & iCntr);
     
    & It's fast enough so doesn't need anything else as already suggested by the valuable inputs.

    • The link was for the Concatenate ; whenever you need in future!
  12. Monty

    Monty Well-Known Member

    Messages:
    717
    Working perfectly...Thanks to Marc..Chihro and Deepak.
  13. Marc L

    Marc L Excel Ninja

    Messages:
    3,025
    Deepak likes this.
  14. Monty

    Monty Well-Known Member

    Messages:
    717
  15. Deepak

    Deepak Excel Ninja

    Messages:
    2,731
    Thanks Marc L for the light on the same! I might missed that post!

    Monty : Thanks you too as i leaned something new too today!!
  16. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,598
    Similar to Marc's version except the Application.Index

    Code (vb):
    Sub Test2()
    Dim varData As Variant: varData = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Dim strFile_Path As String: strFile_Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\Test.txt"
    Dim outArr()
    ReDim outArr(UBound(varData) - 1)
    For i = LBound(varData) To UBound(varData)
        outArr(i - 1) = varData(i, 1)
    Next i
    Dim outstr As String
    outstr = Join(outArr, ",")
    Open strFile_Path For Output As #1
    Print #1, outstr
    Close #1
    MsgBox "Done"
    End Sub
    Marc L and Deepak like this.
  17. Deepak

    Deepak Excel Ninja

    Messages:
    2,731
    As it was expected & thinking about that...
    Using array would be fast rather than writing line by line & to check it i have did few test & found below result on a PC having Core2Duo+4GB Ram+Win7+Excel 2007.

    Best results are...

    HTML:

    Shri : 2000 cells_0.00390625
    Shri : 20000 cells_0.04296875
    Shri : 200000 cells_0.375
    Shri : 500000 cells_0.9296875
    Shri : 1000000 cells_1.88671875

    Marc : 2000 _ 0.0625
    Marc : 20000 _ 0.6484375
    Marc : 200000 _ 6.4453125
    Marc : 500000 _ 16.16015625
    Marc : 1000000 _ 32.41015625
     
    Array was almost 17 times faster than one by one writing.

    Shri Test2 & Marc Demo2 code used for the test.
  18. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,598
    It is due to fact that most of the processing happens in memory as there's no interaction with worksheet post loading of data from the range.
  19. Marc L

    Marc L Excel Ninja

    Messages:
    3,025
    Yes !

    And the way I used INDEX worksheet function in the link is not the best
    'cause at this time I had an issue for only one row …

    Since I've found the right way for directly use only a row with INDEX
    but I suppose it may be slower than directly processing an array.

Share This Page