Copy data in a range into a new blank notepad

[SteynBS]

Good day guys,

I need to copy and paste data from a range into a new blank notepad. I can get the notepad open and copy the data, but for some reason, it does not paste the data in the notepad. Any advice, please?

The range is in sheet "listing" from Columns A and B the range has to be dynamic as the range changes. I have to copy from Column A to C to the end of the last row based in Column A. The code I have sofar is:

Sub Open_NotePad()
Dim myApp As String

myApp = Shell("Notepad", vbNormalFocus)


With Sheets("Listing").Select
Range("A1:C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End With

SendKeys "^V"

SendKeys "^{HOME}"

Application.Goto Worksheets("SAP Listing").Range("A1")
End Sub

 

[Joe4]

Does this do what you want?

Sub Open_NotePad()

Dim myApp As String
Dim lr As Long

myApp = Shell("Notepad", vbNormalFocus)

With Sheets("Listing")
'   Find last row with data in column A
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
'   Copy range
    .Range("A1:C" & lr).Copy
End With

SendKeys "^V"

SendKeys "^{HOME}"

Application.Goto Worksheets("SAP Listing").Range("A1")

End Sub

Also note that NotePad is just a program to use to open/view text files. I hate SendKeys and would probably not tackle this problem this way. I would simply copy the range I need to a new sheet, and save that sheet as a text file (in my desired text format).

 

[SteynBS]

Does this do what you want?

Sub Open_NotePad()

Dim myApp As String
Dim lr As Long

myApp = Shell("Notepad", vbNormalFocus)

With Sheets("Listing")
'   Find last row with data in column A
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
'   Copy range
    .Range("A1:C" & lr).Copy
End With

SendKeys "^V"

SendKeys "^{HOME}"

Application.Goto Worksheets("SAP Listing").Range("A1")

End Sub

Also note that NotePad is just a program to use to open/view text files. I hate SendKeys and would probably not tackle this problem this way. I would simply copy the range I need to a new sheet, and save that sheet as a text file (in my desired text format).

It copies the data to the clipboard and opens the notepad - but it does not paste the data in the notepad.

I got it to work yesterday as I was making the sheet, but this morning when I tried to use the sheet for the intended purpose it did not work.

The problem I have is. The ERP system we are using, when it comes to these uploads you can only upload with a notepad. I have not tested it on a text documents. I would really love for this to work though. if you have any ideas on how to get this to work?

 

[Joe4]

The problem I have is. The ERP system we are using, when it comes to these uploads you can only upload with a notepad. I have not tested it on a text documents. I would really love for this to work though. if you have any ideas on how to get this to work?

I think you may be a bit confused. There is really no such thing as a "NotePad" file - NotePad is just a program used to read/create text files.
By creating the text file by saving from Excel as a Text file, you can create the exact same file.
So rather than try to use Excel VBA to control another program, it is easier to to simply save the data from Excel is the desired text file format.

Just note that there are various text file formats, such as space-delimited (fixed width), tab-delimited, comma separated, etc.
As long as we know what the resulting text file needs to look like, we can program Excel to do that without much trouble.

 

[SteynBS]

I think you may be a bit confused. There is really no such thing as a "NotePad" file - NotePad is just a program used to read/create text files.
By creating the text file by saving from Excel as a Text file, you can create the exact same file.
So rather than try to use Excel VBA to control another program, it is easier to to simply save the data from Excel is the desired text file format.

Just note that there are various text file formats, such as space-delimited (fixed width), tab-delimited, comma separated, etc.
As long as we know what the resulting text file needs to look like, we can program Excel to do that without much trouble.

I think you might know more than I do, but in the end of the day. i need the data in a notepad to be uploaded. Not in any excel file or extention of it. Just as you are able to import data from a notepad, I believe data can be written or pasted in a notepad.

Apologies if I might seem confused or clueless, but as I mentioned. I need data in my excel sheet copied and pasted into a notepad for upload purposed.

 

[Joe4]

I think you might know more than I do, but in the end of the day. i need the data in a notepad to be uploaded. Not in any excel file or extention of it. Just as you are able to import data from a notepad, I believe data can be written or pasted in a notepad.

That is just the thing - you actually are not importing the data from NotePad, as NotePad is NOT a file type. It is simply as tool (like a hammer or saw) used to view various Text files.
If you show us a screen print of what the data looks like in Excel, and then a screen print of what the data looks like in NotePad, I am sure we can come up with the VBA code you need to create the file in the manner you need. Just be sure to remove any sensitive data first (replace it with "dummy data", if necessary).

 

[SteynBS]

That is just the thing - you actually are not importing the data from NotePad, as NotePad is NOT a file type. It is simply as tool (like a hammer or saw) used to view various Text files.
If you show us a screen print of what the data looks like in Excel, and then a screen print of what the data looks like in NotePad, I am sure we can come up with the VBA code you need to create the file in the manner you need. Just be sure to remove any sensitive data first (replace it with "dummy data", if necessary).

Joe,

Thank you for your feedback, I understand that a Notepad is not a file type as you explain and only a tool used to view text files. I have attached screenshots below. Thank you for your assistance and guidance.

From Excel to Notepad

 

[Joe4]

OK, here is VBA code which should create the text file directly.
Just change the path you want to save it to, and file name, if you desire.

Sub MyCreateTextFile()

    Dim lr As Long
    Dim wb As Workbook
    Dim pth As String
    
'   ***ENTER PATH TO SAVE TEXT FILE TO HERE***
    pth = "C:\Temp\"
    
'   Go to "Listing" Sheet
    Sheets("Listing").Activate
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Copy columns A-C
    Range("A1:C" & lr).Copy
    
'   Insert new workbook
    Set wb = Workbooks.Add
    
'   Paste copied data
    Range("A1").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
'   Save as text file
    ActiveWorkbook.SaveAs Filename:=pth & "Listing.txt" _
        , FileFormat:=xlText, CreateBackup:=False
        
'   Close text file
    wb.Close
    
End Sub

 

[SteynBS]

OK, here is VBA code which should create the text file directly.
Just change the path you want to save it to, and file name, if you desire.

Sub MyCreateTextFile()

    Dim lr As Long
    Dim wb As Workbook
    Dim pth As String
   
'   ***ENTER PATH TO SAVE TEXT FILE TO HERE***
    pth = "C:\Temp\"
   
'   Go to "Listing" Sheet
    Sheets("Listing").Activate
   
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Copy columns A-C
    Range("A1:C" & lr).Copy
   
'   Insert new workbook
    Set wb = Workbooks.Add
   
'   Paste copied data
    Range("A1").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
'   Save as text file
    ActiveWorkbook.SaveAs Filename:=pth & "Listing.txt" _
        , FileFormat:=xlText, CreateBackup:=False
       
'   Close text file
    wb.Close
   
End Sub

It works perfectly, thank you. I see what you are getting at. Is there any way to open the save as prompt and not save the new workbook to a default location? The location will change every time we work on different sites. My only concern with this method is, the other guys who will use this, will use the wrong file extension to save the active sheet. I am just asking, unsure if this can be done, but I would like this to be as foolproof as possible. Is there a way to open the save as prompt, but pre-select the file type .txt

Thit is part of the reason I wanted the Notepad opened (and my lack of knowledge), so from there, they can just save it to the location with whatever naming convention works for them without the risk of saving it with the wrong file extension.

I hope it makes sense what I am saying, but again. Thank you very much for your guidance and assistance thus far.

 

[Joe4]

Try this version:

Sub MyCreateTextFile()

    Dim lr As Long
    Dim wb As Workbook
    Dim fName As String
    Dim fileSaveName As String
    
'   Capture active file path and build default file path and name
    fName = ActiveWorkbook.Path & "\Listing.txt"
    
'   Go to "Listing" Sheet
    Sheets("Listing").Activate
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Copy columns A-C
    Range("A1:C" & lr).Copy
    
'   Insert new workbook
    Set wb = Workbooks.Add
    
'   Paste copied data
    Range("A1").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
'   Prompt for file save name
    fileSaveName = Application.GetSaveAsFilename(fName, _
        fileFilter:="Text (Tab delimited)(*.txt), *.txt")
    
'   Save as text file
    If Right(fileSaveName, 4) = ".txt" Then
        ActiveWorkbook.SaveAs Filename:=fileSaveName _
            , FileFormat:=xlText, CreateBackup:=False
    Else
        MsgBox "You have not chosen a valid file name ending in .txt", vbOKOnly, "File Name Error!"
    End If
        
'   Close text file
    wb.Close
    
End Sub