Consulting

Results 1 to 2 of 2

Thread: Transfer Word specific form data to Excel worksheet

  1. #1

    Transfer Word specific form data to Excel worksheet

    Hello
    i have two documents, word and excel, with many double informations. What i need is to transfer only some specific form data from the Word document into specific cells into Excel document.

    I have searched a VBA macro but i have found only macros that copy ALL the data from the Word document. I need only some of them with a specific bookmark.

    Someone can help? thanks

    this is the code that i have found and it copy ALL the forms from the Word file, and it is not useful in my case because i need only some of them...

    Dim wdApp As New Word.Application
    Dim myDoc As Word.Document
    Dim CCtl As Word.ContentControl
    Dim myFolder As String, strFile As String
    Dim myWkSht As Worksheet, i As Long, j As Long
    myFolder = "C:\Users\Manuele\Desktop\lavoro\test"
    Application.ScreenUpdating = False
    If myFolder = "" Then Exit Sub
    Set myWkSht = ActiveSheet
    ActiveSheet.Cells.Clear
    Range("A2") = "cognome"
    Range("a2").Font.Bold = True
    i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row
    strFile = Dir(myFolder & "\*.docx", vbNormal)
    While strFile <> ??
      i = i + 1
      Set myDoc = wdApp.Documents.Open(FileName:=myFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
      With myDoc
        j = 0
        For Each CCtl In .ContentControls
          j = j + 1
          myWkSht.Cells(i, j) = CCtl.Range.Text
        Next
        myWkSht.Columns.AutoFit
      End With
      myDoc.Close SaveChanges:=False
      strFile = Dir()
    Wend
    wdApp.Quit
    Set myDoc = Nothing: Set wdApp = Nothing: Set myWkSht = Nothing
    Application.ScreenUpdating = True
    End Sub
    Last edited by macropod; 03-24-2020 at 03:22 PM. Reason: Applied code formatting

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,027
    Location
    The obvious solution is to insert a control structure in your:
    For Each CCtl In .ContentControls
      …
    Next
    loop to determine which content control data to extract. Since you haven't told us anything about how those content controls might be identified (e.g. by title, tag, type, bookmark name(s) & range(s) in relation to the content controls, location in a table, etc.), it's impossible to be more specific.PS: When posting code, please post properly-formatted code so we can see its structure.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •