Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

import from notepad to excel


(!)

arrrgh2003's Avatar
arrrgh2003 arrrgh2003 is offline
Member with 83 posts.
THREAD STARTER
 
Join Date: Dec 2003
28-Mar-2008, 02:56 PM #1
import from notepad to excel
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's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
28-Mar-2008, 02:58 PM #2
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's Avatar
arrrgh2003 arrrgh2003 is offline
Member with 83 posts.
THREAD STARTER
 
Join Date: Dec 2003
28-Mar-2008, 03:05 PM #3
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's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
28-Mar-2008, 03:10 PM #4
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's Avatar
arrrgh2003 arrrgh2003 is offline
Member with 83 posts.
THREAD STARTER
 
Join Date: Dec 2003
28-Mar-2008, 03:19 PM #5
can you explain a bit more please

no idea what paragraph markers are or how they work
jimr381's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
28-Mar-2008, 03:36 PM #6
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's Avatar
arrrgh2003 arrrgh2003 is offline
Member with 83 posts.
THREAD STARTER
 
Join Date: Dec 2003
28-Mar-2008, 11:24 PM #7
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
29-Mar-2008, 10:46 AM #8
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!
slurpee55's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
29-Mar-2008, 10:49 AM #9
As for the rest of your request, well, I have no idea how to do that yet....
Rollin_Again's Avatar
Member with 4,698 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
29-Mar-2008, 01:41 PM #10
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

Last edited by Rollin_Again; 29-Mar-2008 at 02:11 PM..
jimr381's Avatar
Computer Specs
Member with 4,175 posts.
 
Join Date: Jul 2007
Location: Vienna, VA
Experience: Computer Illiterate
31-Mar-2008, 09:56 AM #11
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's Avatar
Computer Specs
Member with 7,837 posts.
 
Join Date: Oct 2004
Location: Southwest Iowa....
Experience: Currently stupid...
31-Mar-2008, 10:33 AM #12
is this a one-time deal?
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.
Attached Thumbnails
import from notepad to excel-wordlist.jpg   import from notepad to excel-copy-paste-excel.jpg   import from notepad to excel-after-running-script.jpg  
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