Consulting

Results 1 to 7 of 7

Thread: Word to Excel automation database

  1. #1
    VBAX Regular
    Joined
    Nov 2004
    Posts
    74
    Location

    Word to Excel automation database

    Good day viewers,

    I had a cunning plan the other day but have no idea if it is either possible or where to begin.

    I have a Word template. A dialog box pops up on 'New Doc' and collects information which then fills out the Word template using bookmarks.

    What I would like to do now is somehow join an Excel s/sheet to my Word template. Therefore the user would complete the dialog box, Click Submit and as well as the form being filled out the data would also be automatically logged as a new record on the s/sheet. This data could then be analysed.

    Please could someone tell me if this is possible and also maybe point a relevent webpage my way or an interesting book that could help. Please make it easier than JPDO's post: Problems EXCEL to Word

    Thank you

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    See if this can help you get started. In Word VBE set a reference to the "Microsoft Excel ## Object Library". ## could be 9.0, 10.0 etc. depending on what version of Excel you have.
    [vba]
    Option Explicit

    Sub DataToExcel()

    Dim AppExcel As New Excel.Application
    Dim Wkb As Workbook
    Dim Var1 As String
    Dim TargetRow As Long

    Var1 = "Test"

    Set Wkb = AppExcel.Workbooks.Open("C:\book1.xls")
    TargetRow = Wkb.sheets("Sheet1").Range("A65536").End(xlup).Row + 1

    Wkb.sheets("Sheet1").Range("A" & TargetRow).Value = Var1

    Wkb.Save
    Wkb.Close
    AppExcel.Quit

    Set Wkb = Nothing
    Set AppExcel = Nothing

    End Sub
    [/vba]
    In the example Var1 is just a variable that has some text. When you put the text into your Word field, use the same variable to put that same text into a cell in the Excel workbook.

  3. #3
    VBAX Regular
    Joined
    Nov 2004
    Posts
    74
    Location
    Hi Jacob,

    You're always coming to my rescue, thanks. It's been so long since I've declared variables in programming languages, it's going to take me a while to get my head around so forgive me if a couple of days go past before I tell you how I have done.

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    No problem. Just remember to set the reference to Excel (Tools | References) so you can use all the Excel VBA code from Word.

  5. #5
    VBAX Regular
    Joined
    Nov 2004
    Posts
    74
    Location
    huh...

    Sorry could you explain that like you are talking to a child. This is the first time I've tried to passport info from two office applications.

  6. #6
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Hi newk,

    If you haven't got this one yet... In the Visual Basic Editor, go to the Tools menu and choose References.

    Scroll down the list until you find the following:

    Quote Originally Posted by DRJ
    "Microsoft Excel ## Object Library". ## could be 9.0, 10.0 etc. depending on what version of Excel you have.
    Check the box next to the Microsoft Excel reference, then click OK.

    For reference Office 2003 is version 11, XP is 10, 2000 is 9 and 97 is 8.

    Hope this helps!
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  7. #7
    VBAX Regular
    Joined
    Nov 2004
    Posts
    74
    Location
    Thanks Ken, thats what I needed a little push. I'll keep everyone informed of my progress. So far nil, due to Christmas etc

Posting Permissions

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