VBA Code to Copy and Paste from Excel to Notepad File

[ststern45]

Hi,

I would like to know if it would be possible to copy a cell range J20 through J79 and then open a Notepad file named NP1 delete the existing data in NP1 then copy and paste the data from cell range J20 through J79 then save the Notepad file again as NP1.

I have a total of 60 Notepad Files NP1 through NP60.

Thanks,

Steve

 

[larrydunn]

Dear Steve:

I thought I understood before that NP1 - NP60 would be opened one after another in sequence as you marched to the right through the tabs. My second attempt put that assumption into action. If that's true, then you need not type in any of the file names.

If you must type in the file names, it would be simple enough to make the input numeric; i.e., you would type in 25, and file NP25 would be opened.

That makes the input so simple, that any data validation or any such would be unnecessary, or so it seems to me. I would hate having to pick simple file names like that from a list.

Please make it clear if you have to select each file name. If that's true, the problem changes some. Also, do the tab names remain constant? If they are, then a lookup table can tie the tab names to the NP file number. I mean that the code could compute the text file name/number from the list of tab names.

 

[ststern45]

Dear Steve:

I thought I understood before that NP1 - NP60 would be opened one after another in sequence as you marched to the right through the tabs. My second attempt put that assumption into action. If that's true, then you need not type in any of the file names.

If you must type in the file names, it would be simple enough to make the input numeric; i.e., you would type in 25, and file NP25 would be opened.

That makes the input so simple, that any data validation or any such would be unnecessary, or so it seems to me. I would hate having to pick simple file names like that from a list.

Please make it clear if you have to select each file name. If that's true, the problem changes some. Also, do the tab names remain constant? If they are, then a lookup table can tie the tab names to the NP file number. I mean that the code could compute the text file name/number from the list of tab names.

Larry,

Your code worked just as I wanted.

Thanks again,

Steve

 

[larrydunn]

Is it too late to ask what the Notepad files are for? For some reason, it never occurred to me to ask.

There might be more any easy extension available during the process of creating the text files, or there might be another way to look at the whole process.

If you're completely satisfied, maybe I should just shut up. But I don't know how!

 

[Norie]

Larry

By the way I don't really think there is such a thing as a 'Notepad' file.

Notepad is just one of many applications that you can use to view text files.

 

[ststern45]

Designate cell range

Hello,

Below is a Macro that will transfer a range of cells from my Excel worksheet to a Notepad .txt file (C:\PP3\Data\).

The current code will copy 100 lines (K20:K119) of a cell range. I would like to know if the it could be coded to ask me how many lines I want to copy to Notepad.
The data will always start at cell K20.

Thank you!!

Steve

Sub CopyToNotepad()

'Open NotePad file and insert text
Dim vInputName As Variant
Dim sFileName As String
Dim FN As Integer
Dim I As Integer
Dim sTextLine(100) As String
Dim sDefaultPath As String

sDefaultPath = "c:\PP3\Data\"

vInputName = InputBox("NotePad File Name: ",

"Get File Name", "")

If vInputName = "" Then Exit Sub
sFileName = UCase(vInputName)
If InStr(sFileName, ".") < 2 Then sFileName =

sFileName & ".txt" 'Supply extension
sFileName = sDefaultPath & sFileName 'Create

entire pathname

For I = 20 To 119
sTextLine(I - 19) = ActiveSheet.Range("K" &

I).Value
Next I

FN = FreeFile
'Open for output, thus clearing prior data
Open sFileName For Output As #FN

For I = 1 To 100
Print #FN, sTextLine(I)
Next I

Close #FN

End Sub

 

[ccuaron]

Ok, I give up. The code that ststern45 posted seems to be what I need but I cannot get it to work. All I need to do is open up an excel sheet (Delconchecker.xls), copy from A1 to A500 then open txt file in notepad (10dayback.txt) and paste the data. I'm new to this and learning, please be patient. I'm not sure where to put the Excel file name and where to put the notepad file name. Any assistance is appreciated. Thanks

