VBA to Export to Notepad from Excel.

[shiva_reshs]

Hi,

In sheet1, I have a values in Coloum A, B , C and D.

A : John,
B : 1283
C : 01/10/2013
D : USD

Is it possible to convert/export value to "note pad" by using ""|"" as separator with the help of VBA?

John | 1283 | 01/10/2013| USD

Thanks,
SHiva

[zbor]

Do you mean something like this:

[ragulduy]

Please Login or Register  to view this content.

[shiva_reshs]

Thanks a Bunch!!

Both of your code works...

Regards

[shiva_reshs]

Instead of default location of C drive, Can I get a option such "Save as" to save the notepad where I intend to save???

Thanks

[shiva_reshs]

Yodlugar,

Instead of default location of C drive, can I get a option such "Save as" to save the notepad where I intend to save???

Below code helps me to bring the popup of Save as for Excel.

Please Login or Register  to view this content.

Thanks

[ragulduy]

I think you already have the answer...

Please Login or Register  to view this content.

[shiva_reshs]

you are the best!!!

Thanks!
Shiva

[shiva_reshs]

Hey Yodlugar,

My apologies that I did not put my request correctly. Code works but it pick up only one row. I have more then 100 rows which need to be converted with your code.

Please advice.

Thanks

[ragulduy]

Do you want each row in the worksheet to be a new line in the text file? This line is what writes the first row:

Please Login or Register  to view this content.

to put each row onto a new line you would change it to a loop such as

Please Login or Register  to view this content.

which would go from from rows 1 to 100

[shiva_reshs]

I modified your code as below

Please Login or Register  to view this content.

Your code works, but my sheet data is dynamic. So sometime it is only 50 rows and sometime it is more then 50(Random).
Your code also picks up blank value and pick up "|" and paste it in Notepad when make it "For Count" in code.

I can manually delete extra blank "|" from notepad, but will there be any easy workaround in your code?

Thanks

[shiva_reshs]

Please Login or Register  to view this content.

Got it working finally..

Thanks guys..

[ragulduy]

You could add in something like:

Please Login or Register  to view this content.

to deal with the blanks.

Also:
Range("A" & rows.count).end(xlup).row
tens to be more robust than
currentregion.rows.count
(currentregion can sometimes give the wrong number if you do a lot of adding and deleting stuff to a worksheet).

[shiva_reshs]

You mean like this ?

Please Login or Register  to view this content.

Another help..

1) How to tweak the code, so it wont select the first line which is the header.?
2) A cell data is my effective date column. Format is 2013-08-04 but when it writes to notepad it writes as 08/04/2013. How can ensure it copies as it is data from cell.?

[Norie]

http://www.mrexcel.com/forum/excel-q...pad-excel.html

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[shiva_reshs]

I am sorry. I just thought it would be against to post other forum addresses in this forum which would likely to divert user to other site.

Thats the reason when I found the answer in other web page, I put the answer back in this thread so people can know what the answer was to my questions.

Apologies for cross - posting...

Thanks

[shiva_reshs]

I found the solution..

Please Login or Register  to view this content.

Yudlugar, I added your code but could you help me if I put it right in this code??

Please Login or Register  to view this content.