0

I need a VBA code to update my word file. It which consists of some tables That has to be updated from excel file. Excel file consists of bearing data with different bearing numbers. And my report has to be updated with the bearing values. Like for my next report if I just enter the different bearing file it must read all the bearing data from that file.

This has to be done in 3 steps. I have attached a sample image. firstly identify the bearing name which is always in A column (In this case I need to find (248_R), 38,7 % ). Then select 6*6 matrix data (suppose I find the bearing data to be in A946 then I need to record data from B950 to G955) and then transfer to word file(Only the values to the table). I am a newbee in VBA coding please can someone help?

image of sample bearing name with matrix underneath

Image of what the tables look like in the word document: image of tables in word doc

Community
  • 1
  • 1
Pratheek P Manangi
  • 341
  • 1
  • 3
  • 11

1 Answers1

0

The first part of copying the range you want is relatively easy. You can use the following code to copy your desired matrix. I am not sure about pasting to a word document yet, give me some more time on that. (For now, if you run this macro, the range you want will be copied. You can then switch to your word document and hit Ctrl+V to paste it into the desired table.

Also, please check and see whether the following references have been added: enter image description here

Option Explicit

Sub findBearingDataAndPasteToWord()
    Dim i As Integer
    Dim aCell As Range, rng As Range
    Dim SearchString As String

    Set rng = Range("A750:A1790")
    SearchString = "(248_R), 38,7 %"

    For Each aCell In rng
        If InStr(1, aCell.Value, SearchString, vbTextCompare) Then
            ActiveSheet.Range(Cells(aCell.row + 4, 1), Cells(aCell.row + 9, 6)).Copy

            Dim wrdApp As Word.Application
            Dim docWd As Word.Document

            MsgBox "Please select the word document that you want to paste the copied table data into (after pressing OK)" & _
                vbNewLine & vbNewLine & "Script written by takanuva15 with help from Stack Overflow"
            docFilename = Application.GetOpenFilename()
            If docFilename = "False" Then Exit Sub
            Set docWd = getDocument(docFilename)
            Set wrdApp = docWd.Application

            wrdApp.Selection.EndKey Unit:=wdStory
            wrdApp.Selection.TypeParagraph
            wrdApp.Selection.TypeParagraph
            wrdApp.Selection.PasteExcelTable False, True, False

            Exit Sub
        Else: End If
    Next aCell
End Sub

'Returns the document with the given filename
'If the document is already open, then it returns that document
Public Function getDocument(ByVal fullName As String) As Word.Document
    On Error Resume Next
    Set wrdApp = GetObject(, "Word.Application")
    If wrdApp Is Nothing Then Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True

    Dim fileName As String
    Dim docReturn As Word.Document

    fileName = Dir(fullName)
    Set docReturn = Word.Documents(fileName)
    If docReturn Is Nothing Then
        Set docReturn = Word.Documents.Open(fullName)
    End If
    On Error GoTo 0
    Set getDocument = docReturn
End Function
takanuva15
  • 1,286
  • 1
  • 15
  • 27
  • Thanks a lot for your reply. I needed to use ifstr as there might be possibility for the previous part to have changed. Socould you please extend frm here Option Explicit Sub findBearing() Dim i As Integer Dim aCell As Range, Rng As Range Dim SearchString As String Set Rng = Range("A750:A1790") SearchString = "(248_R), 38,7 %" For Each aCell In Rng If InStr(1, aCell.Value, SearchString, vbTextCompare) Then ActiveSheet.Range(Cells(aCell.Address, 1), Cells(aCell.Address, 6)).Copy End If Next aCell End Sub – Pratheek P Manangi Jul 13 '17 at 14:00
  • Thanks, can you please put that code under your original question in your post? – takanuva15 Jul 13 '17 at 14:11
  • I've changed the code. Now it will paste the copied matrix into the open Word document you have. – takanuva15 Jul 13 '17 at 16:35
  • Thanks a lot @takanuva15 but I am getting a compiler error "User-defined type not defined". at the location Dim appWd As Word.Application. Also I need to paste it in a particular table in the word file. Could you please even think in that direction? You are being very kind in helping me out. – Pratheek P Manangi Jul 14 '17 at 07:45
  • You should be able to remove the error by adding the Word Object reference library to your workbook. Follow the instructions [here](http://excel-macro.tutorialhorizon.com/vba-excel-reference-libraries-in-excel-workbook/) and check the box that says "Microsoft Word 16.0 Object Library". I will work on pasting it into a table. Can you put a screenshot in your post of how the tables in the word document look like? – takanuva15 Jul 14 '17 at 13:26
  • I have pasted the screenshot of it. I have also tried to check the option u told but its still showing the same error. That was already selected. – Pratheek P Manangi Jul 16 '17 at 21:30
  • I am getting a compiler error Sub or Function not defined at the line set docWd = getDocument(docFilename) could you solve that? @takanuva15 – Pratheek P Manangi Jul 17 '17 at 08:29
  • Are you sure you checked the right library? [This SO post](https://stackoverflow.com/questions/24261557/user-defined-type-not-defined-error) says that is the way to fix the error. Your screenshot was rejected by SO reviewers, so I have moved it to your original question for others to see. The "function not defined" compiler error can be fixed by copy-pasting the second function 'getDocument' within my post into your vba editor. Can you tell me if the titles above the Word tables are within the table itself as a single merged cell, or are they typed and centered above the actual table? – takanuva15 Jul 17 '17 at 13:37
  • Yeah I did check it out. I am getting Variable not found error. Can i contact u by other means? Like whatsapp or Facebook? @takanuva15 – Pratheek P Manangi Jul 17 '17 at 14:58
  • I'm sorry, but I can't give personal information over SO. There is a chat room that can be connected to this question, but I don't have the privilege to open one. No worries though, we will work it out. Hopefully a moderator can open a chat room for us. Can you tell me which line is showing the "Variable not found" error or put up a screenshot? – takanuva15 Jul 18 '17 at 14:10
  • could you please follow the thread of this question. I have solved a bit further and ended up with this code. https://stackoverflow.com/questions/45275980/how-to-insert-data-copied-from-an-excel-sheet-to-the-table-existing-in-the-word – Pratheek P Manangi Jul 24 '17 at 12:31
  • Dang man, that code changed a ton! It'll take me a couple days to look through it. Thanks for the heads-up; I thought you forgot about this question lol – takanuva15 Jul 25 '17 at 12:58
  • Looks like you figured out the answer to your question [here](https://stackoverflow.com/questions/45280643/how-to-update-table-in-word). Congrats! – takanuva15 Jul 31 '17 at 14:30