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

How to copy and transfer raw data from xlsm to notepad

kopsy

New Member
Hi
I wanted to copy and transfer the raw data (along with spaces) from a worksheet to a notepad automatically every 5 minutes by replacing the existing data in the same notepad.

i have many Worksheets in the Workbook, in which i wanted to export data from a particular Worksheet called "SPOT", Range from A2:Y90.
Notepad file saved in the location C:\ drive in the name of 'collection' in .txt format , C:\collection.txt .

I have googled and tried various methods but has none matched my criteria yet. Hoping for a solution here.

Thanks, awaiting for your assistance. Thank you.
 
Hi !​
tried various methods but has none matched my criteria yet.
So explain and show at least what you have tried as any method should work !​
Without forgetting an attachment for both source & destination files, as per rules of this forum …​
 
Hi Marc!

Here is the code from google, which i little modified and tried for making the above requirement possible, but unfortunately it does only partial work, not exactly what i wanted.

Code:
Option Explicit

Public Sub XLRangeToNotepad()

'Dim iPtr As Integer
  Dim sFileName As String
  Dim intFH As Integer
  Dim oCell As Range

  Dim RunTimer3 As Date

  Application.ScreenUpdating = False
  RunTimer3 = Now + TimeValue("00:05:10")
  Application.OnTime RunTimer3, "XLRangeToNotepad"

  sFileName = "C:\collection.txt"

  Close
  intFH = FreeFile()
  Open sFileName For Output As intFH

  For Each oCell In Worksheets("SPOT").Range("A1:Y90")
    Print #intFH, oCell.Value
  Next oCell

  Close intFH

  'MsgBox "TransferDone!"

  Application.ScreenUpdating = True
End Sub

This code is working, but what it does is, it is copying and pasting everything in a transposed manner like changing all native rows to a single column in Notepad.

Is it not possible to copy and paste the data in raw native format in Notepad as how it displays with tab spaces when we copy and paste it manually!?
 
Modify the for loop as below and see if it helps.

Code:
  Dim strOut As String
  For Each oCell In Worksheets("SPOT").Range("A1:A90")
      strOut = Join(Application.Transpose(Application.Transpose(oCell.Resize(1, 25).Value)), vbTab)
    Print #intFH, strOut
  Next oCell
 
Hi shirivallabha,
Thanks for responding, I'm so delighted, your code works perfectly for me. I'm so happy.
Thanks a lot, you have saved lot of my time. :):)
 
Hi shirivallabha,
Thanks for responding, I'm so delighted, your code works perfectly for me. I'm so happy.
Thanks a lot, you have saved lot of my time. :):)
Thanks for the feedback!

Please also take note of cross-posting and general rules of posting online.
 
Back
Top