Importing: WORD to EXCEL

  • hi there,


    I was wondering if anyone could help my with my problem.


    I need to take / transfer particular values from WORD to EXCEL for ex.


    Apple $2


    where the $2 is transferred to EXCEL worksheet cell


    Can this be done?
    Is it using VB?
    If it is, should it use the Visual Basic Editor in MS WORD or I should have VB6.0 software to do this?
    Can anyone please tell me how to do this, and the code if using VB and anyone has it


    Thank You,


    Dennis

  • Hi there,


    Thanks for your reply


    Well, I didn't have the word document right now, but I will post it soon


    But can you please tell me how to do this to give me a rough picture of how to do it?
    Is this complicated?


    If possible could you give me a word document example an the code so that I get a picture about it?


    And is it written in VB? If it is, where do you write the VB coding?


    Thank you so much

  • Hi Dennis,


    Below You find the code for it:


    Option Explicit


    Sub Import_Word_Value()
    'We must set a reference to the MS Word x.x Library
    'via the Tools | Reference... in the VB-editor.
    Dim wbBok As Workbook
    Dim wsBlad As Worksheet
    Dim rnRapport As Range
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim BMRange As Word.Range


    Set wbBok = ThisWorkbook
    Set wsBlad = wbBok.Worksheets("Blad1")



    With wsBlad
    Set rnRapport = .Range("E9")
    End With


    Application.ScreenUpdating = False


    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & "\WordDennis.doc")
    'The bookmark cover the whole value and not just its position.
    Set BMRange = ActiveDocument.Bookmarks("Dennis").Range


    With wdDoc
    With BMRange
    .Select
    .Copy
    End With
    End With


    rnRapport.PasteSpecial (xlPasteValues)


    Application.CutCopyMode = False

    With wdApp
    .ActiveDocument.Close
    .Quit
    End With


    Set wdDoc = Nothing
    Set wdApp = Nothing


    MsgBox "The value is updated.", vbInformation


    End Sub


    Here is the Word-document and make sure You save it in the same folder as the XL-workbook:

  • hi,


    I tried to click on the Update button
    but then it said "Can't execute code in break mode"


    Then when I tried in the VB Editor, it said that "Compile Error: Can't find project or library" where it highlights this line of the code "wdApp As Word.Application"


    I have saved both files in the same dir


    Once again thank you so much

  • Hi again,


    Ok, please do following:


    Change to the VB-Editor via the command ALt-F11.
    Locate the Project in the Project-window and select it.
    Choose the command Tools | Reference...
    Check the Microsoft Word 9.0 Object Library.
    If You have an item with the name "ISMISSING" then unchecked.


    Save and close the VB-editor


    Now try to run it again by clicking on the button.


    Mail back with the outcome.


    I will visit OzGrid later this day (after 1800 my local time)


    Kind regards,
    Dennis

  • hi again,


    I've tried your tips but now a new error came up which says "user-defined type not defined"


    The problem is highlighted in:
    Dim wdApp As Word.Application
    and below it which has to do with word


    Sorry to cause this trouble and thx for your time and attention


    Thx

  • hi there,


    I found out that I didn't check the microsoft word 9.0 library which is required.


    Which of the syntax in the code takes out the 1400?
    what if there's a lines and lines of numbers


    I'm trying to trace your code, but it seems I don't quite understand


    Do you mind explaining again the syntax to take out a particular value(s)?


    Thank you so much, you have helped a lot


    Regards

  • Hi Dennis,


    One step furhter to a workable solution :)


    Quote


    Which of the syntax in the code takes out the 1400?
    what if there's a lines and lines of numbers



    This copy the value which the bookmark cover:


    With BMRange
    .Select
    .Copy
    End With


    Open the word-document and choose bookmark and go to the bookmark "Dennis". There You will see that the bookmark cover the whole value, i e 1400.


    In order to adjust it with respect to more values on several lines we need to have specific data concerning the case.

  • Hi again,


    Thank you so much dennis, u have helped a lot


    Do I have to bookmark every value needs to be taken?
    like
    Apple : $1
    Orange: $2
    So that I will be having 2 bookmarks named Apple & Orange.


    Is that true?


    Do you happen to know why sometimes excel files can be sooooo big like 26 MB
    While I looked at other excel file with more contents can be only 2MB?
    And how to save excel file in MS EXCEL TEMPLATE instead of MS EXCEL WORKSHEET?


    Regards

  • Hi Dennis,


    Sorry for late reply - it seems that I have missed Your latest reply...


    Here we go:


    Q1: Several values
    Unless they are just after each other then You need to use several names.


    Q2: Increase filesize
    Make a search here at OzGrid site. Dave have a whole page dedicated for this issue. Unfortunately it´s not uncommon...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!