Word Userform populated from Excel Data

[camdameron]

Hi there,
I'm not able to attach an example of what I'm trying to do, so I hope that I can explain this effectively here.

I am trying to have a userform in Word populated with information that has been entered into a database in Excel. I have not built the Word form yet and have some flexibility there.
So, if I have a spreadsheet with twenty columns relating to process errors (date, process error number, facility, description, etc.) some of the information in the columns is entered via drop down and others are free text. I'm looking to find out if there's a way for the user to enter (on the word form) the process error number and have the twenty cells of information for that row be populated into corresponding fields on a word userform.

I know there is a possibility of this with mail merge, but we only write up the process errors one at a time so I'm looking to see if it is possible for the user to specify which row of data it wants the form populated with.

Thanks!
Cameron

 

[Ruddles]

Let me get you started - I find the most difficult step is usually the first one!

You sound as though you've worked out how to create a userform. Hopefully you know how to create a command button and view the code for it.

If you paste the following code into the Click event for a command button, it should bring an instance of Excel to life, open a workbook, extract the value in a cell, present it to you in a message box, then close the workbook and end Excel:-

 Dim objExcel As Excel.Application
 Dim wkbk As Excel.Workbook
 Dim ws As Excel.Worksheet
 
 Set objExcel = CreateObject("Excel.Application")
 Set wkbk = objExcel.Workbooks.Open("[COLOR=red][B]c:\temp\book1.xls[/B][/COLOR]")
 Set ws = wkbk.Sheets(1)
 
 MsgBox ws.Range("[COLOR=red][B]A1[/B][/COLOR]")
 
 wkbk.Close savechanges:=False
 objExcel.Quit

Before you run the code, use Tools > References in the VBA editor to add a reference to the Microsoft Excel Object Library and change the bits in red to refer to an actual workbook and a cell whose value you know.

That's how you get data from Excel when you're in Word. Obviously in a real-life application you would replace the MsgBox command with codde to do whatever you wanted, for example take a value (process error number) entered in a text box, search for it in the worksheet you just opened, and copy the contents of the row into a multiline text box or into s series of separate text boxes.

But that comes later. get the little bit of code I posted working first, then we can look into the search-and-display part.

 

[camdameron]

So, I apologize but maybe I misrepresented myself a bit here by using incorrect lingo. I meant a word form not a user form. DO i need to create a userform in order to pull from Excel? Or can content controls pull from excel also?
I've used your instructions to insert the code into a userform in VB but the buttons arent showing up on my document? Maybe I'm a bit more novice than i let on.

I appreciate your help!

Cameron

 

[Ruddles]

Ah, you did actually say "userform". Okay, try this as a starting point, just to convince yourself that it works - because if it doesn't work at its simplest, it's no use plunging forwards!

Create a new Word document and go Alt-F11 to get to the VBA editor. Create a new general code module (Insert > Module) and paste this code into it:-

[FONT=Courier New]Option Explicit[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Public Sub GetExcelData()[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]  Dim objExcel As Excel.Application
  Dim wkbk As Excel.Workbook
  Dim ws As Excel.Worksheet
 
  Set objExcel = CreateObject("Excel.Application")
  Set wkbk = objExcel.Workbooks.Open("[COLOR=red][B]c:\temp\book1.xls[/B][/COLOR]")
  Set ws = wkbk.Sheets(1)
 
  ws.Range("[COLOR=red][B]A1[/B][/COLOR]").Copy
  Selection.Paste
[/FONT][FONT=Courier New]  
  wkbk.Close savechanges:=False
  objExcel.Quit[/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]End Sub[/FONT]

Change the bits in red to point to an existing workbook - we can change this to a dialog box later, but this will do for now - and a cell in that workbook whose value you know. Go Tools > References in the VBA editor and add a reference to the Microsoft Excel Object Library. (This 'teaches' Word how to handle Excel objects.)

Return to the Word document and go Developer > Macros > GetExcelData > Run. Does the cell you specified get imported from the workbook to the Word document?

If so, great, that bit works. Now you just need to:-

1. Get the process error number from your form and pass it to the VBA code
2. Write a routine to find the process error number in the Excel workbook
3. Return the contents of the appropriate record back to the form

I can help you with (2) but I've not really done anything with Word, so I don't know enough about it to read data from a form and return data to it. You'd need to find someone else to help with (1) and (3).

 

[camdameron]

OK, tried as you suggest and I get an error:

Compile Error - User Defined Type not Defined

the error highlights "objExcel As Excel.Application" in the first line of the code and then Public Sub GetExcelData() is highlighted in yellow.

What am i doing wrong here. I am sorry for being do dense and I appreciate your assistance.

thanks!
Cameron

 

[Ruddles]

Go Tools > References in the VBA editor and add a reference to the Microsoft Excel Object Library.

You did this, yes?

 

[Norie]

If you used mail merge isn't it actually possible to filter it so you only get the record you want?

Also couldn't you do things the other way?

eg user selects row in Excel, hits a button and the data is transferred to your Word form/document

That fan definitely be done but how exactly depends on what you have in Word.

Has your form there been created with formfields?

Or is it just a 'standard' document with bookmarks for where the data is meant to go?

 

[camdameron]

Ideally, I'd like to have a word doc with form fields (dropdowns, date boxes, and other text fields) and have it automatically populate the excel sheet. But I am running into issues so selecting one cell from Excel and having all the assicoated content from Excel be populated into the word form is an excellent suggestion, just not sure how to get started as I am a novice programmer, but am eager to learn.

I have not yet created a form yet, so I have some freedom there.