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
    Application.ScreenUpdating = False
    Dim wdApp As New Word.Application
    Dim wdDoc As Word.Document
    Dim CCtrl As Word.ContentControl
    Dim strFolder As String, strFile As String
    Dim 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 & "\*.docx", 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
    j = j + 1
    WkSht.Cells(i, j) = CCtrl.Range.Text
    Next
    End With
    wdDoc.Close SaveChanges:=False
    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.

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,294
    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
    [MS MVP - Word]

  3. #3
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,396
    Location
    Edit
    WkSht.Cells(i, j)
    To
    WkSht.Cells(j, i)
    Please take the time to read the Forum FAQ

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,294
    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
    [MS MVP - Word]

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

    What am I missing?
    Please take the time to read the Forum FAQ

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,294
    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
    [MS MVP - Word]

  7. #7
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,396
    Location
    Either 'i' needs to be initialized with the column count (if the data are to go into separate columns)
    Those were my assumptions.
    Please take the time to read the Forum FAQ

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,294
    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
    [MS MVP - Word]

  9. #9
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,396
    Location
    I will change that in my workbook immediately.
    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
  •