Chris

 

[iask]

Re: Drop Down Box

I found this code that works almost perfect of what I need. can anyone make this code to copy only what is selected in a column or in a row then paste in notepad. it would be very much appreciate it.

thanks,

iask




Sub ExportToNotepad()

WriteRangeToTextFile Range("B1:B" & lastrow), "C:\Documents and Settings\Desktop\test.txt", vbTab
' WriteRangeToTextFile Range("F4:H26"), "H:\temp\file2.txt", vbTab

Shell "notepad.exe C:\Documents and Settings\Desktop\test.txt" ', vbMaximizedFocus
'Shell "notepad.exe h:\temp\file2.txt", vbMaximizedFocus

End Sub
Sub WriteRangeToTextFile(Source As Range, Path As String, Delimiter As String)
Dim oFSO As Object
Dim oFSTS As Object
Dim lngRow As Long, lngCol As Long

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFSTS = oFSO.CreateTextFile(Path, True)

For lngRow = 1 To Source.Rows.Count

For lngCol = 1 To Source.Columns.Count

If lngCol = Source.Columns.Count Then
oFSTS.Write Source.Cells(lngRow, lngCol).Text & vbCrLf
Else
oFSTS.Write Source.Cells(lngRow, lngCol).Text & Delimiter
End If

Next lngCol

Next lngRow

oFSTS.Close

Set oFSTS = Nothing
Set oFSO = Nothing

End Sub

 

[christianbmartinez]

Re: Drop Down Box

Hi I need to fix my code here. I want to copy and paste the cell from A:T, how can I change below code? Thank you so much all

Sub NotePad()
'Open NotePad file and insert text
Dim vInputName As Variant
Dim sFileName As String
Dim FN As Integer
Dim I As Integer, P As Integer
Dim sTextLine(2000) As String
Dim sDefaultPath As String


sDefaultPath = "c:\mydocs\miscellaneous"


vInputName = InputBox("NotePad File Name: [File Name]", "Get File Name", "NP1")


If vInputName = "" Then Exit Sub
sFileName = UCase(vInputName)
If InStr(sFileName, ".") < 2 Then sFileName = sFileName & ".txt" 'Supply extension
sFileName = sDefaultPath & sFileName 'Create entire pathname

For I = 2 To 2000
sTextLine(I - 1) = ActiveSheet.Range("A" & I).Value
Next I


FN = FreeFile
'Open for output, thus clearing prior data
Open sFileName For Output As #FN


For I = 1 To 2000
Print #FN, sTextLine(I)
Next I


Close #FN


End Sub

 

[Compiled]

Re: Drop Down Box

How would I be able to save my notepad file into a different file directory? Placing it directly into my C drive and it will be called Most recent reorder

its a .txt file obviously, but for some reason, once my vba code copies what is needed into the notepad, i can not get it to save.

Thanks

 

[Rolly_Sefu]

Steve

You could probably use VBA's file input/output methods to do this.

How exactly do you want the data to go in the file(s)?

The following will put the values from J20:J79 in a text file called NP1 seperated by commas.

Sub JtoText()
Dim FF
Dim arr

    arr = Range("J20:J79")
    
    arr = Application.WorksheetFunction.Transpose(arr)
    
    arr = Join(arr, ",")
    
    FF = FreeFile()
    Open "C:\NP1.txt" For Output As #FF 
        Print #FF , arr
    Close #FF 
End Sub

By the way is a different range for each file?

Hello,

Sorry for replying to such an old thread, but i really like Norie's code.
Is it possible to paste the range exactly like in excel ?
i tried to run the code without "arr = Application.WorksheetFunction.Transpose(arr)" and "arr = Join(arr, ",")" but i'm getting a run time error when i reach "Print #FF , arr"

Any suggestions ?

Thank you