1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

import from notepad to excel

Discussion in 'Business Applications' started by arrrgh2003, Mar 28, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. arrrgh2003

    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
     
  2. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    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?
     
  3. arrrgh2003

    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
     
  4. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    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.
     
  5. arrrgh2003

    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:
     
  6. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    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.
     
  7. arrrgh2003

    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
     
  8. slurpee55

    slurpee55

    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:
     
  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    As for the rest of your request, well, I have no idea how to do that yet.... ;)
     
  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    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
     
  11. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,175
    Is there a space separator between each of the items? You could use find/replace to replace the space with 2 hard returns instead.
     
  12. slurpee55

    slurpee55

    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.
     

    Attached Files:

  13. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/698003