VBA MS Word - refer to table in document header

CNorth

New Member
Joined
Jan 7, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hi

I am relatively new to VBA and would be grateful for some help to refer a table in the document header. The code immediately below works fine but when I add this code to a for each loop it generates the runtime error 5941 " the requested member of the collection does not exist" at the .Range.Tables(1).Cell(1, 2).Range.Text = "test" line. The purpose of the code is to loop through each Word file in a folder, insert text into some fields of a table in the document header, and save the changes.

This works

VBA Code:
With ActiveDocument.Sections(1).Headers(wdHeaderFooterPrimary)
.Range.Tables(1).Cell(1, 2).Range.Text = "test"
'etc
End With



This does not work

VBA Code:
Sub Test()
Dim FSO As Object

Set FSO = CreateObject("Scripting.filesystemobject")

Dim folder As Object

Dim fpath As String

Dim myFile As Object

Dim strFileExt As String

strFileExt = ".docx"



Dim strSearch2 As String

Dim strSearch1 As String

Dim lngPosition1 As Long

Dim lngPosition2 As Long



'Returns lngPosition1 of first space

lngPosition1 = InStr(ActiveDocument.Name, " ")

strSearch1 = Left(ActiveDocument.Name, lngPosition1)



'Returns lngPosition1 of last full stop

lngPosition2 = InStrRev(ActiveDocument.Name, ".")

strSearch2 = Left(ActiveDocument.Name, lngPosition2)



Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)

fDialog.Title = "Select a folder"

fDialog.InitialFileName = "G:\.........”

If fDialog.Show = -1 Then

fpath = fDialog.SelectedItems(1)

Set folder = FSO.GetFolder(fpath)

End If

For Each myFile In folder.Files

If InStr(1, myFile.Name, strFileExt, vbTextCompare) > 0 Then

Documents.Open (myFile)

With activedocument.Sections(1).Headers(wdHeaderFooterPrimary)
.Range.Tables(1).Cell(1, 2).Range.Text = "test"
'etc.
End With

Exit For
End If
Next
End Sub

Any help would be really appreciated.

Caroline
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,527
Have you established that the document concerned actually has a table in the primary header with 2 cells on the table's first row?

PS: Not that you're actually using it, all this:
VBA Code:
Dim strSearch2 As String
Dim strSearch1 As String
Dim lngPosition1 As Long
Dim lngPosition2 As Long
'Returns lngPosition1 of first space
lngPosition1 = InStr(ActiveDocument.Name, " ")
strSearch1 = Left(ActiveDocument.Name, lngPosition1)
'Returns lngPosition1 of last full stop
lngPosition2 = InStrRev(ActiveDocument.Name, ".")
strSearch2 = Left(ActiveDocument.Name, lngPosition2)
could be reduced to:
VBA Code:
Dim strSearch2 As String
Dim strSearch1 As String
strSearch1 = Split(ActiveDocument.Name, " ")(0)
strSearch2 = Split(ActiveDocument.Name, ".")(0)
 
Last edited:

CNorth

New Member
Joined
Jan 7, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Thanks for the tip to reduce the code using split.

It does have a table in the header with three rows and two columns. If i use the code at the top as a macro on its own, it works. However, if I put the same code inside the rest of the macro it does not recognise the table for some reason.
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,527
Try:
VBA Code:
Sub Demo()
Dim FSO As Object, StrPth As String, StrNm As String
Dim myFile As Object, Doc As Document
Const strFileExt As String = ".docx"
Set FSO = CreateObject("Scripting.filesystemobject")
With Application.FileDialog(msoFileDialogFolderPicker)
  .Title = "Select a folder"
  If .Show = -1 Then StrPth = FSO.GetFolder(.SelectedItems(1))
End With
StrNm = Dir(StrPth & "\*" & strFileExt, vbNormal)
'process all files in the source folder
Do While StrNm <> ""
  Set Doc = Documents.Open(FileName:=StrPth & "\" & StrNm, AddToRecentFiles:=False, Visible:=False)
  With Doc
    .Sections.First.Headers(wdHeaderFooterPrimary).Range.Tables(1).Cell(1, 2).Range.Text = "test"
    .Close SaveChanges:=True
  End With
  StrNm = Dir()
Loop
End Sub
 
Solution

CNorth

New Member
Joined
Jan 7, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for looking into this. I tried the code above and unfortunately it generates the same 5941 error "the requested member of the collection does not exist" at
VBA Code:
.Sections.First.Headers(wdHeaderFooterPrimary).Range.Tables(1).Cell(1, 2).Range.Text = "test"
.
 

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,527
It works for me - with multiple documents. I can only conclude the code is trying to process a document that lacks a table in the primary header or, if it has one, that table either:
• lacks two cells in the first row; or,
• if it has two or more cells, the first cell is merged horizontally.
You might try:
VBA Code:
.Sections.First.Headers(wdHeaderFooterPrimary).Range.Tables(1).Range.Cells(2).Range.Text = "test"
which will populate whatever is the physical second cell in the table (if there is one)
 

CNorth

New Member
Joined
Jan 7, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
After including an additional to insert a table during the procedure and then insert text into the new table, I finally realised the document header settings were set to 'different first page'. I hadn't even thought to scroll to check the rest of the headers. Thank you so much for your help, the code you provided works brilliantly!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,893
Messages
5,621,486
Members
415,844
Latest member
Reda Fouad Ramzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top