|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
||||
|
||||
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
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA to set Content controls as non printing | Sammie0Sue | Word VBA | 21 | 01-12-2021 04:44 PM |
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 |
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 |