import from notepad to excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

arrrgh2003

Thread Starter
Joined
Dec 4, 2003
Messages
83
Is it possible to import from notepad into excel?

I want to take a list of about 100+ single words from note pad (1 on each row) then put them into an excel sheet.

Problem is that I want to split the words up and insert a translation of the word above each one and then box the pair. I can type the translation manually but it could turn into 100s so I want to find a quick way to do some of it.

It would look like a full page grid with 2 words in each box.

Any help much appreciated
 

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
What do you mean by split the words up? Do you mean that you will insert an extra row in-between to put the translation in?
 

arrrgh2003

Thread Starter
Joined
Dec 4, 2003
Messages
83
yes, sorry I wasn't clear. I want to insert a row above each word and possibly below as well for a phonetic translation but that will be later!

Thanks for the prompt response
 

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
I would open the file in Word first and replace the single paragraph marker with two paragraph markers instead. I then would copy and paste it over to excel and it should put it in the right place.

Automatically getting translations is beyond my scope though.
 

arrrgh2003

Thread Starter
Joined
Dec 4, 2003
Messages
83
can you explain a bit more please

no idea what paragraph markers are or how they work :eek:
 

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
After getting the information open up in MS Word. Do a CTRL+H on the keyboard to open up Find/Replace. Click on the "More" button at the bottom of the dialog box. Click within the "Find What" section and click on the "Special" button at the bottom of dialog box and click on "Paragraph Mark." Now insert 2 paragraph marks into the "Replace with:" field and click on the "Replace All" button.
 

arrrgh2003

Thread Starter
Joined
Dec 4, 2003
Messages
83
Hi,

Thanks for the info, handy to know in word.
However when I paste to excel it pastes a straight list with no extra lines (excel 2007 in compatibility mode)

any other ideas? I was thinking of a little VBA code to select all the list from notepad and paste to worksheet 'temp' then the code would select the first line cell A1 and paste it to A2 in worksheet 'table'
then it would go down the list and take each line and copy to the correct cell in 'table'. Then somehow it would stop at the end of the list and delete worksheet 'temp'

In my head it sounds simple but maybe thats just because I don't know much VBA
 
Joined
Oct 20, 2004
Messages
7,837
This will insert a blank after each row (I'm a beginning coder, kept getting errors trying to put it ahead) but all you would need to do otherwise it to insert a row at the top and you would be okay.
Code:
Sub Insertv2()
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).Select
Loop
End Sub
In the words of firefytr, HTH! :rolleyes:
 
Joined
Sep 4, 2003
Messages
4,916
Here is some VBA code to open a text file and read it line by line. You will need to change the path of the text file to open as well as changing the messagebox to your own actions such as pasting the values into cells of the workbook. Since I don't know what you workbook layout is like you would have to post a sample workbook and I'll help with the coding.

Code:
 Sub ReadText()

 Dim strTextLine As String
 Dim strFilename As String
 Dim vFileHandle As Integer

  strFilename = "C:\Test\Test.txt"  ' Change Text File Path

  If Dir(strFilename) = "" Then
  MsgBox ("File Not Found")
  End If

  vFileHandle = FreeFile

  Open strFilename For Input As vFileHandle

  Do While Not EOF(vFileHandle)
  Line Input vFileHandle, strTextLine
  If strTextLine <> "" Then
  MsgBox (strTextLine) ' Replace Message with your action.
  End If
  Loop

  Close vFileHandle

 End Sub
Here is another method using the File Scripting Object model. If you use this method you must make sure to set reference to Microsoft Scripting Runtime in the VBA editor under TOOLS --> REFERENCES.

Code:
Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Test.txt", ForReading)  'Change the Path to your text file

Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
If strLine<>"" then
msgbox(strLine)   ' Change this messagebox to your desired action such as pasting into cell
End If
Loop

objFile.Close


Regards,
Rollin
 

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
Is there a space separator between each of the items? You could use find/replace to replace the space with 2 hard returns instead.
 
Joined
Oct 20, 2004
Messages
7,837
IF you are just wanting to do this one or two times, my code should be enough - I made a list, copied and pasted it and ran the script in almost no time - less than 2 minutes from start to finish.
If this is something that you do repeatedly, then you may want to import - although going in and altering the path might take you longer than just copy and paste and run the code I posted earlier.
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top