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

 

[Norie]

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?

 

[larrydunn]

Dear Steve:

This code might work for you. You only need change the default path to match the location of your Notepad files.

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

sDefaultPath = "c:\mydocs\miscellaneous\"

vInputName = InputBox("NotePad File Name: [NP1 - NP60]", "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 = 20 To 79
sTextLine(I - 19) = ActiveSheet.Range("J" & I).Value
Next I

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

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

Close #FN

End Sub

 

[ststern45]

Copy Files

Thanks for you reply.

I have a Tab in Excel labeled CA3M and I want to copy and paste cell range J20 through J79 into a Notepad file named NP1.

Looks like this:

2006-10-14 AM 837
2006-10-13 AM 510
2006-10-12 AM 351
2006-10-11 AM 673
2006-10-10 AM 261
2006-10-09 AM 727
2006-10-08 AM 650
2006-10-07 AM 390
2006-10-06 AM 579
2006-10-05 AM 210
2006-10-04 AM 488
2006-10-03 AM 915
2006-10-02 AM 892
2006-10-01 AM 595
2006-09-30 AM 189
2006-09-29 AM 637
2006-09-28 AM 666
2006-09-27 AM 756
2006-09-26 AM 160
2006-09-25 AM 499
2006-09-24 AM 228
2006-09-23 AM 048
2006-09-22 AM 055
2006-09-21 AM 068
2006-09-20 AM 084
2006-09-19 AM 156
2006-09-18 AM 423
2006-09-17 AM 455
2006-09-16 AM 862
2006-09-15 AM 154
2006-09-14 AM 084
2006-09-13 AM 372
2006-09-12 AM 379
2006-09-11 AM 933
2006-09-10 AM 918
2006-09-09 AM 701
2006-09-08 AM 233
2006-09-07 AM 199
2006-09-06 AM 066
2006-09-05 AM 139
2006-09-04 AM 500
2006-09-03 AM 943
2006-09-02 AM 358
2006-09-01 AM 939
2006-08-31 AM 766
2006-08-30 AM 773
2006-08-29 AM 176
2006-08-28 AM 168
2006-08-27 AM 538
2006-08-26 AM 198
2006-08-25 AM 607
2006-08-24 AM 577
2006-08-23 AM 586
2006-08-22 AM 943
2006-08-21 AM 123
2006-08-20 AM 614
2006-08-19 AM 722
2006-08-18 AM 929
2006-08-17 AM 247
2006-08-16 AM 240

Before I copy and paste this list into the NP1 Notepad file I was wondering if it would be possible to delete what was already in the existing Notepad NP1 file then copy and paste the list above. So all I'm doing is replacing the exisiting data.

I have a total of 60 Excel tabs and 60 Notepad files that I would like to update each day if possible.

Thanks,

Steve

 

[Norie]

Steve

Both codes do basically what you want to do - create a text file using a range.

Mine will put all the values on one line, delimited by commas.

Larry's will put each value on a seperate line.

They will both create a brand new file.

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.

 

[larrydunn]

If Norie hasn't beat me to it, I think this code will accomplish what you want. It starts from a specified tab and progresses through each of the 60 tabs, opening the appropriate NP file for each.

As before, it puts data in separate rows, which I gather is what you had in mind.

Sub NotePadByTab()
'Open NotePad (text) file for each of 60 sheets and insert text , line by line
Dim vInputName As Variant
Dim sFileName As String
Dim FN As Integer
Dim I As Integer
Dim iRangeThruTabs As Integer
Dim sTextLine(60) As String
Dim sDefaultPath As String
Dim sFirstTabName As String

sDefaultPath = "c:\mydocs\miscellaneous\"
sFirstTabName = "Sheet1" 'Put start tab name here

Sheets(sFirstTabName).Select 'Always start at this sheet

For iRangeThruTabs = 1 To 60
sFileName = "NP" & iRangeThruTabs & ".txt" 'Supply extension
sFileName = sDefaultPath & sFileName 'Create entire pathname

For I = 20 To 79
sTextLine(I - 19) = ActiveSheet.Range("J" & I).Value
Next I

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

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

ActiveSheet.Next.Select 'Proceed to next tab

Next iRangeThruTabs

End Sub

 

[Norie]

Larry

That code looks good.

I'd take a slightly different approach with a couple of changes that might speed things up.

Here's my take on it.

Sub NotePadByTab()
Dim ws As Worksheet
Dim FF
Dim I As Long

    For Each ws In ThisWorkbook.Worksheets
        I = I + 1
        arr = ws.Range("J20:J79")
   
        arr = Application.WorksheetFunction.Transpose(arr)
   
        arr = Join(arr, vbCrLf)
        
        FF = FreeFile
        
        Open "c:\NP" & I & ".txt" For Output As #FF
            Print #FF, arr
        Close #FF
    Next ws

End Sub

 

[larrydunn]

Norie,

Questions:

1. How is 'arr' to be dimensioned?

2. Does Join(arr, vbCrLf) append CRLF to each row?
(Couldn't test this, because of Step 1)

3. What if not all sheets are to be included?

4. Whimper, whimper.

I admire the consiceness of your code, but lament
that so much of it is new to me!

5. Forgot to mention Transpose. Guess that will become clear in testing.

 

[Norie]

larry

1 My bad for not dimensioning arr. If it was to be dimensioned then it would be as Variant, or you could just leave out the data type and it would default to that anyway.

2 That's right. Join takes a single dimensional array and creates a string from it that seperates each item by the specified delimiter. eg vbCrLf

Note: Join is only available in later versions, 2000 onwards if I recall

3 There's many way we could deal with that, most of which would probably be dependent on the OP's requirements. For example a simple IF might work, or perjaps a Select Case structure.

5 The Transpose converts arr to a 1-dimensional array. This creates a 2-dimensional array.

arr = ws.Range("J20:J79")

 

[ststern45]

Drop Down Box

Dear Steve:

This code might work for you. You only need change the default path to match the location of your Notepad files.

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

sDefaultPath = "c:\mydocs\miscellaneous\"

vInputName = InputBox("NotePad File Name: [NP1 - NP60]", "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 = 20 To 79
sTextLine(I - 19) = ActiveSheet.Range("J" & I).Value
Next I

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

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

Close #FN

End Sub

Thank you everyone for your help.

Would it be possible to create a "Drop-Down" box where I can select the file in place of typing in the filename??

Thanks again,

Steve