Consulting

Results 1 to 9 of 9

Thread: EXCEL to Word vb CODE

  1. #1

    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 
    
    
    Formatting tags added by mark007




    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,151
    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 
    
    
    Formatting tags added by mark007
    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,147
    Location
    Edit
    WkSht.Cells(i, j) 
    
    
    Formatting tags added by mark007
    To
    WkSht.Cells(j, i) 
    
    
    Formatting tags added by mark007
    Please take the time to read the Forum FAQ

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,151
    Location
    Quote Originally Posted by SamT View Post
    Edit
    WkSht.Cells(i, j) 
    
    
    Formatting tags added by mark007
    To
    WkSht.Cells(j, i) 
    
    
    Formatting tags added by mark007
    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,147
    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,151
    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,147
    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,151
    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,147
    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
  •