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

  • 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

Monty

Monty

Well-Known Member
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!
 
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
 
Hey Deepak.

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

Monty!
 
Hi !
Monty said:
Sorry Missed Attachment!
Click to expand...
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);
​
 
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
 

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 ‼
​
 
This is what you need to change..

Code: 
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!
 
Similar to Marc's version except the Application.Index

Code: 
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
 
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.
 
Deepak said:
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.
Click to expand...
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.
 
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.
​
 
You must log in or register to reply here.
Back
Top