Required a VBA code

[RAJESH1960]

I have a huge data in my excel sheet. I have selected and copied the range which have N number of rows and columns. I want to paste the data to notepad. Here, I need to insert a code to get the clipboard data with a magic code for is for early binding to MSForms Data Object and write the data to temp file (true to overwrite existing temp file. I have no idea of writing code or to create object, scripting or file system object. So please help me to write this code.

 

[Yongle]

Why are you pasting to notepad?
A text file can be written directly from Excel

See if code below achieves what you want
- amend the file path and name of file "C:\Test\folder\subfolder\NameOfTextFile.txt"

To test
- run the code
- select a range when asked to do so
- open the created file with Notepad

Sub VBA_Print_to_a_text_file()
    Dim rng As Range, cel As Range, txt As String, c As Long, file_Path As String
    file_Path = "C:\Test\folder\subfolder\NameOfTextFile.txt"
    Set rng = Application.InputBox("Select range to save to text file and click OK", , , , , , , 8)
    Open file_Path For Output As #1
        For Each cel In rng.Resize(, 1)
            txt = cel.Value
            For c = 1 To rng.Columns.Count - 1
                txt = txt & vbTab & cel.Offset(, c)
            Next
            Print #1, txt
            txt = ""
        Next
    Close #1
End Sub

 

[RAJESH1960]

Why are you pasting to notepad?
A text file can be written directly from Excel

See if code below achieves what you want
- amend the file path and name of file "C:\Test\folder\subfolder\NameOfTextFile.txt"

To test
- run the code
- select a range when asked to do so
- open the created file with Notepad

Sub VBA_Print_to_a_text_file()
    Dim rng As Range, cel As Range, txt As String, c As Long, file_Path As String
    file_Path = "C:\Test\folder\subfolder\NameOfTextFile.txt"
    Set rng = Application.InputBox("Select range to save to text file and click OK", , , , , , , 8)
    Open file_Path For Output As #1
        For Each cel In rng.Resize(, 1)
            txt = cel.Value
            For c = 1 To rng.Columns.Count - 1
                txt = txt & vbTab & cel.Offset(, c)
            Next
            Print #1, txt
            txt = ""
        Next
    Close #1
End Sub

Thanks for the reply Yongle. I need to import the same to Tally. Tally accepts xml format for import and hence I need to write the data to Notepad.
In your code ("C:\Test\folder\subfolder\NameOfTextFile.txt") what if I change the txt to xml. Will it work or should I change any other line in the code connected to text.?

 

[Yongle]

You said you wanted the data in Notepad and the code provided creates a text file that would be identical to cutting and pasting from Excel to Notepad
You last post has lost me - you are now talking about xml -which was not mentioned in your first post - I will leave you to it
Good luck

 

[RAJESH1960]

You said you wanted the data in Notepad and the code provided creates a text file that would be identical to cutting and pasting from Excel to Notepad
You last post has lost me - you are now talking about xml -which was not mentioned in your first post - I will leave you to it
Good luck

Sorry. I am not so good at words to explain. I have a code with me but it works sometimes and sometimes shows an error. So I wanted a code which would run without error. By looking at the code you may understand what I am trying to do.

' get the clipboard data
' magic code for is for early binding to MSForms.DataObject
With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipBoard
strData = .GetText
End With

' write to temp file
usr = Environ("username")
strTempFile = "C:\Users\" & usr & "\Desktop\RENAME Purchase.xml"
With CreateObject("Scripting.FileSystemObject")
' true to overwrite existing temp file
.CreateTextFile(strTempFile, True).Write strData
End With