#1
|
|||
|
|||
Retrieve page numbers in Word doc from set of variables in Excel file
Hi, I have an Excel spreadsheet which has 1 column of variables (i.e., 100+ customer names). I also have a 400 page Word document with each page containing a customer's name. There are numerous instances of each customer in the Word document but the pages are not sorted (e.g., Joe Smith occurs on pages 2,5,18,49; Mary Smith occurs on pages 3,7,31,55,etc). I'm trying to retrieve the page numbers for each respective customer, that way when I print the statements, they will be in order by customer name. I had tried to merge several VBA scripts I found online but to no avail. I would be very appreciative if someone could offer some guidance. Thank you all in advance. |
#2
|
||||
|
||||
When retrieved, where are the page #s to go?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hi Macropod,
Thanks for the prompt response! Sorry for leaving that detail out. I'm trying to retrieve the page numbers and inserting them into column 2, next to the customer names, of the existing Excel spreadsheet. |
#4
|
||||
|
||||
Try the following Excel macro:
Code:
Sub GetWordPageData() '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 StrDocNm As String, lRow As Long, i As Long Dim WkSht As Worksheet, StrTxt As String 'Check whether the document exists StrDocNm = "C:\Users\" & Environ("Username") & "\Documents\Document Name.doc" If Dir(StrDocNm) = "" Then MsgBox "Cannot find the designated document: " & StrDocNm, vbExclamation Exit Sub End If Set WkSht = ActiveSheet lRow = WkSht.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row Set wdApp = CreateObject("Word.Application") wdApp.Visible = True Set wdDoc = wdApp.Documents.Open(Filename:=StrDocNm, AddToRecentFiles:=False, Visible:=False) For i = 1 To lRow StrTxt = "" With wdDoc.Content With .Find .ClearFormatting .Replacement.ClearFormatting .Text = WkSht.Cells(i, 1).Text .Replacement.Text = "" .Forward = True .Wrap = wdFindStop .Format = False .MatchCase = True .MatchWholeWord = True .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False .Execute End With Do While .Find.Found StrTxt = StrTxt & " " & .Duplicate.Information(wdActiveEndPageNumber) .Collapse wdCollapseEnd .Find.Execute Loop End With WkSht.Cells(i, 2).Value = Replace(Trim(StrTxt), " ", ", ") Next wdDoc.Close SaveChanges:=False wdApp.Quit Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Thanks again, Macropod. I enabled the Word object model and updated the file path & name. It's giving me the following error message:
Run-time error '-2147023170 (800706be)': Automation Error The remote procedure call failed. When I debug it, it highlights the following line of code: StrTxt = StrTxt & " " & .Duplicate.Information(wdActiveEndPageNumber) I searched around and saw someone was having similar problems. I tried setting some of the variables (e.g., wdApp and wdDoc) as objects but still no luck. |
#6
|
||||
|
||||
You shouldn't need to change any of the variables - the code works fine for me as is. The only thing I can think of that might help is changing:
Set wdDoc = wdApp.Documents.Open(Filename:=StrDocNm, AddToRecentFiles:=False, Visible:=False) to: Set wdDoc = wdApp.Documents.Open(Filename:=StrDocNm, AddToRecentFiles:=False, Visible:=True) Other than that, try repairing the Office installation (via Programs & Features > Microsoft Office > Change in the Windows Control Panel).
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Hi Paul,
I tested your code on another computer and it worked. Thank you for your patience and help! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Page numbers at bottom of page in Word | maggie122097 | Word | 1 | 04-21-2014 01:21 PM |
How do I refer to page numbers, when the numbers change as I prepare the document? | StevenD | Word | 5 | 11-29-2012 12:52 AM |
unable to insert page numbers in Word | thislife95 | Word | 2 | 06-05-2012 08:07 AM |
Page Numbers Not Matching Chapter Numbers | gracie5290 | Word | 1 | 02-02-2012 11:41 PM |
Word page numbers and footers. | drenriza | Word | 5 | 01-27-2011 12:47 AM |