Populating Word form template with Excel data

[SkylabOne]

Dear masters of Excel,

My Excel workbook contains extensive data which is constantly updated with new project numbers (below is obviously an example).

I've created a Word form template called Invoice Request which is now filled in manually, but this is prone to error and it takes too much time.
Upon opening the Wordfile the user should be prompted to fill in a project number and the corresponding data should be filled in automatically.

My problem: I do not know how to do this. I did find out that I should use bookmarks, but I can't seem to link them to my Word file.

ClientNumber	ClientName	ProjectNumber	ContactPerson
1	Jane's Bar	1401	Jane
2	Jim's Restaurant	1405	Jim
3	Joe's Kitchen	1410	Joe

<tbody>
</tbody>

I understand this is more of a Word related question than Excel, but both Excel and VBA are used for this, so I hope someone who reads this will have a sample code for me.

Thank you in advance.

Kind regards,
Rolf

 

[jscranton]

Rolf,

I have built several applications that function in the manner that you describe. My short answer is that it is quite complicated.

For starters, you are using Excel to function as a database. This is a bad idea for reasons unrelated to the VBA code you would need to write. Extensive data should not be housed in Excel. I would migrate it to Access or an SQL database.

Secondly, the "heart" of your code would be a connection between your Word document and your data source. In theory, this is simple. In practice, it can be very, very difficult especially if multiple users are deploying the Word document from various locations with various levels of access to the data source. If this is all happening on your local disk, then it is much more manageable.

Finally, the word document is going to use a series of content controls (text boxes, drop downs). I have found these to be a little clunky from a programmatic standpoint.

Let me know about point #2 and I will steer you to some code to get started.

J.

 

[MPW]

I currently use excel for a rather large data base (over 10,000) lines and it works quite well. I create a word document from the info in the spreadsheet but instead of using Word to grab the info from Excel, I use Excel to create a fresh template in Word exporting from Excel instead of importing from Word. I create a new Word doc and populate it with everything from scratch. Don't know if this helps at all.

 

[SkylabOne]

Hi J.,

Thank you for the swift reply. It is definitely possible to migrate the data to Access.

Regarding #2; for now I'll be the only one using it only on my own local disk. However, we use Office 365/Sharepoint at my work, and in the end this document will need to be accessed by everyone within the sales department. But that's future-talk and of no concern now.

Regarding #Finally; How I see it, the Word document won't need any drop downs. Basically only a first question pop-up upon opening asking what client or project number to base the document on for populating data, and bookmarks throughout the document which will be filled in automatically (based on the client or project number).

I'm looking forward to your reply. Thank you for your time.

Kind regards,
Rolf

 

[SkylabOne]

I currently use excel for a rather large data base (over 10,000) lines and it works quite well. I create a word document from the info in the spreadsheet but instead of using Word to grab the info from Excel, I use Excel to create a fresh template in Word exporting from Excel instead of importing from Word. I create a new Word doc and populate it with everything from scratch. Don't know if this helps at all.

MPW,

If I understand correctly, you push the data from Excel to Word? So, you somehow select data in Excel and this is exported to a certain Word template through VBA in Excel?

I would like users to open the template, get a question about what client or project number they want to make an invoice for, they fill in the right number, and the entire form is populated. Is that more or less the same as you mean?

 

[jscranton]

First step will be triggering an event in the Document Open properties:

Private Sub Document_Open()

'When opened, use a message box to ask the user for a client or a project number

'THen call a macro to connect to the data source

Call someMacro(projectName as String)

As you describe it, you could do something much simpler than I originally thought:

Dim objExcel As New Excel.Application
Dim exWb As Excel.Workbook

Set exWb = objExcel.Workbooks.Open("c:\temp\expenses.xls")

You can then use the exWB object to get whatever you need.

On the other hand, if this is deployed across a wider network (especially Sharepoint) you will need to use an ADODB connection. I highly recommend reviewing connection strings at www.connectionstrings.com.

Dim cnn As ADODB.Connection[/FONT]

[FONT=Courier New]Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;[/FONT]
[FONT=Courier New]Extended Properties[/FONT][FONT=Courier New]="Excel 12.0 Xml;HDR=

Once this connection is open, you pull data from your data source. The string for an ACCDB would be differently as would an web based ACCDB stored on Sharepoint (which I would highly recommend).

Let me know if this gets you started.

J.

 

[MPW]

The way I look at it, Excel has a lot more control of where the data is located and Word not so much.
So I start with a blank Word doc and push everything I want on that sheet (All the Titles, formatting, any info from Excel, everything) is created on the fly. The application I am using this with is taking all the notes that were made while working with over 10,000 lines of text and creating an error list, all formatted the way it should look for the end user. My app does this with a click of a button but you could easily make an interface to let the end user select what data to send to Word.

 

[SkylabOne]

It definitely got me started, J!

Now I would like to know two things:
1.) Opening the template a pop-up opens now in which the user will fill in the client or project number, respectively column A and B in Excel, in either text boxes. My question: I'd like to put a label below the text box to show the client name and contact person (column C & D) once a correct number is entered. This to make sure the right row is selected by the user.

2.) Clicking on the OK! button will fill in the form with the right data from the sheet. For example: E-mail = column E, Telephone = column F. How to make sure the right cell is selected, compared to the client or project number?

Thank you again! My workday is over, but I'll be back again tomorrow.

Kind regards,
Rolf

 

[SkylabOne]

Sounds like a great method, MPW! I'm hoping to create something like that too. Excel is a frontier I've mastered quite well, but VBA is a whole different thing and I just started using it. Being even less than a novice it can be quite frustrating at times, but it's incredibly satisfying to create something that's actually working, so I'm looking forward to really diving into it and learn to master it as much as possible.

 

[jscranton]

How's this coming? Sorry, I have been slammed the last two days (I meant to circle back earlier).