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

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.

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.

No problem. Just remember to set the reference to Excel (Tools | References) so you can use all the Excel VBA code from Word.

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.

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!

Thanks Ken, thats what I needed a little push. I'll keep everyone informed of my progress. So far nil, due to Christmas etc