Create Word Report From Named Ranges

  • Hello,
    I have been struggling to create a word report from excel using named ranges and bookmarks. I have read and re-read majority of the threads. I am able to initiate the word document but for some odd reason (unknown to me) the data from the named ranges is not getting copied to bookmarks in the word documents.
    The idea is:
    A print button is present in workbook Test
    The user clicks on the print button


    Please find attached the files.
    Any help will be appreciated.

  • 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


    Thank you for your reply. The adding of the file is Ok. There is no issue with the Path as well.
    Any other suggestions? Please

  • 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

  • Re: Create Word Report From Named Ranges


    Thank you Dave, unfortunately use of Path is not an issue. There is something happening with the named ranges. Proof is:
    Name the cells B5,B6 and B7 as Name, Class and Result respecitively. Then give these named ranges some value.
    Add same bookmarks in the word template.
    Click Report and it will copy the values to word.


    Please find attached the files that display the above said proof.

    So, the code is correct. Still it is some reason that it is not doing something that it is supposed to.
    Got no idea why!

  • Re: 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.


    Code
    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 xlName


    You 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.

    Thanks


    J Hooimeyer

  • 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! :(


    Code
    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
    .....


    [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


    Code
    Activeworkbook.Names.Add Name:="INVCOLPOL", RefersTo:="=Sheet1!$E$5"


    PLEASE NOTE: You must delete the named ranges before the above code can work.

    Thanks


    J Hooimeyer

  • Re: Create Word Report From Named Ranges


    Thanks mate. Let me have a go at it and will be back here![hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Thanks a tonne mate.. works like a beauty. I deleted all the ranges from Test.xls and included your code. Now it is smoooth...
    Thanks a lot![hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Thanks a tonne mate.. works like a beauty. I deleted all the ranges from Test.xls and included your code. Now it is smoooth...
    Thanks a lot!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!