Pasting content from Excel to Notepad

K

kandanuru

Board Regular
Joined
Dec 27, 2011
Messages
98
Hi,

This is very important for me.Request you to go through the query.

On my desktop i will have 10 flat files saved. With file Names Notepad1,Notepad2..Notepad10

Content of the flat file is :

Notepad1:
<!DOCTYPE html>
<html>
<body>
<a href="xyz.com Registry solutions for .xyz .college and .now gTLD's">
This is a link</a>
</body>
</html>

Notepad2:


<!DOCTYPE html>
<html>
<body>
<a href="http://www.xsz.com">
This is a link</a>
</body>
</html>

.
.
.
Notepad10:


<!DOCTYPE html>
<html>
<body>
<a href="http://www.xqz.com">
This is a link</a>
</body>
</html>


I shall have an excel file with various links in A(A1:A10) column.

http://www.xyz.com
Welcome
???????,??????????????
http://www.xyt.com
.
.
.
.
.
http://www.xqz.com


Now do i have any possibility to create a macro which opens the Notepad1 and paste the excel file A1 content in the required location in Notepad save and close it.

Excel file A1 content in Notepad1 link location
Excel file A2 content in Notepad2 link location
Excel file A3 content in Notepad3 link location
.
.
.
.
.
Excel file A10 content in Notepad10 link location
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Hi Kandanuru,

Stop posting one thread multiple times. It will reduce your chances of getting help, this time as well as next time.

Here's code for reading from Text files. Change blue part to suit your path requirements:
Rich (BB code): 
Public Sub ReadfromNotepad()
Dim varFile As Variant
Dim iFF As Integer, iRow As Integer
Dim sLine As String

iRow = 1 'Excel Sheet start row
varFile = Dir("C:\Temp\Notepad*.txt", vbNormal) 'Collect only files with name as notepad*.txt

Do While varFile <> "" 'Loop through the files
    iFF = FreeFile
    Open "C:\Temp\" & varFile For Input As #iFF 'Read file
    Do While Not EOF(iFF)
        Line Input #iFF, sLine
        Range("A" & iRow).Value = sLine
        iRow = iRow + 1
    Loop
    Close #iFF
    varFile = Dir
Loop

End Sub
 
Upvote 0
Hi Vallabha,

Thank you very much for the reply..
Code is not working out for scenario.
Request you to go through the scenario below.

Hi,
This is very important for me.Request you to go through the query.
On my desktop i will have 10 flat files saved. With file Names Notepad1,Notepad2..Notepad10
Content of the flat file is :
Notepad1:
{
"employees": [
{ "Name":"Mr." , "FirstName":"Doe" },

Notepad2:
{
"employees": [
{ "Name":"Mr." , "FirstName":"Duncan" },

.
.
.
Notepad10:
{
"employees": [
{ "Name":"Mr." , "FirstName":"Justin" },
I shall have an excel file with names in A(A1:A10) column.
Doe
Duncan
.
.
.
.
.
Justin

Now do i have any possibility to create a macro which opens the Notepad1 and paste the excel file A1 content in the required location in Notepad save and close it.
Excel file A1 content in Notepad1 Firstname location
Excel file A2 content in Notepad2 Firstname location
Excel file A3 content in Notepad3 Firstname location
.
.
.
.
.
Excel file A10 content in Notepad10 Firstname location​
Regards,
Kandy
 
Upvote 0
Hi,

In what sense does the code not work?

Does it give error? If it gives error, then you will have to specify where it fails.
 
Upvote 0
Hi Vallabha,

Exactly wat is happing is Notepads content is getting copied in excel file.

My scenarios is like Excel cell values from A1:A10 needs to be pasted in the specific area in the Notepad.

For example:

A1 contains value Deo

Notepad1 needs to be updated with value as Deo in the First name.
employees": [
{ "Name":"Mr." , "FirstName":"Deo" },

A2 contains value Duncan
Notepad2 needs to be updated with value as Duncan in the First name.
employees": [
{ "Name":"Mr." , "FirstName":"Duncan" },

I am new to VBA..so i may have not given all the clear info.

Please suggest the code for the scenario.

Regards,
Kandy
 
Upvote 0
Oh. So you want to bulk replace contents of Text files. Make sure you replace blue part with your path.

Rich (BB code): 
Public Sub ReplaceText()
Dim objFSO As Object
Dim objFile As Object
Dim strTxt As String, strNewTxt As String
Const ForReading = 1, ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")

For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
    On Error Resume Next
    Set objFile = objFSO.OpenTextFile("C:\Temp\Notepad" & i & ".txt", ForReading)
    
    strTxt = objFile.ReadAll
    objFile.Close
    strNewTxt = Replace(strTxt, """FirstName"":", """FirstName"":" & Range("A" & i).Value)
    
    Set objFile = objFSO.OpenTextFile("C:\Temp\Notepad" & i & ".txt", ForWriting)
    objFile.WriteLine strNewTxt
    objFile.Close

    If Err.Number <> 0 Then
        Range("B" & i).Value = "Error processing!"
        Err.Clear
    End If
Next i

Set objFSO = Nothing
Set objFile = Nothing
End Sub
 
Upvote 0
Hi vallabha

Thank you so much.

One small concern here..

When i am running macro by giving A1 value as XYZABC the Notepad1 is getting updated as FirstName":XYZABC"Deo"

Can you please look into this issue.
Guide me the solution.

Regards,
Rakesh
 
Upvote 0
kandanuru said:
Hi vallabha

Thank you so much.

One small concern here..

When i am running macro by giving A1 value as XYZABC the Notepad1 is getting updated as FirstName":XYZABC"Deo"

Can you please look into this issue.
Guide me the solution.

Regards,
Rakesh
Click to expand...
Rakesh,

Are you running the macro for second time on the same set of files?
 
Upvote 0
Hi vallabha

I am running macro only once XYZABC needs to be repalced with DEO.
Can you please resolve this issue.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Copy text from Internet Explorer Webpage into Excel using VBA
Replies
1
Views
557
samcd87
S
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
J
  • Question
Copy column data from worksheet to .htm file (notepad) using VBA
2
Replies
14
Views
1K
johnnywinto
J
P
  • Solved
How can I prevent Excel from Adding extra quote marks when I paste information out of excel
Replies
5
Views
7K
psycoperl
P
A
  • Question
Web Scraping using VBA
Replies
4
Views
9K
atr140
A

Forum statistics

Threads
1,216,014
Messages
6,128,273
Members
449,436
Latest member
blaineSpartan

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top