Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 02-20-2015, 10:10 AM
harry.ayre harry.ayre is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Feb 2015
Posts: 2
harry.ayre is on a distinguished road
Default When content controls are deleted, my macro does not work as I would like.

Hi

I have been using the forums on this site for some time and have found them incredibly useful. Thank you!

I have a large number of word forms with content controls in paragraphs of text as well as tables. I also have an excel sheet which pulls the values in the word form fields, in order from left to right, in to the excel sheet.

Here is a copy of the code I am using. I got it from http://www.vbaexpress.com/forum/show...l=1#post257696

Sub GetFormData()
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 & "\*.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
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


Public Sub ClearSheet()


'Clear Sheet Data'


Rows("6:2000").Select
Selection.ClearContents
End Sub


The problem that I'm having is that users of the forms cannot delete any of the form fields in the word forms. If this occurs, the excel does not account for the deleted form field. Is there any way that I can code something which will leave a blank cell in excel where the form field was deleted?

Any help would be very appreciated.

Thanks!
Reply With Quote
  #2  
Old 02-22-2015, 11:27 PM
gmayor's Avatar
gmayor gmayor is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 2,673
gmayor is just really nicegmayor is just really nicegmayor is just really nicegmayor is just really nice
Default

The problem is that you are counting controls to determine which column to fill. The macro has no way of knowing that you have removed a control so it works on the basis that the new count is correct. You need to address the controls by name and fill the column related to that control name e.g.

Code:
        For Each CCtrl In .ContentControls
            Select Case CCtrl.Title
                Case "Title1": WkSht.Cells(i, 1) = CCtrl.Range.Text
                Case "Title2": WkSht.Cells(i, 2) = CCtrl.Range.Text
                    'etc
            End Select
        Next oCC
This will mean that you will have to add names to the CCs before they are filled by your users.
__________________
Graham Mayor - MS MVP (Word)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 02-23-2015, 10:35 AM
harry.ayre harry.ayre is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Feb 2015
Posts: 2
harry.ayre is on a distinguished road
Default

Thank you, this is very very useful. Would you have any suggestions for how I can then edit the excel macro so that each tag is assigned a specific column?

Thanks so much
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cant type into content controls in a form after protecting document using macro rgburridge Word VBA 4 01-27-2015 02:37 PM
Word 2010 Macro Enabled Template with Content Controls keen1598 Word VBA 7 01-29-2014 03:17 PM
VBA to set Content controls as non printing Sammie0Sue Word VBA 10 11-09-2013 12:09 AM
Content Controls Sammie0Sue Word 6 11-06-2013 10:56 PM
Macro to link 2 content controls bortonj88 Word VBA 2 08-21-2012 06:24 AM


All times are GMT -7. The time now is 10:20 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft