Consulting

Results 1 to 9 of 9

Thread: EXCEL to Word vb CODE

  1. #1
    VBAX Newbie
    Joined
    Nov 2017
    Posts
    1
    Location

    EXCEL to Word vb CODE

    Can anyone help with the following code:
    Sub GetFormData()
    'Note: this code requires a reference to the Word object model
    'To do this, go to Tools|References in the VBE, then scroll down to the Microsoft Word entry and check it.
    Application.ScreenUpdating = False
    Dim wdApp As New Word.Application, wdDoc As Word.Document, CCtrl As Word.ContentControl
    Dim strFolder As String, strFile As String, WkSht As Worksheet, i As Long, j As Long
    strFolder = GetFolder
    If strFolder = "" Then Exit Sub
    Set WkSht = ActiveSheet
    i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
    strFile = Dir(strFolder & "\*.doc", vbNormal)
    While strFile <> ""
      i = i + 1
      Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
      With wdDoc
        j = 0
        For Each CCtrl In .ContentControls
          With CCtrl
            Select Case .Type
              Case Is = wdContentControlCheckBox
               j = j + 1
               WkSht.Cells(i, j).Value = .Checked
              Case wdContentControlDate, wdContentControlDropdownList, wdContentControlRichText, wdContentControlText
               j = j + 1
               WkSht.Cells(i, j).Value = .Range.Text
              Case Else
            End Select
          End With
        Next
        .Close SaveChanges:=False
      End With
      strFile = Dir()
    Wend
    wdApp.Quit
    Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
    Application.ScreenUpdating = True
    End Sub
    
    
    Function GetFolder() As String
    Dim oFolder As Object
    GetFolder = ""
    Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
    If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
    Set oFolder = Nothing
    End Function
    I need the script to insert the data into a single designated column. THe above code currently inserts all the data into the first available row.
    Last edited by macropod; 07-11-2022 at 02:45 PM. Reason: Added code formatting

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    If there's only the one content control to extract data from in each document, you could change:
            With wdDoc 
                j = 0 
                For Each CCtrl In .ContentControls 
                    j = j + 1 
                    WkSht.Cells(i, j) = CCtrl.Range.Text 
                Next 
            End With
    to:
    WkSht.Cells(i, 1) = wdDoc.ContentControls(2).Range.Text
    where 1 is the column # you want to update and 2 is the content control # you want to extract the data from.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Edit
    WkSht.Cells(i, j)
    To
    WkSht.Cells(j, i)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by SamT View Post
    Edit
    WkSht.Cells(i, j)
    To
    WkSht.Cells(j, i)
    That really isn't going to satisfy the specified requirement:
    Quote Originally Posted by JonJon View Post
    I need the script to insert the data into a single designated column
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Each strFile gets it's own Column, Each CCtrl gets it's own Row in that Column.

    What am I missing?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    The 'i' variable is updated for each new file, hence your variation would start at column # equivalent to the last-used row (which is what 'i' is initialized with) - if that column exists - then increments the column # for each new file. That is not inserting all the data into 'a single designated column', let alone (in all likelihood) an appropriate starting column. Either 'i' needs to be initialized with the column count (if the data are to go into separate columns) or, if there are multiple content controls and all the data are to go into the same column, the 'j' variable needs to be held constant and the 'i' value alone should be updated for each content control. The code revision I posted does that on the assumption there is only one content control to extract data from per file.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Either 'i' needs to be initialized with the column count (if the data are to go into separate columns)
    Those were my assumptions.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Quote Originally Posted by SamT View Post
    Those were my assumptions.
    But 'i' is still being initialized with the row count!
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  9. #9
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I will change that in my workbook immediately.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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