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

Copy and paste to a notepad file

V

Vipin Vijayan

Member
Hi

I have created a excel macro, after running this macro I will get a range of data in a copied format that needs to be copy to a notepad. Is there any automation possibility in macro to copy this copied data to a notepad directly.

Please help.......
 
Would you pls upload your sample file along with desired output in notepad.

OR try something like below.
Code: 
Option Explicit

Sub txt_export2()
Dim buf As String, col As Long, r As Long, ws As Worksheet
Dim d As String, rng As Range, c As Range, row As Long

Set ws = ActiveSheet
d = "|"

Open "C:\Export.txt" For Output As #1'txt file location
With ws
    col = .UsedRange.Columns.Count
    row = .UsedRange.Rows.Count
        For r = 1 To row
            Set rng = .Range(.Cells(r, 1), .Cells(r, col))
                For Each c In rng
                    buf = buf & c.Text & d
                Next
                buf = Left(buf, Len(buf) - Len(d))
            Print #1, buf
        buf = ""
        Next r
End With
Close #1
Set rng = Nothing
Set ws = Nothing
End Sub
 
Hi deepak,

thanks for your updation. I am currently working on company system. due to the privacy policy, the system admin blocked the uploading function. Thats why i couldn't upload the file to site.

second macro is not working on my file. it may be the mistake from my file.

the first macro use posted is partially working. a notepad is opening but the copy n paste function is not working. if this function works, it will be great.

Thanks

Vipin
 
Is it possible to email the file then send it to *********
or drag it in a usb & then upload from somewhere else.
 
Last edited:
I just realised that in some case this doesn't work so try to check after replacing below line.

Code: 
Shell "notepad.exe", vbMaximizedFocus

in second code
change this to another location like as C:\Users\dEEPAK\Desktop then try
Code: 
Open"C:\Users\dEEPAK\Desktop\Export.txt" For Output As #1
 
Sorry, I couldn't understand. if you don't mind could you send me the full code again?????:)

Thank you very much for your response.

Vipin
 
Code: 
OptionExplicit

Sub Copy_notepad()
    Range("A1:A10").Copy
    Shell "notepad.exe", 3
    SendKeys "^V"
    Application.CutCopyMode = False
EndSub
 
Try this too..

Code: 
Option Explicit

Sub txt_export2()
Dim buf As String, col As Long, r As Long, ws As Worksheet
Dim d As String, rng As Range, c As Range, row As Long

Set ws = ActiveSheet
d = "|"

Open "C:\Users\Deepak\Desktop\Export.txt" For Output As #1 'txt file location
With ws
    col = .UsedRange.Columns.Count
    row = .UsedRange.Rows.Count
        For r = 1 To row
            Set rng = .Range(.Cells(r, 1), .Cells(r, col))
                For Each c In rng
                    buf = buf & c.Text & d
                Next
                buf = Left(buf, Len(buf) - Len(d))
            Print #1, buf
        buf = ""
        Next r
End With
Close #1
Set rng = Nothing
Set ws = Nothing
End Sub
 
Code: 
Option Explicit
 
Sub txt_export2()
Dim buf As String, col As Long, r As Long, ws As Worksheet
Dim d As String, rng As Range, c As Range, row As Long
 
Set ws = ActiveSheet
d = "|"
 
Open "C:\Users\vipin58425\Documents\Vipin\test.txt" For Output As #1 'txt file location
With ws
    col = .UsedRange.Columns.Count
    row = .UsedRange.Rows.Count
        For r = 1 To row
            Set rng = .Range(.Cells(r, 1), .Cells(r, col))
                For Each c In rng
                    buf = buf & c.Text & d
                Next
                buf = Left(buf, Len(buf) - Len(d))
            Print #1, buf
        buf = ""
        Next r
End With
Close #1
Set rng = Nothing
Set ws = Nothing
End Sub

Thank you deepak. But i am getting an error which shows "Run Time error '55': File already open". The only thing i changed in this code is the path, I don't know which file they meant, do you have any idea regarding this? Please check my code here..

Vipin
 
Code: 
Option Explicit
Sub txt_export2()
Dim buf As String, col As Long, r As Long, ws As Worksheet
Dim d As String, rng As Range, c As Range, row As Long
Dim fNum As Single
Set ws = ActiveSheet
d = "|"
fNum = FreeFile()
Open "C:\Users\vipin58425\Documents\Vipin\test.txt" For Output As #fNum 'txt file location
With ws
    col = .UsedRange.Columns.Count
    row = .UsedRange.Rows.Count
        For r = 1 To row
            Set rng = .Range(.Cells(r, 1), .Cells(r, col))
                For Each c In rng
                    buf = buf & c.Text & d
                Next
                buf = Left(buf, Len(buf) - Len(d))
            Print #fNum, buf
        buf = ""
        Next r
End With
Close #fNum
Set rng = Nothing
Set ws = Nothing
End Sub
 
You must log in or register to reply here.
Back
Top