I'm attempting to produce a Word form with fields for Suppliers to fill out
electronically; then to be able to compile the responses within an Excel
spreadsheet in db format. Although this could be achieved easier within
Access (or Lotus Approach) I need the end result to be sharable to users
without MS Access.

Please can anyone suggest where I can find information that will help
complete this task - I can create the Word doc, but have no idea how to link
it to an Excel db once returned from the Supplier.

I am more used to Lotus apps, so VBL is new to me.

All assistance gratefully received.... :)

I'm assuming by db format you mean you want to have headings in the s/sheet
that tally with the fields in your Word form and that you want a list to be
created in Excel from the forms being filled out by the suppliers.

I'd suggest a macro in Word attached to a toolbar button, stored in the
normal template so it is available whichever documents are open. When you
click on the button, the data from the active document is automatically added
to the Excel list.

In Excel, open a new workbook and type your headings in the top row of the
first sheet, starting in A1. Close and save it.

In Word, go to the Visual Basic Editor from Tools, Macro (or Alt-F11) and
insert a new module in the Normal project (Insert, Module). Make sure
Microsoft Excel Objects Library is ticked from Tools, References. Then type:

Sub PopulateExcel()
Dim myExcel As New Excel.Application
With myExcel
..Workbooks.Open "full path to your workbook file"
..Range("A1").CurrentRegion.Select
..Selection.Offset(Selection.Rows.Count, 0).Resize(1, 1).Select
..ActiveCell.Value = ActiveDocument.Bookmarks("Text1").Range.Text
..ActiveCell.Offset(0, 1).Value = ActiveDocument.Bookmarks("Text2").Range.Text
..ActiveCell.Offset(0, 2).Value = ActiveDocument.Bookmarks("Text3").Range.Text
etc.

..ActiveWorkbook.Close True
End With
Set myExcel = Nothing
End Sub

Each form field in your Word form has a bookmark name (to see it,
double-click on it when you're designing the form). This is a bit like a
named range in Excel and the code above is reading this into the Excel
workbook that the code has opened so you may well need to change the exact
bookmark names ("Text1", "Text2" and so on). The ActiveCell.Offset line is
looking at successive columns with respect to the left hand column of the
list so you need to get the bookmark names in the right order, tallying with
the right Offset.

Good luck!


"Dexxterr" wrote:

I'm attempting to produce a Word form with fields for Suppliers to fill out
electronically; then to be able to compile the responses within an Excel
spreadsheet in db format. Although this could be achieved easier within
Access (or Lotus Approach) I need the end result to be sharable to users
without MS Access.

Please can anyone suggest where I can find information that will help
complete this task - I can create the Word doc, but have no idea how to link
it to an Excel db once returned from the Supplier.

I am more used to Lotus apps, so VBL is new to me.

All assistance gratefully received.... :)

Martin - this makes a lot of sense. I'm going to give it a try.

Thank you for your help, regards,

D.


"Martin" wrote:

I'm assuming by db format you mean you want to have headings in the s/sheet
that tally with the fields in your Word form and that you want a list to be
created in Excel from the forms being filled out by the suppliers.

I'd suggest a macro in Word attached to a toolbar button, stored in the
normal template so it is available whichever documents are open. When you
click on the button, the data from the active document is automatically added
to the Excel list.

In Excel, open a new workbook and type your headings in the top row of the
first sheet, starting in A1. Close and save it.

In Word, go to the Visual Basic Editor from Tools, Macro (or Alt-F11) and
insert a new module in the Normal project (Insert, Module). Make sure
Microsoft Excel Objects Library is ticked from Tools, References. Then type:

Sub PopulateExcel()
Dim myExcel As New Excel.Application
With myExcel
.Workbooks.Open "full path to your workbook file"
.Range("A1").CurrentRegion.Select
.Selection.Offset(Selection.Rows.Count, 0).Resize(1, 1).Select
.ActiveCell.Value = ActiveDocument.Bookmarks("Text1").Range.Text
.ActiveCell.Offset(0, 1).Value = ActiveDocument.Bookmarks("Text2").Range.Text
.ActiveCell.Offset(0, 2).Value = ActiveDocument.Bookmarks("Text3").Range.Text
etc.

.ActiveWorkbook.Close True
End With
Set myExcel = Nothing
End Sub

Each form field in your Word form has a bookmark name (to see it,
double-click on it when you're designing the form). This is a bit like a
named range in Excel and the code above is reading this into the Excel
workbook that the code has opened so you may well need to change the exact
bookmark names ("Text1", "Text2" and so on). The ActiveCell.Offset line is
looking at successive columns with respect to the left hand column of the
list so you need to get the bookmark names in the right order, tallying with
the right Offset.

Good luck!


"Dexxterr" wrote:

I'm attempting to produce a Word form with fields for Suppliers to fill out
electronically; then to be able to compile the responses within an Excel
spreadsheet in db format. Although this could be achieved easier within
Access (or Lotus Approach) I need the end result to be sharable to users
without MS Access.

Please can anyone suggest where I can find information that will help
complete this task - I can create the Word doc, but have no idea how to link
it to an Excel db once returned from the Supplier.

I am more used to Lotus apps, so VBL is new to me.

All assistance gratefully received.... :)