[arrrgh2003]

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]

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]

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]

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]

can you explain a bit more please

no idea what paragraph markers are or how they work

[jimr381]

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]

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

[slurpee55]

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.

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!

[slurpee55]

As for the rest of your request, well, I have no idea how to do that yet....

[Rollin_Again]

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.

 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.

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]

Is there a space separator between each of the items? You could use find/replace to replace the space with 2 hard returns instead.

[slurpee55]

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.