User Tag List

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: MS Excel Experts

  1. #1
    Regular Member Damian's Avatar
    Join Date
    Jun 2005
    Location
    Market Drayton
    Problems Posted
    0
    Problems Solved
    0
    Best Answers
    0
    Good Answers
    0
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Vehicle InfoStats





    Default MS Excel Experts

    Are there any people out there with a bit of VB knowledge for Excel.

    Here is my first scenario

    I have got a yes/no selection in my Excel spreadsheet and on clicking YES I would like it to open a specific Word Document, on clicking NO, it does nothing.

    Second scenario

    A yes/no selection again and on clicking YES this time it opens a nrand new Word Document and automatically puts in some pre-determined text (meaning the word template does not have to be with the spreadsheet all the time) and on clicking NO, it does nothing

    It's basically for a H&S questionnaire, for example, if there is working at height the Manager clicks YES and this either opens the Working at Height Risk Assemsment template, or it open a new document and pastes in the wording

    Hope this make sense

    Cheers

  2. #2
    Regular Member Ian's Avatar
    Join Date
    Oct 2005
    Location
    York
    Problems Posted
    0
    Problems Solved
    0
    Best Answers
    0
    Good Answers
    0
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Vehicle InfoStats





    Default

    Okay, this took a little while -

    Copy the code below into the VB Editor for the command button that you are using on the excel sheet for Yes. If you want the No button to do nothing, then dont link it to anything.

    in the line:-
    Set wdDoc = wdApp.Documents.Open("C:\linked from worksheet.doc")
    set the path to the word doc that you want.


    'In order to use this code you must set a reference to the
    'Word object library by doing this. In the VB Editor click
    'Tools, References. Then search for Microsoft Word n.n Object Library
    'where n.n will depend on your version of Word.

    Dim wdApp As Word.Application, wdDoc As Word.Document

    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then 'Word isn't already running
    Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set wdDoc = wdApp.Documents.Open("C:\linked from worksheet.doc")

    wdApp.Visible = True
    wdDoc.Activate


    End Sub


    Hope this helps

  3. #3
    Regular Member Damian's Avatar
    Join Date
    Jun 2005
    Location
    Market Drayton
    Problems Posted
    0
    Problems Solved
    0
    Best Answers
    0
    Good Answers
    0
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Vehicle InfoStats





    Default

    That was spot on, worked a treat. Can I be cheeky and run this scenario past you.

    The document will be given to a variety of site managers which would mean having to give each of them all the template word documents(and there could be 10 or 15) also and making sure they are all stored in the correct way so the hardcoded links work. They cannot link to a server as they may be onsite not connected to the network.

    Would there be a way to have some 'text' in the VB code so a new Word document would be created and some text be put into that document automatically thus negating the need for the hard coded paths. I am guessing no formatting will be able to be transferred

    Cheers

  4. #4
    Regular Member ukanalyst's Avatar
    Join Date
    Jul 2006
    Location
    Cheshire
    Problems Posted
    0
    Problems Solved
    0
    Best Answers
    0
    Good Answers
    0
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Vehicle InfoStats





    Default

    Quote Originally Posted by Damian
    That was spot on, worked a treat. Can I be cheeky and run this scenario past you.

    The document will be given to a variety of site managers which would mean having to give each of them all the template word documents(and there could be 10 or 15) also and making sure they are all stored in the correct way so the hardcoded links work. They cannot link to a server as they may be onsite not connected to the network.

    Would there be a way to have some 'text' in the VB code so a new Word document would be created and some text be put into that document automatically thus negating the need for the hard coded paths. I am guessing no formatting will be able to be transferred

    Cheers
    Here you go mate, the solution posted gives you pretty much what you want with a small addition:

    Sub test()
    Dim wdApp As Word.Application, wdDoc As Word.Document

    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then 'Word isn't already running
    Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set wdDoc = wdApp.Documents.Add("Normal.dot")

    wdApp.Visible = True
    wdDoc.Activate
    wdDoc.Range(0, 0).Text = "Hello World"
    wdDoc.Range(6, 11).Bold = True
    wdDoc.Range(0, 5).Underline = True

    End Sub

    HTH

    Dave

  5. #5
    Regular Member Damian's Avatar
    Join Date
    Jun 2005
    Location
    Market Drayton
    Problems Posted
    0
    Problems Solved
    0
    Best Answers
    0
    Good Answers
    0
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Vehicle InfoStats





    Default

    Cheers will try tomorrow

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. MS Excel - Any Experts?
    By Aidi in forum The 'Off Topic' room
    Replies: 3
    Last Post: 22nd April 2012, 17:24
  2. Excel Macro's - Any Experts
    By spennny in forum The 'Off Topic' room
    Replies: 3
    Last Post: 17th July 2010, 09:26
  3. excel help
    By shan in forum General Chat
    Replies: 11
    Last Post: 7th November 2007, 00:33
  4. Excel Formula Experts
    By Damian in forum General Chat
    Replies: 9
    Last Post: 22nd December 2006, 10:08
  5. Excel Help Please
    By jka in forum General Chat
    Replies: 15
    Last Post: 6th April 2006, 10:42

Visitors found this page by searching for:

Nobody landed on this page from a search engine, yet!

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •