Re: Create Word Report From Named Ranges
I think you need to open the .dot file containing the bookmarks not add it. Also your use of Path as a String variable is probably not good as Path has a specific meaning. HTH. Dave
Re: Create Word Report From Named Ranges
Your use of Path is an issue. Here's some archive code that should help. Good luck! Dave
ps. sorry code author unkown
Sub test()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim BMRange As Word.Range
Dim company As Range
Dim address As Range
Dim address2 As Range
Dim city As Range
Dim state As Range
Dim zip As Range
Dim CuurentDate As Date
Dim Cnt As Integer
Dim LastRow As Integer
LastRow = Sheets("sheet1").UsedRange.End(xlDown).Row
Set wdApp = CreateObject("Word.Application") 'Create an instance of word
For Cnt = 2 To LastRow
Set wdDoc = wdApp.Documents.Open("C:\Documents and Settings\mwildrick\Desktop\TEST\Publisher Payment Form.dotm") 'Open word file
'now set your excel ranges
Set company = ThisWorkbook.Sheets("CompanyInfo").Range("A" & Cnt)
Set address = ThisWorkbook.Sheets("CompanyInfo").Range("B" & Cnt)
Set city = ThisWorkbook.Sheets("CompanyInfo").Range("D" & Cnt)
Set state = ThisWorkbook.Sheets("CompanyInfo").Range("E" & Cnt)
Set zip = ThisWorkbook.Sheets("CompanyInfo").Range("F" & Cnt)
'Set your word bookmark
Set BMRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="company")
BMRange.Text = company
Set BMRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="Address")
BMRange.Text = address
Set BMRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="City")
BMRange.Text = city
Set BMRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="State")
BMRange.Text = state
Set BMRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="Zip")
BMRange.Text = zip
'Save your word doc
With wdApp.ActiveDocument
.SaveAs ThisWorkbook.Path & "\" & company & ".doc"
.Close
End With
Next Cnt
'Close out word
wdApp.Quit
Set BMRange = Nothing
Set wdDoc = Nothing
Set wdApp = Nothing
End SubRe: Create Word Report From Named Ranges
Hi Vibhav
The issue you have is that most of your named ranges are named at a worksheet level and will not be picked up in the the following loop.
For Each xlName In wb.Names
'if xlName's name is existing in document then put the value in place of the bookmark
If docWord.Bookmarks.Exists(xlName.Name) Then
docWord.Bookmarks(xlName.Name).Range.Text = Range(xlName.Value)
End If
Next xlNameYou have two options.
Delete the ranges and then rename the ranges at a workbook level or create an additional loop to pick up those ranges which are name at the worksheet level.
Go into the Insert > Name > Define menu and you should be able to see what i mean.
Re: Create Word Report From Named Ranges
i think I understand what you mean. To incorporate what you suggested I took the following approach.
Delete all the named ranges in Test.xls
After Sheet1 is copied and new file Testing.xls is made, a sub is called to rename the ranges.
But it still not working! 
Sheets("Sheet1").Copy
Application.DisplayAlerts = False
ChDir "C:\Word-Excel"
ActiveWorkbook.SaveAs Filename:= _
"C:\Word-Excel\Testing.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Call NAMETHERANGE
.....SUB NAMETHERANGE()
Worksheets("Sheet1").Names.Add Name:="INVCOLPOL", RefersTo:="=Sheet1!$E$5"
Worksheets("Sheet1").Names.Add Name:="INVCOLPRO", RefersTo:="=Sheet1!$E$6"
Worksheets("Sheet1").Names.Add Name:="INVCOLTRA", RefersTo:="=Sheet1!$E$7"
Worksheets("Master").Names.Add Name:="INVCOLGOV", RefersTo:="=Sheet1!$E$8"
Worksheets("Sheet1").Names.Add Name:="INVCOLAPP", RefersTo:="=Sheet1!$E$9"
Worksheets("Sheet1").Names.Add Name:="INVCOLDAQ", RefersTo:="=Sheet1!$E$10"
Worksheets("Sheet1").Names.Add Name:="INVCOLRES", RefersTo:="=Sheet1!$E$11"
Worksheets("Sheet1").Names.Add Name:="INVCOLPEO", RefersTo:="=Sheet1!$E$12"
Worksheets("Sheet1").Names.Add Name:="INVCOLFUN", RefersTo:="=Sheet1!$E$13"
end sub[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Could you please let me know how do I then rename the ranges at a workbook level using VBA or create an additional loop to pick up those ranges which are name at the worksheet level using VBA?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Could you please let me know how do I then rename the ranges at a workbook level using VBA or create an additional loop to pick up those ranges which are name at the worksheet level using VBA?
Re: Create Word Report From Named Ranges
Hi Vibhav
The above has not fixed you problem as you have named the ranges at the workbook level again. You have referred to the worksheet object when naming the ranges
An example of the code required is below
Activeworkbook.Names.Add Name:="INVCOLPOL", RefersTo:="=Sheet1!$E$5"PLEASE NOTE: You must delete the named ranges before the above code can work.
Don’t have an account yet? Register yourself now and be a part of our community!